Shared pool详解程序员

      

What is shared pool?

shared Pool当中主要包含了2部分：library cache和dictionary cache 也称为 row cache。

 

  Library cache包含了共享SQL区(shared SQL areas),私有SQL区(private SQLareas,如果配置了共享服务器),PL/SQL存储过程以及包，还有一些控制信息，比如说locks以及library cache handles。

Dictionary cache包含了表，视图的依赖信息，比如表结构，它的用户，Oracle在解析SQL的时候就会频繁的访问dictionary cache。包含了数据字典信息

Library cache 概述：

library cache最主要的功能就是存放用户提交的SQL语句、SQL语句相关的解析树（解析树也就是对SQL语句中所涉及到的所有对象的展现）、执行计划、用户提交的PL/SQL程序块（包括匿名程序块、存储过程、包、函数等）以及它们转换后能够被oracle执行的代码等。为了对这些内存结构进行管理，还存放了很多控制结构，包括lock、pin、dependency table等。
library cache还存放了很多的数据库对象的信息，包括表、索引等等。有关这些数据库对象的信息都是从dictionary cache中获得的。如果用户对library cache中的对象信息进行了修改，则这些修改会返回到dictionary cache中。
在library cache中存放的所有的信息单元都叫做对象（object），这些对象可以分成两类：一类叫存储对象，也就是上面所说的数据库对象。它们是通过显式的SQL语句或PL/SQL程序创建出来的，如果要删除它们，也必须通过显示的SQL命令进行删除。这类对象包括表、视图、索引、包、函数等等；另一类叫做过渡对象，也就是上面所说的用户提交的SQL语句或者提交的PL/SQL程序块等。这些过渡对象是在执行SQL语句或PL/SQL程序的过程中产生的，并缓存在内存里。如果实例关闭则删除，或者由于内存不足而被交换出去，从而被删除。

 

KGH Heap Manager 说明：

Shared pool和PGA都是由一个Oracle的内存管理器来管理，我们称之为KGH heap manager。Heap Manager不是一个进程，而是一串代码。Heap Manager主要目的就是满足server 进程请求memory 的时候分配内存或者释放内存。        

Heap Manager在管理PGA的时候，Heap Manager需要和操作系统来打交道来分配或者回收内存。但是呢，在shared pool中，内存是预先分配的，Heap Manager管理所有的空闲内存。

    当某个进程需要分配shared pool的内存的时候，Heap Manager就满足该请求，Heap Manager也和其他ORACLE模块一起工作来回收shared pool的空闲内存

 

Hash Table and Hash Bucket

Library cache是由一个hash table组成，这个hash table又由hash bucket组成的数组构成，每个hash bucket又是由一些相互指向的library cache handle所组成，library cache object handle就指向具体的library cache object以及一些引用列表。

Library Cache结构示意图如下：

 

Library Cache handle

我们对Library cache中所有对象的访问是通过利用library cache handle来实现的，也就是说我们想要访问library cache object，我们必须先找到library cache handle。Library cache handle指向library cache object,它包含了library object的名字，命名空间，时间戳，引用列表，lock对象以及pin对象的列表信息等等。Library cache handle也被library cache用来记录哪个用户在这个这个handle上有lock，或者是哪个用户正在等待获得这个lock。那么这里我们也知道了library cache lock是发生在handle上的。

    当一个进程请求library cache object, library cache manager就会应用一个hash 算法，从而得到一个hash 值，根据相应的hash值到相应的hash bucket中去寻找。这里的hash算法原理与buffer cache中快速定位block的原理是一样的。如果library cache object在内存中，那么这个library cache handle就会被找到。有时候，当shared pool不够大，library cache handle会保留在内存中，然而library cache heap由于内存不足被age out，这个时候我们请求的object heap就会被重载。最坏的情况下，library cache handle在内存中没有找到，这个时候就必须分配一个新的library cache handle，同时object heap也会被加载到内存中。

 

 

Library Cache Object

Library Cache Object是由一些独立的heap所组成，前面说了Library cache handle指向Library cache Object，其实handle是指向第一个heap,这个heap 我们就称之为heap 0。Heap 0记录了指向其他heap的指针信息。

 

 

Library cache lock/pin

Library cache lock/pin是用来控制对library cache object的并发访问的。Lock管理并发，pin管理一致性，lock是针对于library cache handle,而pin是针对于heap。

当我们想要访问某个library cache object，我们首先要获得这个指向这个object的handle的lock，获得这个lock之后我们就需要pin住指向这个object的heap。

当我们对包，存储过程，函数，视图进行编译的时候，Oracle就会在这些对象的handle上面首先获得一个library cache lock，然后再在这些对象的heap上获得pin，这样就能保证在编译的时候其它进程不会来更改这些对象的定义，或者将对象删除。

当一个session对SQL语句进行硬解析的时候，这个session就必须获得library cache lock，这样其他session就不能够访问或者更改这个SQL所引用的对象。如果这个等待事件花了很长时间，通常表明共享池太小(由于共享池太小，需要搜索free的chunk，或者将某些可以被移出的object page out，这样要花很长时间)，当然了，也有可能另外的session正在对object进行修改(比如split 分区),而当前session需要引用那个table，那么这种情况下我们必须等另外的session进行完毕。

Library Cache lock有3中模式：

l  Share(S):     当读取一个library cache object的时候获得

l  Exclusive(X): 当创建/修改一个library cache object的时候获得

l  Null(N)：     用来确保对象依赖性

 

比如一个进程想要编译某个视图，那么就会获得一个共享锁，如果我们要create/drop/alter某个对象，那么就会获得exclusive lock。Null锁非常特殊，我们在任何可以执行的对象(cursor，function)上面都拥有NULL锁，我们可以随时打破这个NULL锁，当这个NULL锁被打破了,就表示这个对象被更改了，需要从新编译。NULL锁主要的目的就是标记某个对象是否有效。比如一个SQL语句在解析的时候获得了NULL 锁，如果这个SQL的对象一直在共享池中，那么这个NULL锁就会一直存在下去，当这个SQL语句所引用的表被修改之后，这个NULL锁就被打破了，因为修改这个SQL语句的时候会获得Exclusive 锁，

由于NULL锁被打破了，下次执行这个SQL的时候就需要从新编译。

Library Cache pin有2种模式：

l  Share(S):     读取object heap

l  Exclusive(X)：修改object heap

 

Library Cache pin没有什么好说的，当某个session想要读取object heap，就需要获得一个共享模式的pin，当某个session想要修改object heap，就需要获得排他的pin。当然了在获得pin之前必须获得lock。

 

Library cache Latch

   Library cache latch用来控制对library cache object的并发访问。前面已经提到，我们要访问library cache object之前必须获得library cache lock， lock不是一个原子操作(原子操作就是在操作程中不会被打破的操作，很明显这里的lock可以被打破),Oracle为了保护这个lock，引入了library cache latch机制，也就是说在获得library cache lock之前，需要先获得library cache latch，当获得library cache lock之后就释放library cache latch。

如果某个library cache object没有在内存中，那么这个lock就不能被获取，这个时候需要获得一个library cache load lock latch，然后再获取一个library cache load lock,当load lock获得之后就释放library cache load lock latch。

当一条SQL语句被提交给Oracle执行，Oracle会自动执行以下的内存分配步骤：

1． Oracle检查共享池，看是否已经存在关于这条语句的共享SQL区。如果存在，这个共享SQL区就被用于执行这条语句。而如果不存在，Oracle就从共享池中分配一块新的共享SQL区给这条语句。同时，无论共享SQL区存在与否，Oracle都会为用户分配一块私有SQL区以保存这条语句相关信息（如变量值）。

2． Oracle为会话分配一个私有SQL区。私有SQL区的所在与会话的连接方式相关。

 

这里再介绍与共享池相关的一些重要参数。

 1.SHARED_POOL_SIZE

这个参数我们前面已经提到，它指定了Shared Pool的大小。在32位系统中，这个参数的默认值是8M，而64位系统中的默认值位64M。

但是，在SGA中还存在一块叫内部SGA消耗（Internal SGA Overhead）的内存被放置在共享池中。在9i及之前版本，共享池的统计大小（通过v$sgastat视图统计）为SHARED_POOL_SIZE + 内部SGA消耗大小。而10g以后，SHARED_POOL_SIZE就已经包含了这部分内存大小。因此在10g中，共享池的实际使用大小就是SHARED_POOL_SIZE – 内部SGA消耗大小，这在配置共享池大小时需要考虑进去，否则，扶过SHARED_POOL_SIZE设置过小，在实例启动时就会报ORA-00371错误。

 

2.  SHARED_POOL_RESERVED_SIZE

指定了共享池中缓存大内存对象的保留区的大小。

 

3. _SHARED_POOL_RESERVED_MIN_ALLOC

 

设置了进入保留区的对象大小的阀值。

**********************************************************************

最后，我们再介绍关于共享池的一些重要视图

 

 v$shared_pool_advice

这个视图与Oracle的另外一个优化建议器——共享池建议器——相关。我们可以根据这个视图里面oracle所做的预测数据来调整共享池大小。它的预测范围是从当前值的10%到200%之间。视图的结构如下

字段	数据类型	描述
SHARED_POOL_SIZE_FOR_ESTIMATE	NUMBER	估算的共享池大小（M为单位）
SHARED_POOL_SIZE_FACTOR	NUMBER	估算的共享池大小与当前大小比
ESTD_LC_SIZE	NUMBER	估算共享池中用于库缓存的大小（M为单位）
ESTD_LC_MEMORY_OBJECTS	NUMBER	估算共享池中库缓存的内存对象数
ESTD_LC_TIME_SAVED	NUMBER	估算将可以节省的解析时间。这些节省的时间来自于请求处理一个对象时，重新将它载入共享池的时间消耗和直接从库缓存中读取的时间消耗的差值。
ESTD_LC_TIME_SAVED_FACTOR	NUMBER	估算的节省的解析时间与当前节省解析时间的比。
ESTD_LC_MEMORY_OBJECT_HITS	NUMBER	估算的可以直接从共享池中命中库缓存的内存对象的命中次数。

关于如何根据建议器采用合理的共享池大小的方法，和前面提到的缓冲区建议器的使用方法类似，不再赘述。

·        V$SHARED_POOL_RESERVED

前面提到了这个视图。这个视图存放了共享池保留区的统计信息。可以根据这些信息来调整保留区。视图结构如下：

Column	Datatype	Description
以下字段只有当参数SHARED_POOL_RESERVED_SIZE设置了才有效。
FREE_SPACE	NUMBER	保留区的空闲空间数。
AVG_FREE_SIZE	NUMBER	保留区的空闲空间平均数。
FREE_COUNT	NUMBER	保留区的空闲内存块数
MAX_FREE_SIZE	NUMBER	最大的保留区空闲空间数。
USED_SPACE	NUMBER	保留区使用空间数。
AVG_USED_SIZE	NUMBER	保留区使用空间平均数。
USED_COUNT	NUMBER	保留区使用内存块数。
MAX_USED_SIZE	NUMBER	最大保留区使用空间数
REQUESTS	NUMBER	请求再保留区查找空闲内存块的次数。
REQUEST_MISSES	NUMBER	无法满足查找保留区空闲内存块请求，需要从LRU列表中清出对象的次数。
LAST_MISS_SIZE	NUMBER	请求的内存大小，这次请求是最后一次需要从LRU列表清出对象来满足的请求。
MAX_MISS_SIZE	NUMBER	所有需要从LRU列表清出对象来满足的请求中的内存最大大小
以下字段无论参数SHARED_POOL_RESERVED_SIZE是否设置了都有效。
REQUEST_FAILURES	NUMBER	没有内存能满足的请求次数（导致4031错误的请求）
LAST_FAILURE_SIZE	NUMBER	没有内存能满足的请求所需的内存大小（导致4031错误的请求）
ABORTED_REQUEST_THRESHOLD	NUMBER	不清出对象的情况下，导致4031错误的最小请求大小。
ABORTED_REQUESTS	NUMBER	不清出对象的情况下，导致4031错误的请求次数。。
LAST_ABORTED_SIZE	NUMBER	不清出对象的情况下，最后一次导致4031错误的请求大小。

我们可以根据后面4个字段值来决定如何设置保留区的大小以避免4031错误的发生。

·         v$db_object_cache

这一视图显示了所有被缓存在library cache中的对象，包括表、索引、簇、同义词、PL/SQL存储过程和包以及触发器。

字段	数据类型	说明
OWNER	VARCHAR2(64)	对象所有者
NAME	VARCHAR2(1000)	对象名称
DB_LINK	VARCHAR2(64)	如果对象存在db link的话，db link的名称
NAMESPACE	VARCHAR2(28)	库缓存的对象命名空间，包括： TABLE/PROCEDURE, BODY, TRIGGER, INDEX, CLUSTER, OBJECT, CURSOR, INVALID NAMESPACE, JAVA SHARED DATA, PUB_SUB, RSRC CONSUMER GROUP
TYPE	VARCHAR2(28)	对象类型，包括：INDEX, TABLE, CLUSTER, VIEW, SET, SYNONYM, SEQUENCE, PROCEDURE, FUNCTION, PACKAGE, PACKAGE BODY, TRIGGER, CLASS, OBJECT, USER, DBLINK, CURSOR, JAVA CLASS, JAVA SHARED DATA, NON-EXISTENT, NOT LOADED, PUB_SUB, REPLICATION OBJECT GROUP, TYPE
SHARABLE_MEM	NUMBER	对象消耗的共享池中的共享内存
LOADS	NUMBER	对象被载入次数。即使对象被置为无效了，这个数字还是会增长。
EXECUTIONS	NUMBER	对象执行次数，但本视图中没有被使用。可以参考视图v$sqlarea中执行次数。
LOCKS	NUMBER	当前锁住这个对象的用户数（如正在调用、执行对象）。
PINS	NUMBER	当前pin住这个对象的用户数（如正在编译、解析对象）。
KEPT	VARCHAR2(3)	对象是否被保持，即调用了DBMS_SHARED_POOL.KEEP来永久将对象pin在内存中。 (YES | NO)
CHILD_LATCH	NUMBER	正在保护该对象的子latch的数量。

·        v$sql、v$sqlarea 、v$sqltext

这三个视图都可以用于查询共享池中已经解析过的SQL语句及其相关信息。

V$SQL中列出了共享SQL区中所有语句的信息，它不包含GROUP BY字句，并且为每一条SQL语句中单独存放一条记录；

V$SQLAREA中一条记录显示了一条共享SQL区中的统计信息。它提供了有在内存中、解析过的和准备运行的SQL语句的统计信息；

V$SQLTEXT包含了库缓存中所有共享游标对应的SQL语句。它将SQL语句分片显示。

下面介绍一下我常用的V$SQLAREA的结构：

字段	数据类型	说明
SQL_TEXT	VARCHAR2(1000)	游标中SQL语句的前1000个字符。
SHARABLE_MEM	NUMBER	被游标占用的共享内存大小。如果存在多个子游标，则包含所有子游标占用的共享内存大小。
PERSISTENT_MEM	NUMBER	用于一个打开这条语句的游标的生命过程中的固定内存大小。如果存在多个子游标，则包含所有子游标生命过程中的固定内存大小。
RUNTIME_MEM	NUMBER	一个打开这条语句的游标的执行过程中的固定内存大小。如果存在多个子游标，则包含所有子游标执行过程中的固定内存大小。
SORTS	NUMBER	所有子游标执行语句所导致的排序次数。
VERSION_COUNT	NUMBER	缓存中关联这条语句的子游标数。
LOADED_VERSIONS	NUMBER	缓存中载入了这条语句上下文堆（KGL heap 6）的子游标数。
OPEN_VERSIONS	NUMBER	打开语句的子游标数。
USERS_OPENING	NUMBER	打开这些子游标的用户数。
FETCHES	NUMBER	SQL语句的fetch数。
EXECUTIONS	NUMBER	所有子游标的执行这条语句次数。
USERS_EXECUTING	NUMBER	通过子游标执行这条语句的用户数。
LOADS	NUMBER	语句被载入和重载入的次数
FIRST_LOAD_TIME	VARCHAR2(19)	语句被第一次载入的时间戳。
INVALIDATIONS	NUMBER	所以子游标的非法次数。
PARSE_CALLS	NUMBER	所有子游标对这条语句的解析调用次数。
DISK_READS	NUMBER	所有子游标运行这条语句导致的读磁盘次数。
BUFFER_GETS	NUMBER	所有子游标运行这条语句导致的读内存次数。
ROWS_PROCESSED	NUMBER	这条语句处理的总记录行数。
COMMAND_TYPE	NUMBER	Oracle命令类型代号。
OPTIMIZER_MODE	VARCHAR2(10)	执行这条的优化器模型。
PARSING_USER_ID	NUMBER	第一次解析这条语句的用户的ID。
PARSING_SCHEMA_ID	NUMBER	第一次解析这条语句所用的schema的ID。
KEPT_VERSIONS	NUMBER	所有被DBMS_SHARED_POOL包标识为保持（Keep）状态的子游标数。
ADDRESS	RAW(4 | 8)	指向语句的地址
HASH_VALUE	NUMBER	这条语句在library cache中hash值。
MODULE	VARCHAR2(64)	在第一次解析这条语句是通过调用DBMS_APPLICATION_INFO.SET_MODULE设置的模块名称。
MODULE_HASH	NUMBER	模块的Hash值
ACTION	VARCHAR2(64)	在第一次解析这条语句是通过调用DBMS_APPLICATION_INFO.SET_ACTION设置的动作名称。
ACTION_HASH	NUMBER	动作的Hash值
SERIALIZABLE_ABORTS	NUMBER	所有子游标的事务无法序列化的次数，这会导致ORA-08177错误。
IS_OBSOLETE	VARCHAR2(1)	游标是否被废除（Y或N）。当子游标数太多了时可能会发生。
CHILD_LATCH	NUMBER	为了包含此游标的子latch数。

查看当前会话所执行的语句以及会话相关信息：

SQL> select a.sid||’.’||a.SERIAL#, a.username, a.TERMINAL, a.program, s.sql_text

  2  from v$session a, v$sqlarea s

  3  where a.sql_address = s.address(+)

  4  and a.sql_hash_value = s.hash_value(+)

  5  order by a.username, a.sid;

 

… …

 

SQL>

·        v$sql_plan

视图V$SQL_PLAN包含了library cache中所有游标的执行计划。通过结合v$sqlarea可以查出library cache中所有语句的查询计划。先从v$sqlarea中得到语句的地址，然后在由v$sql_plan查出它的查询计划：

SQL> select lpad(‘ ‘, 2*(level-1))||operation “Operation”,

  2         options “Options”,

  3         decode(to_char(id), ‘0’, ‘Cost=’||nvl(to_char(position), ‘n/a’), object_name) “Object Name”,

  4         substr(optimizer, 1, 6) “Optimizer”

  5   from v$sql_plan a

  6  start with address = ‘C0000000FCCDEDA0’

  7  and id = 0

  8  connect by prior id = a.parent_id

  9  and prior a.address = a.address

 10  and prior a.hash_value = a.hash_value;

 

Operation           Options              Object Name          Optimizer

——————- ——————– ——————– ———

SELECT STATEMENT                         Cost=0               CHOOSE

  NESTED LOOPS 

    INDEX           RANGE SCAN           CSS_BL_CNTR_IDX1     ANALYZ

    INDEX           RANGE SCAN           CSS_BKG_BL_ASSN_UQ1  ANALYZ

 

SQL>

·        v$librarycache

这个视图包含了关于library cache的性能统计信息，对于共享池的性能调优很有帮助。它是按照命名空间分组统计的，结构如下：

字段	数据类型	说明
NAMESPACE	VARCHAR2(15)	library cache的命名空间
GETS	NUMBER	请求GET该命名空间中对象的次数。
GETHITS	NUMBER	请求GET并在内存中找到了对象句柄的次数（锁定命中）。
GETHITRATIO	NUMBER	请求GET的命中率。
PINS	NUMBER	请求pin住该命名中对象的次数。
PINHITS	NUMBER	库对象的所有元数据在内存中被找到的次数（pin命中）。
PINHITRATIO	NUMBER	Pin命中率。
RELOADS	NUMBER	Pin请求需要从磁盘中载入对象的次数。
INVALIDATIONS	NUMBER	命名空间中的非法对象（由于依赖的对象被修改所导致）数。
DLM_LOCK_REQUESTS	NUMBER	GET请求导致的实例锁的数量。
DLM_PIN_REQUESTS	NUMBER	PIN请求导致的实例锁的数量.
DLM_PIN_RELEASES	NUMBER	请求释放PIN锁的次数。
DLM_INVALIDATION_REQUESTS	NUMBER	GET请求非法实例锁的次数。
DLM_INVALIDATIONS	NUMBER	从其他实例那的得到的非法pin数。

其中PIN的命中率（或未命中率）是我们系统调优的一个重要依据：

SQL> select sum(pins) “hits”,

  2         sum(reloads) “misses”,

  3         sum(pins)/(sum(pins)+sum(reloads)) “Hits Ratio”

  4  from v$librarycache;

 

      hits     misses Hits Ratio

———- ———- ———-

  84962803        288 0.99999661

 

SQL> 

SQL> select sum(pins) “hits”,

  2         sum(reloads) “misses”,

  3         ((sum(reloads)/sum(pins))*100) “Reload%”

  4  from v$librarycache;

 

      hits     misses    Reload%

———- ———- ———-

  84963808        288 0.00033896

 

SQL>

当命中率小于99%或未命中率大于1%时，说明系统中硬解析过多，要做系统优化（增加Shared Pool、使用绑定变量、修改cursor_sharing等措施，性能优化不是本文重点，不再赘述）。

 

 

·          v$library_cache_memory

 

这个视图显示了各个命名空间中的库缓存内存对象的内存分配情况。一个内存对象是为了高效管理而组织在一起的一组内部内存。一个库对象可能包含多个内存对象。

字段	数据类型	说明
LC_NAMESPACE	VARCHAR2(15)	Library cache命名空间
LC_INUSE_MEMORY_OBJECTS	NUMBER	属于命名空间并正被在共享池使用的内存对象数。
LC_INUSE_MEMORY_SIZE	NUMBER	正在使用的内存对象的大小总（M未单位）。
LC_FREEABLE_MEMORY_OBJECTS	NUMBER	共享池中空闲的内存对象数。
LC_FREEABLE_MEMORY_SIZE	NUMBER	空闲内存对象的大小总和（M为单位）。

·        v$sgastat

这个视图前面介绍过，是关于SGA使用情况的统计。其中，关于Shared Pool有详细的统计数据。