Oracle shared pool internals 猜

Oracle shared pool 主要分为library cache 、dictionary cache 和control structure 三个部分。

第一部分library cache 保存已执行的SQL 和PL/SQL 语句，和它们的执行计划等信息。

library cache 管理机制是采用一种hash 算法，实现library cache object 的快速查找和保存。

该hash 算法将library cache  空间定义为hash table 。hash table 中由多个bucket 组成，每个bucket 中，又由一个或多个library cache object handle 组成。library cache object handle 包含了library cache object 、名称等信息。通过handle 可以找到library cache object 和它们的内容。

 

（注：hash table  有多个hash bucket 组成，形成数组。hash bucket 有一个或多个object handle 组成。同一个bucket 中的不同的object handle 对象之间的关系是怎么样的？是数组还是链表？）

 

(miki西游 @mikixiyou 原文链接: http://mikixiyou.iteye.com/blog/1661948 )

 

library cache object handle 的结构如下：

• handle addr
• name
• namespace
• lock owners
• lock waiters
• pin owners
• pin waits
• flag
• heap 0(Object)

 

在library cache object handle 中，heap 0 又称object ，是一个指向对象的指针。heap 0 指向的对象的结构为：

• object type
• object name
• flags
• tables
• data blocks

 

在heap 0 中，tables 和data blocks 也是指向其他两个对象的指针。

tables 指向的对象的结构为：

• dependency table
• child table
• translation table
• authorization table
• access table
• r-o dependency table
• schema name table

 

data blocks 指向的对象的结构为：

• object
•  source
• diana
• pcode
• mcode
• errors
• sql context 。

 

其中source 也称为heap 1 用于存储sql 文本,sql context 称为heap 6 用于存储SQL 执行计划。

 

 

 

手工将一个SQL 的类型为CRSR 的object handle 从内存中dump 出来。分别为parent cursor 和child cursor ，分别在不同bucket 。

在parent cursor 的children 栏中有指向child cursor 的handle 地址。

 

BUCKET 5944:

   LIBRARY OBJECT HANDLE: handle=1895899e8 mtx=0x189589b18(1) cdp=1

   name=select* from sys.obj$ where obj#=:x

   hash=8cd0bd4dbb09ea7c3d007f1ce9b01738 timestamp=08-21-2012 10:02:35

   namespace= CRSR  flags=RON/KGHP/TIM/PN0/SML/KST/DBN/MTX/[120100d0]

   kkkk-dddd-llll=0000-0001-0001 lock=N pin=0 latch#=6 hpc=0002 hlc=0002

   lwt=0x189589a90[0x189589a90,0x189589a90] ltm=0x189589aa0[0x189589aa0,0x189589aa0]

   pwt=0x189589a58[0x189589a58,0x189589a58] ptm=0x189589a68[0x189589a68,0x189589a68]

   ref=0x189589ac0[0x189589ac0,0x189589ac0] lnd=0x189589ad8[0x189589ad8,0x189589ad8]

     LOCK OWNERS:

         lock      user   session count mode flags

     -------- -------- -------- ----- ---- ------------------------

     17f72e938 1852c9890 1852c9890      1 N     [00]

     LIBRARY OBJECT: object=1786e6168

     type=CRSR flags=EXS[0001] pflags=[0000] status=VALD load=0

     CHILDREN: size=16

     child#     table reference    handle

     ------ -------- --------- --------

          0 17816fa10 17816f680 144768330

     DATA BLOCKS:

     data#      heap   pointer     status pins change whr

     ----- -------- -------- --------- ---- ------ ---

         0 144773248 1786e6280 I/P/A/-/-     0 NONE    00

   BUCKET 5944 total object count=1 

 

 

 

 

  

LIBRARY OBJECT HANDLE: handle=144768330 mtx=0x144768460(0) cdp=0

   namespace=CRSR flags=RON/KGHP/PN0/EXP/[10010100]

   kkkk-dddd-llll=0000-0001-0001 lock=N pin=0 latch#=6 hpc=fffe hlc=fffe

   lwt=0x1447683d8[0x1447683d8,0x1447683d8] ltm=0x1447683e8[0x1447683e8,0x1447683e8]

   pwt=0x1447683a0[0x1447683a0,0x1447683a0] ptm=0x1447683b0[0x1447683b0,0x1447683b0]

   ref=0x144768408[0x17816f680,0x17816f680] lnd=0x144768420[0x144768420,0x144768420]

     CHILD REFERENCES:

     reference latch flags

     --------- ----- -------------------

     17816f680     11 CHL[02]

     LOCK OWNERS:

         lock      user   session count mode flags

     -------- -------- -------- ----- ---- ------------------------

     17f72f1d8 1852c9890 1852c9890      1 N     [00]

     LIBRARY OBJECT: object=178161a10

     type=CRSR flags=EXS[0001] pflags=[0000] status=VALD load=0

     DEPENDENCIES: count=1 size=16

     dependency#     table reference    handle position flags

     ----------- -------- --------- -------- -------- -------------------

               0 17837dae8 17837d828 189addbb8        17 DEP[01]

     AUTHORIZATIONS: count=1 size=16 minimum entrysize=16

     00000000 00000000 00020000 00000000

     ACCESSES: count=1 size=16

     dependency# types

     ----------- -----

               0 0009

     SCHEMA: count=1 size=262144

     00000000

     DATA BLOCKS:

     data#      heap   pointer     status pins change whr

     ----- -------- -------- --------- ---- ------ ---

         0 144e55298 178161b28 I/P/A/-/-     0 NONE    00

         6 1786cd5a0 16ece5dc0 I/-/A/-/E     0 NONE    00 

 

 

 

 

 

Oracle  执行每一个SQL ，都在shared pool 的library cache 中。library cache 中的对象，使用hash table 方式管理。

当一个SQL 第一次发送到Oracle Server 时，Oracle 首先会将该SQL 转换成ASCII 码，然后通过hash 算法得到一个hash 值。这就是经常看到的存在于v$sql 和v$sqlarea 的hash_value 。

Oracle 会请求一个shared pool latch 在library cache 中分配一个存储空间，然后释放该shared pool latch ，再请求一个library cache latch ，将这个SQL 写入到library cache 中的相应的bucket 中。这时生成的对象称为library cache object handle ，它的namespace 值也就是type 的值称为CRSR 。

类型为CRSR 的library cache object handle 的对象有两个，分别PARENT CRSR 和CHILD CRSR 。这也就是我们在SQL 优化中常见的CURSOR 。

类型为PARENT CURSOR 的library cache object handle 对应的动态性能视图为v$sqlarea 。

类型为child cursor 的library cache object handle 对应的动态性能视图是v$sql 。

可以试着这样理解一下。parent cursor 保存SQL 文本，一条SQL 就是一个parent cursor 。child cursor 和parent cursor 的子类，保存不同用户、不同优化器模式、不同索引，不同参数传入值等而生成的SQL 执行计划、执行次数、执行时间、执行逻辑读和物理读等信息。

因此，同一个SQL ，会生成多个child cursor 和一个parent cursor 。

每生成一个child cursor ，在parent cursor 就记录为一个version 。在v$sqlarea.version­_count 中记录的数字是所有曾生成过的child cursor 。

在v$sqlarea 中，有些字段是v$sql 的字段的聚合值，如fetchs,executions,buffer_gets,disk_reads 。

 

当这个SQL 第二次发送到Oracel Server 中，生成的hash_value 值，在parent cursor ，即libarary cache object handle 的链表，也就是v$sqlarea 中找到符合的记录时，会继续到child cursor 中去找，如有此SQL 相关信息完全相同的记录时，则共享使用该child cursor ，这在oracle 会记录为一次soft parse 。如果在child cursor 中没找到可以共享的记录，则会生成一个child cursor ，在parent cursor 中会增加一个version ，这在oracle 会记录为一次hard parse 。

在soft parse 中，还有一种情况，就是child cursor 被共享3 次（此值属于道听途说，不确定）后，oracle 会将此child cursor 放到session cached cursor 中。Oracle 在找child cursor 之前会在session cached cursor 链表中去找，有符合条件的记录则会记录一次soft soft parse 。

简而言之，在session cached cursor 中找到，则称为soft soft parse ，若在child cursor 中找到则称为soft parse ，都没有找到，则生成一个child cursor ，称为hard parse 。

在Oracle 中，library cache object handle 的hash table 是一个逻辑概念，dump 出来的shared pool 内容也不全面，v$sql 和v$sqlarea 也仅仅是一个查看对象的视角。窥豹一斑，也可能是盲人摸象。需要理解Oracle 的内部机制，真是不容易。