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

秒速五厘米 2022-03-19 13:08 264阅读 0赞

这个可以算是学习笔记吧, 是参照甲骨论老相老师的视频做的:  
http://www.jiagulun.com/thread-2674-1-1.html  
  
**1.数据库文件**  
Oracle 数据库文件大概可分为3种,分别是:  
**控制文件(control files):** 存放数据库本身物理结构信息  
**数据文件(data files):** 存放数据库数据啦~  
**日志文件(log files):**  包括重做日志文件和归档日志文件, 记录数据库数据的变化.  
如下图:

![Oracle sql语句执行过程图文分析 - 饥民 - 饥民2011][Oracle sql_ - _ - _2011]

**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)**  
        
      
         如下图:

![Oracle sql语句执行过程图文分析 - 饥民 - 饥民2011][Oracle sql_ - _ - _2011 1]  
  
**2.2 服务器进程**

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

![Oracle sql语句执行过程图文分析 - 饥民 - 饥民2011][Oracle sql_ - _ - _2011 2]

注意啊, 每当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大小.  
如图:

![Oracle sql语句执行过程图文分析 - 饥民 - 饥民2011][Oracle sql_ - _ - _2011 3]

**3.客户端与服务器的SQL语句传输**  
     这时,用户在客户端输入若干条SQL语句,例如1个普通的存储过程,有读和写的动作.  
  
     这条语句通过什么来传输呢,答案就是session啦,  那么服务器上用什么来接受这个sql语句呢,答案就是Server Process啊:  
  
如图:

![Oracle sql语句执行过程图文分析 - 饥民 - 饥民2011][Oracle sql_ - _ - _2011 4]

**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.

![Oracle sql语句执行过程图文分析 - 饥民 - 饥民2011][Oracle sql_ - _ - _2011 5]

**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次使用啦.

![Oracle sql语句执行过程图文分析 - 饥民 - 饥民2011][Oracle sql_ - _ - _2011 6]  
  
  
**6.逻辑读,物理读与缓存命中率.**  
          由上图得知,所谓 **逻辑读**,就是从缓存(一般是内存)里读取数据啦.  而 **物理读**,也就是从磁盘(数据文件)里读取数据啦.  
  
          至于缓存命中率,就是取出数据的过程中     逻辑读次数/ (逻辑读次数+物理读次数) 这个比率.  
  
          当然这个比率越接近1越好, 因为物理读相当费时间啦, 机械硬盘瓶颈嘛~ 除非有服务器用SSD做硬盘...无咩可能啦~  
  
          当然命中率并不是数据库健康的唯一指标,因为当逻辑读十分巨大的时候, 即使物理读也很大,这个比率也很好看的, 所以有时要关心 **每秒物理读(tps)**.  
  
          可以在linux下使用iostat命令来查看当前磁盘的每秒物理读啦~  
  
**8.** **在缓存中修改数据.**  
       server process拿出数据放入缓存中,接下来就对数据进行修改啦.  
       因为修改数据很可能会产生大量缓冲数据,所以这个动作实在Database buffer cache里完成的. 这个很容易理解.

![Oracle sql语句执行过程图文分析 - 饥民 - 饥民2011][Oracle sql_ - _ - _2011 7]

**9.**  **修改数据会产生重做日志.**  
       上面提到,日志是用来记录数据库的数据变化的,所以对数据改动产生一定量的日志数据.  那么这些日志是不是直接就写到日志文件中呢.  
  
       写日志文件也是物理读,所以SGA就有个Redo log buffer,就是日志缓存啦, 专门实时存放产生的日志数据啦~

![Oracle sql语句执行过程图文分析 - 饥民 - 饥民2011][Oracle sql_ - _ - _2011 8]

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

![Oracle sql语句执行过程图文分析 - 饥民 - 饥民2011][Oracle sql_ - _ - _2011 9]

**11.将数据缓存和日志缓存写入磁盘**  
        其实到上面那一步为止,  整个sql语句执行流程已经完成了.  
        可能有人会问, Server process修改的数据和产生的日志还在SGA里面啊, 它们不用被写入磁盘吗?  
  
        答案是肯定需要的, 但是这些动作已经不是sql执行流程之内, 而且这些动作也不是server process负责的, 他们分别由 **DBWR** 和 **LGWR** 这个两个进程负责.  
  
如图:

![Oracle sql语句执行过程图文分析 - 饥民 - 饥民2011][Oracle sql_ - _ - _2011 10]

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

![Oracle sql语句执行过程图文分析 - 饥民 - 饥民2011][Oracle sql_ - _ - _2011 11]

**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啦.

![Oracle sql语句执行过程图文分析 - 饥民 - 饥民2011][Oracle sql_ - _ - _2011 12]  
  
  
最后感谢老相老师用心做的教学视频, 真的很通俗易懂, 截个图哈哈

![Oracle sql语句执行过程图文分析 - 饥民 - 饥民2011][Oracle sql_ - _ - _2011 13]

[Oracle sql_ - _ - _2011]: http://img7.ph.126.net/e9g9zniRsPnjLSWy3LyByQ==/6597921888168443967.jpg
[Oracle sql_ - _ - _2011 1]: http://img9.ph.126.net/Y1uokNtLhVuW3pUU7Z2FYw==/6597323753842425439.jpg
[Oracle sql_ - _ - _2011 2]: http://img4.ph.126.net/j2w-9uXAEeteuCgBaMpcWA==/6597716279494041224.jpg
[Oracle sql_ - _ - _2011 3]: http://img1.ph.126.net/M99g34k1-phyvVcPbdwhcA==/6598073620772855771.jpg
[Oracle sql_ - _ - _2011 4]: http://img1.ph.126.net/G9JSQXp7Q4NaW7Pyw11uoQ==/6597428207447377151.jpg
[Oracle sql_ - _ - _2011 5]: http://img6.ph.126.net/-EOkaSwCYdvRHRhrwGXwog==/6597703085354506926.jpg
[Oracle sql_ - _ - _2011 6]: http://img9.ph.126.net/yFioQV-Z0SV-BMkJ3xxvqQ==/6597176419286128529.jpg
[Oracle sql_ - _ - _2011 7]: http://img4.ph.126.net/hEG1_JJIpJe7FfLgbaPNCA==/6597885604284733494.jpg
[Oracle sql_ - _ - _2011 8]: http://img8.ph.126.net/O9rajmsQsDa4qSk8g-K8-Q==/6598266035307724318.jpg
[Oracle sql_ - _ - _2011 9]: http://img6.ph.126.net/j8XM6PVS8BIwkkh4IxQQ3A==/6597379828937358226.jpg
[Oracle sql_ - _ - _2011 10]: http://img1.ph.126.net/dweDEj82-B6sEyoJKTKweQ==/6597383127472241561.jpg
[Oracle sql_ - _ - _2011 11]: http://img4.ph.126.net/SRiTl9FOc7rFToPQYAfjLw==/6597789946773112384.jpg
[Oracle sql_ - _ - _2011 12]: http://img1.ph.126.net/QNy1eX_D1zaNWj_8f5Feuw==/6597520566424306021.jpg
[Oracle sql_ - _ - _2011 13]: http://img0.ph.126.net/xCxtPjgieFLI1baz8O5W2w==/6597842723331250820.jpg