共享池部分-library cache、library cache object handle、library cache object、shared cursor、session cursor和解析

最新推荐文章于 2022-06-07 14:47:06 发布

转载最新推荐文章于 2022-06-07 14:47:06 发布 · 3.1w 阅读

本文详细介绍了Oracle数据库中的SQL解析过程、软解析与硬解析的区别，以及shared pool、library cache、session cursor等核心组件的工作原理。文章还探讨了parent cursor与child cursor的概念，并解释了如何利用这些机制优化SQL执行效率。

共享池部分-library cache、library cache object handle、library cache object、shared cursor、session cursor和解析一些基本概念

 
 Posted on 
 2015 年 1 月 7 日 
 by 
 xiaoyu

 关于cursor是oracle中开发人员经常使用的，这里我们不提开发人员所提的cursor，而是简单提下shared pool中的parent cursor和child cursor以及pga中的session cursor，以及硬解析、软解析、library cache的内部结构
 shared pool中的library cache结构： 
  
    library cache中的对象称为library cache object，而library cache object都是以library cache object handle的结构存储在library cache中，要访问library cache object，就需要先访问library cache object handle，library cache object handle就是oracle一种自定义的c语言复杂结构，那么oracle是如何来存储和访问library cache object handle 
    
 实际上，library cache object handle是以hash table的方式存储在library cache中，每一个hash bucket都对应不同的hash value，对于单个hash bucket而言，里面存储的就是哈希值相同的所有library cache object handle，同一个hash bucket中不同的library cache object handle之间会用指针连接起来，即同一个hash bucket中不同的library cache object handle组成该hash bucket中涉及的library cache object的库缓存对象句柄链表（library cache object handles）。
 
 oracle如何去解析sql语句、软解析和硬解析： 
  
    当oracle要执行目标sql语句时，首选对该sql的sql文本做hash运算，然后根据得到的hash值去相关的hash bucket中遍历对应的库缓存对象句柄链表，此时需要持有library cache latch，如果找到了对应的library cache object handle，则可以直接使用其执行计划、解析树等对象，最后释放library cache latch，这就是我们常说的软解析，如果没有找到对应的library cache object handle，需要再次持有library cache latch，由于有新增加的sql对应的shared cursor（parent cursor和child cursor)需要load到shared pool中，shared pool结构发生了变化，oracle需要在shared pool中加载和分配新的空间，oracle会在持有library cache latch的前提下再持有shared pool latch，成功申请空间后释放掉shared pool latch，然后重新解析sql并把相关的sql执行计划、解析树等对象以library cache object handle的方式存储到先关的hash bucket中的库缓存对象句柄链表中，最后再释放library cache latch，这个也就是凑名昭著的硬解析。 
    
 查看上面软解析和硬解析的过程，我们发现硬解析会较长时间的持有library cache latch，也会持有shared pool latch，虽然软解析也会持有library cache latch，但是持有次数相比硬解析要少，持有时间也会硬解析短，软解析不会持有shared pool latch；如果系统出现大量的硬解析，则会引起library cache latch和shared pool latch的争用，一般在OLTP系统中，经常在书中看见要我们更多的使用绑定变量来提高系统的性能的原因，在oracle 11g后，oracle用mutex替换了相关的library cache latch，mutex的作用除了有latch的作用，还有library cache pin的作用。
 
 上面讲述了硬解析、软解析、library cache的结构后，再来简单提下经常看见parent cursor和child cursor 
  
    碰见太多的oracle文档中提到的shared pool主要是library cache和row cache，而library cache最主要的就是缓存sql语句，如果直接点就是缓存cursor，这里的cursor包括两种parent cursor和child cursor，那么parent cursor是什么了，child cursor又是什么了，这里需要对library cache object handle做一些深入的了解，library cache object handle也就是库缓存句柄有很多的属性，而对于cursor的library cache object handle而言比较重要的就是name、namespace和heap 0 pointer 
    
 属性“Name”：表示的时library cache object handle所对应的library cache object的名称，例如如果是sql语句对应的库缓存对象句柄，则属性name就是该sql语句的sql文本；如果是表对应的库缓存对象句柄，则属性name就是该表的表明
 属性“Namespace“：表示的是库缓存对象句柄对应的库缓存对象所在的分组名，不同类型的库缓存对象句柄可能属于同一个分组，比如sql语句和pl/sql语句所对应的库缓存对象句柄的namespace值都是CRSR
 属性 “heap 0 pointer”：这里要说明下library cache object handle类似c语言的结构体，library cache object handle中还嵌套了一些子结构，其中heap 0 pointer是指向子结构heap 0的指针
 heap 0也是一种复杂的结构，它一样有很多属性，对于cursor而言比较重点的两个属性是tables和data block pointer。
 属性table记录的是于该heap 0所在的库缓存对象有关联关系的库缓存对象句柄地址的集合，table又细分为很多类，对于cursor而言重点关注child table，child table记录的就是从属于该heap 0所在的库缓存对象的子库缓存对象句柄地址的集合，简单来讲可以理解为指向子游标的库缓存对象句柄的指针，那么oracle就可以直接通过访问库缓存对象（parent cursor）句柄的heap 0中指定的child table，来直接找到相关的子库缓存对象(child cursor）句柄，进而访问子库缓存对象。
 属性data block pointer是heap 0存储的指向data heap的指针，data heap可以简单理解为库缓存中的一块连续的内存区域，而这些内存区域就是存储着cursor的动态运行时runtime数据，比如特别常见的执行计划、sql所涉及的对象定义、绑定变量类型和长度等。
 
 parent cursor和child cursor都属于shared pool中shared cursor，parent cursor和child cursor都是以library cache object handle的方式存储在library cache中，parent cursor存储了目标sql的sql文本，其中sql文本是存储在parent cursor所对应的library cache object handle的属性name中，而child cursor则存储了执行计划和解析树。 
   oracle在解析目标sql去library cache中查找是否有匹配的shared cursor过程大体如下：  

      1 首先是根据库缓存对象句柄的name和namespace做hash运算，由于sql语句和pl/sql语句的namespace都是crsr，对于sql和pl/sql语句可以直接理解为根据sql text来做hash运算，得到了hash value就去hash bucket中找到是否有匹配的hash bucket 
      
 2 找到合适的hash bucket后，就去这个hash bucket对应的library cache object handles链表中找到是否有满足条件的library cache object handle（这里需要检验sql文本或者pl/sql文本是否一致，因为不同的sql语句或者pl/sql语句的hash value可能一致）
 3 根据找到的library cache object handle也就是parent cursor对应的库缓存对象句柄去找对应的child cursor，最后看是否满足重用执行计划和解析数（要比对对象、绑定变量类型和长度等）
 parent cursor和child cursor遍历顺序： 
    
      child cursor的name属性是空的，也就是我们不可能直接脱离parent cursor而去寻找child cursor，查看sql是否可以重用时都是需要先找到parent cursor然后根据parent cursor对应的库缓存对象句柄中heap 0 pointer-heap 0-table-child tables来去cursor cursor对应的库缓存对象句柄链表中去遍历是否有满足的cursor child（此时不仅仅需要验证sql文本一致，文本一致只作用于验证parent cursor部分是否可以共享，还要查看sql所涉及的对象定义、绑定变量类型和长度、优化器参数等来看是否能够重用child cursor） 
      
 那么对于shared pool而言硬解析就有以下两种情况： 1 parent cursor和child cursor都没有办法共享 2 parent cursor能够共享，但是child cursor不能共享
 可能在刚开始我们第一次接触oracle部分书籍和文档时就提到了shared pool中的library cache，但是真正能够讲清楚shared cursor、child cursor、hash bucket、library cache、library cache object、软解析、硬解析得并不多，当然这些概念模棱两可并不太影响我们运维的工作，但是如果能真正理解这些概念是可以帮助我们更深入的理解oracle的sql解析部分
 
 下面再来简单讲一个比较边缘化的概念session cursor： 
    
      session cursor顾名思义就是session级别的cursor，和shared cursor一样，session cursor也是oracle定义的一种c语言复杂结构，也是以hash表的方式缓存起来，所不同的是session cursor是缓存在PGA中。 
      
 跟shared cursor所不同的还是有以下几点：

       1 session cursor是于session相对应，不同的session之间的session cursor没有办法共享
      
 2 session cursor也是有生命周期的
      
 3 session cursor也是以hash表的形式缓存在pga中，oracle也是通过相关的hash运算先访问当前session的pga的session cursor。
     
 由于session cursor概念的引入，oracle的解析需要增加这一步骤：首先计算hash value查找session cursor中是否有满足条件的session cursor，如果有满足条件的session cursor，则重用这个session cursor，如果没有满足条件的session cursor，则需要重新生成一个session cursor，对于session cursor而言，这个也属于硬解析，如果session cursor、parent cursor、child cursor都已经分别存在了PGA和library cache中，则这就是我们所提到的软软解析，软软解析不需要再次生成session cursor，相比软解析消耗更少的资源和时间。
 
 软软解析的必须条件： 
    
      软软解析需要shared cursor和session cursor都分别在shared pool和pga中缓存，而将session cursor缓存到pga跟sql的执行解析次数和session_cached_cursors参数有关：如果session_cached_cursors等于0，那么session cursor在open、parse、bind、execute fetch后就直接close了。而如果session_cached_cursor大于0，而且session cursor所对应的sql解析和执行次数超过了3次，这个session cursor就能够被pga缓存住(存储在pga中的session cursor是用lru算法来管理的），这样sql再次执行时，shared cusror和session cursor都能够找到匹配的记录，oracle不再重复的需要为其生成一个session cursor，当然close一个缓存在pga的session cursor，也只是需要将其标记为soft cloud。
    
 接下来我们来验证下session cursor缓存到pga的条件：

       SQL> show parameter session_cached_cursors;
      
 NAME                                 TYPE        VALUE
      
 ------------------------------------ ----------- ------------------------------
      
 session_cached_cursors               integer     50
      
 SQL> select count(*) from t_samp01;
      
   COUNT(*)
      
 ----------
      
          0
      
 SQL> select cursor_type,sql_id from v$open_cursor where sid=50 and sql_text like 'select count(*) from t_samp01%';
      
 no rows selected
      
 SQL> select count(*) from t_samp01;
      
   COUNT(*)
      
 ----------
      
          0
      
 SQL> select cursor_type,sql_id from v$open_cursor where sid=50 and sql_text like 'select count(*) from t_samp01%';
      
 no rows selected
      
 SQL> select count(*) from t_samp01;
      
   COUNT(*)
      
 ----------
      
          0
      
 SQL> select cursor_type,sql_id from v$open_cursor where sid=50 and sql_text like 'select count(*) from t_samp01%';
      
 CURSOR_TYPE                                                      SQL_ID
      
 ---------------------------------------------------------------- -------------
      
 DICTIONARY LOOKUP CURSOR CACHED                                  16099tj19y4gr
      
 SQL> select count(*) from t_samp01;
      
   COUNT(*)
      
 ----------
      
          0
      
 SQL> select cursor_type,sql_id from v$open_cursor where sid=50 and sql_text like 'select count(*) from t_samp01%';
      
 CURSOR_TYPE                                                      SQL_ID
      
 ---------------------------------------------------------------- -------------
      
 SESSION CURSOR CACHED                                            16099tj19y4gr
      
 SQL> select sql_text from v$sql where sql_id='16099tj19y4gr';
      
 SQL_TEXT
      
 --------------------------------------------------------------------------------
      
 select count(*) from t_samp01
      
 看见同一个session下执行3次以上的sql对应的session cursor已经缓存到了pga中
     
 在oracle 11g之前的办法，在缓存session cursor的hash表对应的hash bucket中，oracle会缓存目标sql对应的parent cursor的库缓存对象句柄地址，这意味着oracle已经建立起了session cursor到parent cursor的联系，这样软软解析会对相关的latch的争用更低，因为session cursor中存储了直接指向parent cursor的指针，oracle不再需要和软解析一样要去持有library cache latch访问相关的hash bucket的library cache object handles链表中查找匹配的parent cursor
 在oracle 11g后，dump session的信息，session cursor中已经没有记录，对应的parent cursor的library cache object handle地址：
 11.2.0.3版本下alter sssion set events ‘immediate trace name errorstack level 3’的trac文件session cached cursor的dump中已经不存储指向parent cursor中的library cache object handle地址

       11.2.0.3版本的trace文件：
      
 ----- Session Cached Cursor Dump -----
      
 ----- Generic Session Cached Cursor Dump -----
      
 -----------------------------------------------------------
      
 -------------- Generic Session Cached Cursors Dump --------
      
 -----------------------------------------------------------
      
 hash table=0x7feac43941d0 cnt=6 LRU=0x7feac4386678 cnt=6 hit=10 max=50 NumberOfTypes=6
      
 type#0 name=DICTION count=0
      
 type#1 name=BUNDLE  count=5
      
 type#2 name=SESSION count=1
      
 type#3 name=PL/SQL  count=0
      
 type#4 name=CONSTRA count=0
      
 type#5 name=REPLICA count=0
      
  Bucket#024 seg=0x7feac4394648 nit=5 nal=5 ips=5 sz=56 flg=3 ucnt=1
      
    0 cob=0x7feac43021f8 idx=18 flg=0 typ=1 cur=0x7feac4302328 lru=1 fl=15
      
  Bucket#056 seg=0x7feac4394c48 nit=5 nal=5 ips=5 sz=56 flg=3 ucnt=1
      
    0 cob=0x7feac42db1e8 idx=38 flg=0 typ=1 cur=0x7feac42db318 lru=1 fl=15
      
  Bucket#130 seg=0x7feac4395a28 nit=5 nal=5 ips=5 sz=56 flg=3 ucnt=1
      
    0 cob=0x7feac43a1f78 idx=82 flg=0 typ=1 cur=0x7feac43a20a8 lru=1 fl=15
      
  Bucket#183 seg=0x7feac4396418 nit=5 nal=5 ips=5 sz=56 flg=3 ucnt=1
      
    0 cob=0x7feac43abcd8 idx=b7 flg=0 typ=1 cur=0x7feac43abe08 lru=1 fl=15
      
  Bucket#187 seg=0x7feac43964d8 nit=5 nal=5 ips=5 sz=56 flg=3 ucnt=1
      
    0 cob=0x7feac43ac3f8 idx=bb flg=0 typ=1 cur=0x7feac43ac528 lru=1 fl=15
      
  Bucket#192 seg=0x7feac43965c8 nit=5 nal=5 ips=5 sz=56 flg=3 ucnt=1
      
    0 cob=0x7feac43043b8 idx=c0 flg=0 typ=2 cur=0x7feac43027b0 lru=1 fl=1
      
 。。。
     
       10g的版本trace文件：
      
 这里会记录hdl就是session对应的library cache object handle的地址。
      
 Session cached cursors
      
 -----------------------------------------------------------
      
 -------------- Generic Session Cached Cursors Dump --------
      
 -----------------------------------------------------------
      
 hash table=0xb7f31d00 cnt=3 LRU=0xb7f273f8 cnt=3 hit=4 max=20 NumberOfTypes=3
      
 type#0 name=KQD     count=1
      
 type#1 name=KQD BUN count=0
      
 type#2 name=KKS     count=2
      
  Bucket#056 seg=0xb7f323f4 nit=4 nal=4 ips=4 sz=16 flg=3 ucnt=1
      
    0 cob=0xb7f39d80 38 flg=0 typ=0 idx=38 cur=0xb7f39b9c lru=1 flg=5 hdl=0x34cb963c
      
    1 cob=0xb7f39d90 0 flg=0 typ=0 idx=0 cur=(nil) lru=0 flg=0 hdl=(nil)
      
    2 cob=0xb7f39da0 0 flg=0 typ=0 idx=0 cur=(nil) lru=0 flg=0 hdl=(nil)
      
    3 cob=0xb7f39db0 0 flg=0 typ=0 idx=0 cur=(nil) lru=0 flg=0 hdl=(nil)
      
  Bucket#168 seg=0xb7f331f4 nit=4 nal=4 ips=4 sz=16 flg=3 ucnt=1
      
    0 cob=0xb7f3b02c a8 flg=0 typ=2 idx=a8 cur=0xb7f4e2c0 lru=1 flg=1 hdl=0x34d8a490
      
    1 cob=0xb7f3b03c 0 flg=0 typ=0 idx=0 cur=(nil) lru=0 flg=0 hdl=(nil)
      
    2 cob=0xb7f3b04c 0 flg=0 typ=0 idx=0 cur=(nil) lru=0 flg=0 hdl=(nil)
      
    3 cob=0xb7f3b05c 0 flg=0 typ=0 idx=0 cur=(nil) lru=0 flg=0 hdl=(nil)
      
  Bucket#205 seg=0xb7f33694 nit=4 nal=4 ips=4 sz=16 flg=3 ucnt=1
      
    0 cob=0xb7f3b0fc cd flg=0 typ=2 idx=cd cur=0xb7f3b1b8 lru=1 flg=1 hdl=0x347beec8
      
    1 cob=0xb7f3b10c 0 flg=0 typ=0 idx=0 cur=(nil) lru=0 flg=0 hdl=(nil)
      
    2 cob=0xb7f3b11c 0 flg=0 typ=0 idx=0 cur=(nil) lru=0 flg=0 hdl=(nil)
      
    3 cob=0xb7f3b12c 0 flg=0 typ=0 idx=0 cur=(nil) lru=0 flg=0 hdl=(nil)
     
 还有一个参数cursor_space_for_time，该参数是为了缓解发生在child cursor上的与库缓存相关的latch争用，但是在oracle11GR1开始，mutex已经替代了各种与库缓存对象相关的latch，所以cursor_space_for_time参数在oracle 11.1.0.7及其后续的版本中已经过时。 
    
      oracle在解析执行sql时首先需要先获取库缓存相关的latch，然后再持有相关的cursor的library cache pin这个enqueue来pin住cursor，在sql执行完毕后oracle就会释放掉这个library cache pin，也就是相关的library cache对象会被置换出library cache，而如果有一个场景是：一个session cursor只会open和close一次，但是与之先关的child cursor中间会反复经历parse、bind、execution和fetch阶段，这样如果每次执行完毕后都需要反复的获取库缓存对象的latch和library cache pin，这样就会造成库缓存的latch争用。 
      
 注意：解析执行sql时首先获取library cache latch，在执行sql时为了防止sql对应的cursor被修改，则需要将这个sql相关的library cache对象pin到内存中，这个过程是持有library cache pin的过程，而在oracle11g后，持有library cache latch和library cache pin的过程已经被mutex所代替。
 cursor_space_for_time参数可以一定程度的解决库缓存的latch争用，当将此参数设置为true时，sql执行完毕后oracle也不会释放掉该sql相关的library cache pin，而是要等待游标关闭后才会释放掉相关sql的library cache pin，但是由于部分执行完毕的sql对应的child cursor并不能马上被age out出shared pool，从而导致shared pool的空间压力。
 
 下面简单的摘要一部分官方文档中对这个参数的描述 
    
      true 
      
 Shared SQL areas are kept pinned in the shared pool. As a result, shared SQL areas are not aged out of the pool as long as an open cursor references them. Because each active cursor’s SQL area is present in memory, execution is faster. However, the shared SQL areas never leave memory while they are in use. Therefore, you should set this parameter to true only when the shared pool is large enough to hold all open cursors simultaneously.
 In addition, a setting of true retains the private SQL area allocated for each cursor between executions instead of discarding it after cursor execution, saving cursor allocation and initialization time.
 false
 Shared SQL areas can be deallocated from the library cache to make room for new SQL statements.
 shared pool的内部结构远远比上面所列的要复杂，对于有些概念xiaoyu也不是理解的非常清晰，有兴趣的可以去看看相应的DSI部分。