Oracle sql语句执行过程图文分析

这个可以算是学习笔记吧, 是参照甲骨论老相老师的视频做的:
http://www.jiagulun.com/thread-2674-1-1.html

1.数据库文件
Oracle 数据库文件大概可分为3种,分别是:
控制文件(control files): 存放数据库本身物理结构信息
数据文件(data files): 存放数据库数据啦~
日志文件(log files):  包括重做日志文件和归档日志文件, 记录数据库数据的变化.
如下图:

 

2.数据库实例

       但是用户和应用程序是无法直接访问数据库文件的数据的, 这时Oracle服务器会启动1(或多个..RAC集群啦)个实例.,用户可以通过连接这个实例来访问数据库的数据.

       实例有两个组成部分,分别是:
       系统全局内存区(SGA):服务器专门划分给Oracle实例使用的内存块啦.
       Oracle进程: 包括服务器进程和后台进程, 后面会解析嘎啦.

2.1 系统全局内存区(SGA)
      SGA可以分成6大块,分别是 Java pool, shared pool, database buffer cache,large pool, streams pool, Redo log buffer
       注: 可用 v$sga这个视图去查看sga各大块大小.

       这篇文章介绍的是sql语句的执行流程,所以主要讲解其中的3个大块:
        共享缓冲区(shared pool)
       数据库缓冲高速缓存(database buffer cache)
       重做日志缓冲区(redo log buffer)
      
    
         如下图:

2.2 服务器进程

     当用户(客户端)要连接Oracle数据库时, Oracle就会创建1个session(会话),并且在 服务器上创建1个专门处理这个session的进程,就是服务器进程啦.

如下图

 

 

注意啊, 每当1个新用户创建1个新的连接到数据库,Oracle都会对应创建1条服务器进程的.


2.3 PGA(Program Global Area) 中文自己翻译啦~

        对应上面的Server Process, Oracle会在服务器上对每一条Server Process分配一定大小的内存,就是PGA了, 注意有几个session就会有几个对应的SGA块, 所以服务器对内存需求很大的啦~

可以用
select sum(pga_userd_mem) from v$process
语句来查看当前使用的总PGA大小.
如图:

 
3.客户端与服务器的SQL语句传输
     这时,用户在客户端输入若干条SQL语句,例如1个普通的存储过程,有读和写的动作.

     这条语句通过什么来传输呢,答案就是session啦,  那么服务器上用什么来接受这个sql语句呢,答案就是Server Process啊:

如图:

  server process会判断sql语句是否合法(语法,权限)

  如果sql语法有错,或者对应的表或视图或Procedure没有权限,就会直接返回错误信息啦,这个大家都懂的.


2/3.server process根据sql语句生成执行计划(execute plan).

      Oracle是无法直接执行sql语句的,必须先生成执行计划,然后Oracle就会根据执行计划去执行了.
      而生成执行计划要访问许多数据库对象, 是1个比较消耗服务器资源(CPU,IO,Memory)的动作.

      而且因为同一条sql语句可能会有多个用户多次重复的执行, 那么是否每次都生成一次执行计划呢?
     这时SGA里面的Shared pool就发挥作用了,说白了它会缓存sql的执行计划..

    所以server process会首先从Shared pool里面查找有无现成的执行计划, 如果有就直接采用.
   如果无, 就自己生成1个,然后看情况把这个执行计划放入shared pool.

 

 

server process根据执行计划 去取(写)数据.
       好了 当server process得到执行计划后就可以去取数据了.
       Oracle的数据放在哪里呢, 放在数据文件,这个大家都懂,但是server process是不是就直接去访问数据文件呢?

      因为计算机的时间消耗主要都在物理IO,所以要尽量避免物理读写,所以SGA里面的database buffer cache起作用了.
      说明白点, database buffer cache就是用来缓冲Data files里的数据的.  这样就可以避免了对数据文件的读写.

      所以server process得到执行计划后,第一步是首先去database buffer cache去找有没有现成的数据. 如果有最好, 如果无或者缓存中数据不全的话就只能去访问data files啦. 

      从data files获得数据后, 也不是直接发给用户客户端,而是根据情况放入database buffer cache里面, 以便当前或其他用户2次使用啦.

6.逻辑读,物理读与缓存命中率.
          由上图得知,所谓 逻辑读,就是从缓存(一般是内存)里读取数据啦.  而 物理读,也就是从磁盘(数据文件)里读取数据啦.

          至于缓存命中率,就是取出数据的过程中     逻辑读次数/ (逻辑读次数+物理读次数) 这个比率.

          当然这个比率越接近1越好, 因为物理读相当费时间啦, 机械硬盘瓶颈嘛~ 除非有服务器用SSD做硬盘...无咩可能啦~

          当然命中率并不是数据库健康的唯一指标,因为当逻辑读十分巨大的时候, 即使物理读也很大,这个比率也很好看的, 所以有时要关心 每秒物理读(tps).

          可以在linux下使用iostat命令来查看当前磁盘的每秒物理读啦~

8. 在缓存中修改数据.
       server process拿出数据放入缓存中,接下来就对数据进行修改啦.
       因为修改数据很可能会产生大量缓冲数据,所以这个动作实在Database buffer cache里完成的. 这个很容易理解.
 

 

9. 修改数据会产生重做日志.
       上面提到,日志是用来记录数据库的数据变化的,所以对数据改动产生一定量的日志数据.  那么这些日志是不是直接就写到日志文件中呢.

       写日志文件也是物理读,所以SGA就有个Redo log buffer,就是日志缓存啦, 专门实时存放产生的日志数据啦~
      

 

 
10. 最终Server Process把返回数据或信息通过session传回给用客户端
      Server Process 做完读取和修改数据的动作后,就会将结果返给用户了.

 

11.将数据缓存和日志缓存写入磁盘
        其实到上面那一步为止,  整个sql语句执行流程已经完成了.
        可能有人会问, Server process修改的数据和产生的日志还在SGA里面啊, 它们不用被写入磁盘吗?

        答案是肯定需要的, 但是这些动作已经不是sql执行流程之内, 而且这些动作也不是server process负责的, 他们分别由 DBWR 和 LGWR 这个两个进程负责.

如图:

 
DBWR:  Database writer,后台进程之一,负责将Database buffer cache里被修改的数据写入数据文件.
LGWR:   Log writer,后台进程之一,负责将Redo log buffer里的日志数据写入到日志文件.

 
12.为什么Oracle要将server process 和后台进程分开?

       如上图,为什么写入数据文件和日志文件要交给后台进程去完成呢.
       其实我们在流程可以发现, 服务器与用户打交道的就只有1个进程,就是Server Process啊,  所以server Process的速度直接影响了用户的感受, 无论后台进程多么繁忙,只要server process响应迅捷, 用户还是觉得数据库很快的. 相反,后台进程没事做,服务器CPU很空闲, 但server process反应慢的话, 用户就觉得数据库慢了.

      所以就要尽量精简server process的动作,  看看后台进程DBWR 和 LGWR进行的是什么动作, 磁盘写动作啊! 呢个仲慢过物理读啊.所以这些动作完全可以在sql流程执行完慢慢来嘛.

        Server Process唯一进行的物理操作就是物理读,  这个是无办法避免的, 因为数据都在磁盘上嘛... 除非有办法预测用户要提取的数据,提前拿出来.但也没有那么大的内存啊.


13.顺便介绍其余3大系统进程CKPT,SMON,PMON
都说Oracle有SGA6大池 3大数据库文件, 5大系统进程.

其中DBWR 和 LGWR上面已经介绍过了. 现在其余3个

CKPT : Checkpoint  检查点进程,负责更新控制文件和数据文件的头部信息,  控件文件在这篇blog开头就已经介绍过啦,至于数据文件的头部信息?  头部信息就是当前数据块的状态信息啦.

SMON :  system monitor 系统监视器, 负责监视维护SGA和后台进程啦,例如合并SGA里面的碎片.
PMON :  process monitor  进程监视器,  这里主要指服务器进程啦,例如一个用户突然掉线了,但是该服务器进程还在服务器,Pmon会隔一段时间把该进程清理掉并且释放SGA啦.

最后感谢老相老师用心做的教学视频, 真的很通俗易懂, 截个图哈哈

 

 









 

     

转载于:https://www.cnblogs.com/nvd11/archive/2013/04/02/2996816.html