Oracle Shared pool 详解

一. Shared Pool 概述

            在之前的blog对Oracle 的内存架构也做了一个概述，参考：

                        Oracle 内存 架构 详解

                        http://blog.csdn.net/tianlesoftware/archive/2010/05/15/5594080.aspx

            在网上搜到一篇介绍shared pool 非常详细的pdf资料。 原文链接以找不到，但还是要感谢作者Kamus的辛勤劳动。

            结合Kamus 的pdf和csdn 网友的blog，重新整理了一下，链接如下：

            http://blog.csdn.net/robinson1988/archive/2010/11/26/6037925.aspx

IINTRODUCT IION

            What is shared pool? This first query that comes,let us have a brief introduction regarding shared pool here first.Most of the people knows that shared pool is the part of System Global Area (SGA) it’s true but little else, What exactly the shared pool?

            Shared pool are contain lots of key memory areas of Oracle and in Instance tuning the major area that we have to tune is shared pool if shared pool defined improperly the overall database performance will suffer.

Majority shared pool related to the two part of SGA one is fixed are which is relatively constant to a oracle instance for a particular version and the second part is Variable area which gradually shrink and grow for user and application requirement.

Now we should do a close look of various component of Shared Pool

Basically Shared Pool could be divided in three major parts:

            1. Library Cache

            2. Dictionary Cache

            3. Control Structure

 

二.  Library Cache

            Memory Part where all the SQL and PL/SQL statement executed, all execution plan reside here for SQL statement stored here.

 

We can further subdivide this Library Chache into:

            1. Shared and Private SQL Area

            2. PL/SQL Procedure Part

 

2.1 Shared and Private SQL Area

            A shared SQL area contains the parse tree and execution plan for a single SQL statement, or for similar SQL statements. Oracle saves memory by using one shared SQL area for multiple similar DML statements, particularly when many users execute the same application.

            A shared SQL area is always in the shared pool. Oracle allocates memory from the shared pool when a SQL statement is parsed; the size of this memory depends on the complexity of the statement. If a SQL statement requires a new shared SQL area and the entire shared pool has already been allocated, Oracle can deallocate items from the pool using a modified least Recently used algorithm until there is enough free space for the new statement's shared SQL area.

 

            A private SQL area contains data such as bind information and runtime buffers. Each session that issues a SQL statement has a private SQL area. Each user that submits an identical SQL statement has his or her own private SQL area that uses a single shared SQL area; many private SQL areas can be associated with the same shared SQL area.

 

A private SQL area has a persistent area and a runtime area:

            （1）The persistent area contains bind information that persists across executions, code for datatype conversion (in case the defined datatype is not the same as the datatype of the selected column), and other state information (like recursive or remote

cursor numbers or the state of a parallel query). The size of the persistent area depends on the number of binds and columns specified in the statement. For example, the persistent area is larger if many columns are specified in a query.

            （2）The runtime area contains information used while the SQL statement is being executed. The size of the runtime area depends on the type and complexity of the SQL statement being executed and on the sizes of the rows that are processed by the statement. In general, the runtime area is somewhat smaller for INSERT, UPDATE and DELETE statements than it is for SELECT statements, particularly when the SELECT statement requires a sort.

            Oracle creates the runtime area as the first step of an execute request. For INSERT, UPDATE, and DELETE statements, Oracle frees the runtime area after the statement has been executed.  For queries, Oracle frees the runtime area only after all rows are fetched or the query is cancelled.

            The location of a private SQL area depends on the type of connection established for a session. If a session is connected via a dedicated server, private SQL areas are located in the user's PGA. However, if a session is connected via the multithreaded server, the persistent areas and, for SELECT statements, the runtime areas, are kept in the SGA (x$ksmms) table provide the runtime information regarding SQL area in Library Cache which is suppose to be allocated to a particular Oracle Instance。

 

E.g.

/* Formatted on 2011/6/21 10:18:48 (QP5 v5.163.1008.3004) */

SELECT *

  FROM X$KSMSS

 WHERE KSMSSNAM = 'sql_area' AND KSMSSLEN <> 0;

 

2.2 PL/SQL Procedure Part

            Oracle processes PL/SQL program units (procedures, functions, packages, anonymous blocks, and database triggers) much the same way it processes individual SQL statements.

            Oracle allocates a shared area to hold the parsed, compiled form of a program unit. Oracle allocates a private area to hold values specific to the session that executes the program unit, including local, global, and package variables (also known as package instantiation) and buffers for executing SQL. If more than one user executes the same program unit, then a single, shared area is used by all users, while each user maintains a separate copy of his or her private SQL area, holding values specific to his or her session.

            Individual SQL statements contained within a PL/SQL program unit are processed as described in the previous sections. Despite their origins within a PL/SQL program unit, these SQL statements use a shared area to hold their parsed representations and a private area for each session that executes the statement.

            (x$ksmms) table provide the runtime information regarding PL/SQL area in Library Cache which is suppose to be allocated to a particular Oracle Instance 。

E.g.

/* Formatted on 2011/6/21 10:39:11 (QP5 v5.163.1008.3004) */

SELECT *

  FROM X$KSMSS

 WHERE KSMSSNAM LIKE 'PL/SQL%' AND KSMSSLEN <> 0;

 

PL/SQL MPCODE stands for machine dependent pseudocode.

PL/SQL DIANA stands for the PL/SQL code size in the shared pool at runtime.

 

2.3  Library Cache Manager

            The main purpose of the library cache is to provide a mechanism to locate and store any library cache object quickly. A hashing mechanism is used to locate a handle, which contains the identity (name) of the object. The library cache handle then points us to one or more the library cache objects and their contents.  

            The library cache caches different types of library objects (e.g. packages, procedures, functions, shared cursors, anonymous PL/SQL blocks, table definitions, view definitions, form definitions).

            Library cache memory is allocated out of the top most heap or the generic SGA heap. When the library cache, KGL, needs more memory, it will call the heap manager (KGH) to allocate it. The library cache consists of a hash table, which consists of an array of hash buckets. Each hash bucket is a doubly linked list of library cache object handles. Each library cache object handle points to a library cache object and has a reference list. The library cache object is further broken down into other components such as a dependency table, a child table, and an authorisation table (to name a few).

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的空闲内存。

 

2.4  Library Cache Manager (Hash Table and Hash Bucket)

 

            Library cache Manager 可以看做是Heap Manager的客户端，因为library cache manager是根据Heap Manager来分配内存从而存放library cache objects。Library cache Manager控制所有的library cache object，包括package/procedure, cursor, trigger等等。

           

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

 

            The hash table is an array of hash buckets. The initial number of the hash buckets is 251; however, the number of buckets will increase when the number of objects in the table exceeds the next number.

            The next numbers are the next higher prime value. They are 251, 509, 1021,2039, 4093, 8191, 16381, 32749, 65521, 131071,and 4292967293 where the "n+1"th size is approximately twice the "n"th size. The resulting expansion of the hash table will involve allocating a new hash table at the next prime size, rehashing the library cache objects from the old table to the new table, and freeing the space allocated from the old hash table. Throughout this procedure, access to the hash table is blocked (by freezing access to the child latches) as one user allocates new buckets to double the size of the hash table and then uses the least significant bits of the hash value to determine which new bucket a handle belongs to. Contrary to common belief, this is a rare and inexpensive operation that may cause a short (approximately 3-5 second) hiccup in the system.

            The hash table never shrinks. The library cache manager will apply a modulo hash function to a given object’s namespace, object name, owner, and database link to determine the hash bucket where the object should be found.

 

            It then walks down the corresponding linked list to see if the object is there. If the object does not exist, the library cache manager will create an empty object with the given name, insert it in the hash table, and request the client load it by calling the client's environment dependent load function.

            Basically, the client would read from disk, call the heap manager to allocate memory, and load the object.

 

2.5 Library Cache Handle

            A library cache handles points to a library cache object. It contains the name of the library object, the namespace, a timestamp, a reference list, a list of locks locking the object and a list of pins pinning the object. Each object is uniquely identified by the name within its namespace.

 

            对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也会被加载到内存中。

 

查看namespace:

SYS@anqing2(rac2)> select namespace from v$librarycache;

NAMESPACE

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

SQL AREA

TABLE/PROCEDURE

BODY

TRIGGER

INDEX

CLUSTER

OBJECT

PIPE

JAVA SOURCE

JAVA RESOURCE

JAVA DATA

在AWR 里有关Library Cache Activity 的统计信息：

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

 

The library cache manager will generate a name for every object, even anonymous PL/SQL blocks. The handle uses namespaces to partition library cache objects by types.

 

            These are examples of different types of namespaces: one namespace holds all namespaces depended on PL/SQL objects, one for package bodies and table bodies, one for shared cursors; one for triggers; one for indexes; and one for clusters. The namespace describes the type of an item kept in the library cache.

            The name consists of the object owner name, the object name, the database link name, and the database link owner name. A comprehensive list can be viewed from v$librarycache.

 

            A handle can be freed if there are no current references to it and it has not been expressly marked to be kept. We use this to determine when a handle should be unpinned in memory.

2.6  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中模式：

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

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

            （3）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种模式：

            （1）Share(S):      读取object heap

            （2）Exclusive(X)： 修改object heap

 

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

 

            在Oracle10gR2中，library cache pin被library cache mutex 所取代。

 

2.7  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。

 

            library cache latch受隐含参数_KGL_LATCH_COUNT的控制，默认值为大于等于系统中CPU个数的最小素数，但是Oracle对其有一个硬性限制，该参数不能大于67。

            注意：我们去查询_kgl_latch_count有时候显示为0,这是一个bug。

 

Oracle利用下面算法来确定library cache object handle是由哪个子latch来保护的：

            latch# = mod(bucket#, #latches)

 

            也就是说用哪个子latch去保护某个handle是根据那个handle所在的bucket号，以及总共有多少个子latch来进行hash运算得到的。

有关其他内容，参考：

            Oracle Latch 说明

            http://blog.csdn.net/tianlesoftware/archive/2010/01/27/5263238.aspx

            Oracle Mutex 机制 说明

            http://blog.csdn.net/tianlesoftware/archive/2011/05/30/6455517.aspx

            锁 死锁 阻塞 Latch 等待 详解

            http://blog.csdn.net/tianlesoftware/archive/2010/08/19/5822674.aspx

三.  Dictionary Cache

            Table definition against which an application user suppose to do a query, it include table’s associated Index and Columns and privilege information regarding table as we as columns.

/* Formatted on 2011/6/21 15:01:52 (QP5 v5.163.1008.3004) */
SELECT *
  FROM X$KSMSS
 WHERE KSMSSNAM LIKE 'PL/SQL%' AND KSMSSLEN <> 0;

四.  Control Structure

            This contain the information regarding Internal Latch and Locks (Data Structure), it also contain buffer header, the process session and transaction arrays.

            The size of these arrays depends on the setting of Initialisation parameter of Init.ora file and can’t be changed without shutting down the database.

 

4.1 Shared Pool Chunks

            Have close looks of Shared Pool, For that we should have a close look at x$ksmsp each row in this table shows a chunk of shared pool :

When each shared pool chunk is allocated the code is passed to a function that does the work of allocation and this address is visible to KSMCHCOM column,which describe the purpose of allocation. This chunk is supposed to be larger then the size of the object as it also contains the header information.

 

            The column KSMCHCLS represent the class, there are basically four type of classes:

            Freeabl :  can be freed only contain the objects needed for the session call.

            Free :  free and not contained by valid object.

            Recr :  contain by temporary objects.

            Perm :  contained by the permanent object.

/* Formatted on 2011/6/21 16:14:50 (QP5 v5.163.1008.3004) */
  SELECT KSMCHCLS CLASS,
         COUNT (KSMCHCLS) NUM,
         SUM (KSMCHSIZ) SIZ,
         TO_CHAR ( ( (SUM (KSMCHSIZ) / COUNT (KSMCHCLS) / 1024)), '999,999.00')
         || 'k'
            "AVG SIZE"
    FROM X$KSMSP
GROUP BY KSMCHCLS;

注意：

            在生产库上查询X$KSMSP时，要看下系统的繁忙或者说是负载高低，因为可能会导致db hang 住。

So the overall summary :
 
/* Formatted on 2011/6/21 16:06:38 (QP5 v5.163.1008.3004) */
  SELECT KSMCHCOM name,
         COUNT (KSMCHCOM),
         SUM (DECODE (KSMCHCLS, 'recr', KSMCHSIZ)) RECREATABLE,
         SUM (DECODE (KSMCHCLS, 'freeabl', KSMCHSIZ)) FREEABLE
    FROM x$ksmsp
GROUP BY KSMCHCOM;

 

http://www.cnblogs.com/springside-example/archive/2011/06/22/2529711.html Oracle Shared pool 详解

http://www.linuxidc.com/Linux/2011-07/39424p3.htm Oracle Library cache 内部机制 说明

http://doc.chinaunix.net/oracle/200709/203809.shtml Shared pool深入分析及性能调整

http://wenku.baidu.com/view/6842ebba960590c69ec37673.html 百度文库 shared pool