buffer pool和shared pool详解（之四，重要视图、以及转储），bufferpool
1.2.5 X$KSMSP视图
Shared Pool 的空间分配和使用情况，可以通过一个内部视图来观察，这个视图就是X$KSMSP。
X$KSMSP的名称含义为: [K]ernal [S]torage [M]emory Management [S]GA Hea[P]其中每一行都代表着Shared Pool中的一个Chunk。以下是x$ksmsp的结构:
12:03:45 sys@felix SQL>desc x$ksmsp
Name Null? Type
---------------------------- -------------------------
ADDR RAW(8)
INDX NUMBER
INST_ID NUMBER
KSMCHIDX NUMBER
KSMCHDUR NUMBER
KSMCHCOM VARCHAR2(16)
KSMCHPTR RAW(8)
KSMCHSIZ NUMBER
KSMCHCLS VARCHAR2(8)
KSMCHTYP NUMBER
KSMCHPAR RAW(8)
12:06:29 sys@felix SQL>
这里需要关注一下以下几个字段。
（1）x$ksmsp.ksmchcom是注释字段，每个内存块被分配以后，注释会添加在该字段中。
（2）x$ksmsp.ksmchsiz代表块大小。
（3）x$ksmsp.ksmchcls列代表类型，主要有4类，具体说明如下。
（1）free：即Free Chunks，不包含任何对象的Chunk，可以不受限制的被自由分配。
（2）recr：即Recreatable Chunks，包含可以被临时移出内存的对象，在需要的时候，这个对象可以被重新创建。例如，许多存储共享SQL代码的内存都是可以重建的。
（3）freeable：即Freeable Chunks，包含session周期或调用的对象，随后可以被释放。这部分内存有时候可以全部或部分提前释放。但是注意，由于某些对象是中间过程产生的，这些对象不能临时被移出内存（因为不可重建）。
（4）perm：即Permanent Memory Chunks，包含永久对象，通常不能独立释放
在这个测试数据库中，初始启动数据库，在x$ksmsp视图中存在12623个Chunk：
12:12:54 sys@felix SQL>select count(*) fromx$ksmsp;
COUNT(*)
----------
12623
12:12:56 sys@felix SQL>select count(*) fromdba_objects;
COUNT(*)
----------
75613
此时shared pool中的chunk数量增加
12:13:04 sys@felix SQL>select count(*) fromx$ksmsp;
COUNT(*)
----------
13892
12:13:09 sys@felix SQL>
这就是由于Shared Pool中进行SQL解析，请求空间，进而导致请求free空间分配、分割，从而产生了更多、更细碎的内存Chunk。
由此可以看出，如果数据库系统中存在大量的硬解析，不停请求分配free的Shared Pool内存，除了必需的SharedPool Latch等竞争外，还不可避免地会导致Shared Pool中产生更多的内存碎片（当然，在内存回收时，你可能看到Chunk数量减少的情况）。
继续进行一点深入研究，首先重新启动数据库：
12:13:09 sys@felix SQL>startup force;
ORACLE instance started.
Total System Global Area 417546240 bytes
Fixed Size 2228944 bytes
Variable Size 335547696 bytes
Database Buffers 75497472 bytes
Redo Buffers 4272128 bytes
Database mounted.
Database opened.
12:16:41 sys@felix SQL>
创建一张临时表用以保存之前x$ksmsp的状态：
CREATE GLOBAL TEMPORARY TABLE e$ksmsp ON COMMITPRESERVE ROWS AS
SELECTa.ksmchcom,
SUM(a.CHUNK) CHUNK,
SUM (a.recr) recr,
SUM (a.freeabl) freeabl,
SUM (a.SUM) SUM
FROM (SELECT ksmchcom, COUNT (ksmchcom) CHUNK,
DECODE (ksmchcls, 'recr', SUM (ksmchsiz), NULL)recr,
DECODE (ksmchcls, 'freeabl', SUM (ksmchsiz), NULL)freeabl,
SUM(ksmchsiz) SUM
FROM x$ksmspGROUP BY ksmchcom, ksmchcls) a
where 1 = 0
GROUP BYa.ksmchcom;
保存当前Shared Pool状态：
INSERT INTO E$KSMSP
SELECTa.ksmchcom,
SUM(a.CHUNK) CHUNK,
SUM(a.recr) recr,
SUM(a.freeabl) freeabl,
SUM(a.SUM) SUM
FROM(SELECT ksmchcom,
COUNT(ksmchcom) CHUNK,
DECODE(ksmchcls, 'recr', SUM(ksmchsiz), NULL) recr,
DECODE(ksmchcls, 'freeabl', SUM(ksmchsiz), NULL) freeabl,
SUM(ksmchsiz) SUM
FROM x$ksmsp
GROUPBY ksmchcom, ksmchcls) a
GROUP BYa.ksmchcom /
12:20:31 sys@felix SQL>INSERT INTO E$KSMSP
12:20:50 2 SELECT a.ksmchcom,
12:20:50 3 SUM(a.CHUNK) CHUNK,
12:20:50 4 SUM(a.recr) recr,
12:20:50 5 SUM(a.freeabl)freeabl,
12:20:50 6 SUM(a.SUM) SUM
12:20:50 7 FROM (SELECT ksmchcom,
12:20:50 8 COUNT(ksmchcom) CHUNK,
12:20:50 9 DECODE(ksmchcls, 'recr', SUM(ksmchsiz), NULL) recr,
12:20:50 10 DECODE(ksmchcls, 'freeabl',SUM(ksmchsiz), NULL) freeabl,
12:20:50 11 SUM(ksmchsiz)SUM
12:20:50 12 FROM x$ksmsp
12:20:50 13 GROUP BY ksmchcom,ksmchcls) a
12:20:50 14 GROUP BY a.ksmchcom ;
2788 rows created.
12:20:51 sys@felix SQL>
执行查询：
12:22:30 sys@felix SQL>select count(*) fromdba_objects;
COUNT(*)
----------
75614
13:18:32 sys@felix SQL>
比较查询前后shared pool内存分配的变化：
select a.ksmchcom,
a.chunk,
a.sum,
b.chunk,
b.sum,
(a.chunk - b.chunk) c_diff,
(a.sum - b.sum) s_diff
from(SELECT a.ksmchcom,
SUM(a.CHUNK) CHUNK,
SUM(a.recr) recr,
SUM(a.freeabl) freeabl,
SUM(a.SUM) SUM
FROM (SELECT ksmchcom,
COUNT(ksmchcom) CHUNK,
DECODE(ksmchcls, 'recr',SUM(ksmchsiz), NULL) recr,
DECODE(ksmchcls,'freeabl', SUM(ksmchsiz), NULL) freeabl,
SUM(ksmchsiz) SUM
FROM x$ksmsp
GROUP BY ksmchcom, ksmchcls) a
GROUP BY a.ksmchcom) a,
e$ksmsp b
wherea.ksmchcom = b.ksmchcom
and(a.chunk - b.chunk) <> 0;
KSMCHCOM CHUNK SUM CHUNK SUM C_DIFF S_DIFF
-------------------------------- -------------------- ---------- ---------- ---------- ----------
KGLS^2a03296c 3 12288 1 4096 2 8192
free memory 200 13585552 164 12075904 36 1509648
KGLH0^522f4e73 5 20480 3 12288 2 8192
KGLS^b9dac7f1 14 57344 3 12288 11 45056
KGLH0^c11a66b1 18 73728 2 8192 16 65536
KGLS^3fc2ae3a 12 49152 3 12288 9 36864
KGLH0^61ffddd0 18 73728 2 8192 16 65536
KGLHD 8965 5486488 6456 3979120 2509 1507368
SQLA^522f4e73 10 40960 5 20480 5 20480
KGLS^18717bf4 7 28672 6 24576 1 4096
KGLS^672109bb 3 12288 1 4096 2 8192
KGLH0^4a1d3fe3 18 73728 2 8192 16 65536
reserved stoppe 76 3648 62 2976 14 672
KGLH0^c165fb75 18 73728 2 8192 16 65536
KGLS^a7a0f1b0 14 57344 3 12288 11 45056
KGLS^470434f8 5 20480 1 4096 4 16384
modification 29 178872 20 123360 9 55512
KGLS^b96975f9 8 32768 1 4096 7 28672
KGLS^6e2f6b00 4 16384 3 12288 1 4096
KGLH0^7eef98e0 3 12288 2 8192 1 4096
KGI Session Sta 28 1840 24 1584 4 256
KGLS^e7c28658 3 12288 1 4096 2 8192
KGLH0^3d645f43 18 73728 2 8192 16 65536
KGLH0^1a8436ae 4 16384 3 12288 1 4096
parameter table 308 623840 264 534720 44 89120
KQR SO 1356 899136 773 536304 583 362832
KGLS^1cb5ff2d 10 40960 2 8192 8 32768
KGLS^cfa770fb 5 20480 1 4096 4 16384
KTC latch subh 23 80472 11 46688 12 33784
kpscad: kpscsco 7 576 6 504 1 72
kdlwss 28 11000 24 9408 4 1592
KGLS^518fa5d0 9 36864 1 4096 8 32768
KQR PO 5887 4067840 2616 2037936 3271 2029904
KGLS^d10c66e2 8 32768 2 8192 6 24576
KKSSP 28 15680 24 13440 4 2240
KGLS^6c13497e 6 24576 1 4096 5 20480
KGLNA 8 7048 5 3720 3 3328
KGLH0^7f01546f 18 73728 2 8192 16 65536
KGLDA 3541 850192 2076 498408 1465 351784
KGLS^ea4fb95d 5 20480 1 4096 4 16384
KGLH0^aaab13e6 18 73728 2 8192 16 65536
parameter handl 28 83328 24 71424 4 11904
42 rows selected.
13:18:43 sys@felix SQL>
12:22:30 sys@felix SQL>
简单分析一下以上结果：首先free memory的大小减少了89228（增加到另外5个组件中），这说明SQL解析存储占用了一定的内存空间；而Chunk从164增加为200，这说明内存碎片增加了，碎片增加是共享池性能下降的开始。
1.2.6 Shared Pool的转储与分析
使用如下命令可以对共享池LibraryCache信息进行转储分析：
ALTER SESSION SET EVENTS'immediate trace name LIBRARY_CACHE level LL';
其中LL代表Level级别,对于9.2.0及以后版本,不同Level含义如下：
（1）Level =1，转储Library Cache统计信息；
（2）Level =2，转储Hash Table概要；
（3）Level =4，转储Library Cache对象，只包含基本信息；
（4）Level =8，转储Library Cache对象，包含详细信息（如child references、pin waiters等）；
（5）Level =16，增加heap sizes信息；
（6）Level =32，增加heap信息。
Library Cache由一个Hash表组成，而Hash表是一个由Hash Buckets组成的数组，每个hashBucket都是包含Library Cache Handle的一个双向链表。Library Cache Handle指向Library Cache Object和一个引用列表。Library Cache对象进一步分为依赖表、子表和授权表等。
首先通过以下命令对Library Cache进行转储：
13:18:43 sys@felix SQL>ALTER SESSION SET EVENTS'immediate trace name LIBRARY_CACHE level 4';
Session altered.
13:32:24 sys@felix SQL>
13:39:49 sys@felix SQL>col namespace for a30
13:40:04 sys@felix SQL>select gets,pins,reloads,INVALIDATIONS ,namespace fromv$librarycache;
GETS PINS RELOADS INVALIDATIONS NAMESPACE
---------- ---------- ---------- -------------------------------------------
6802 43040 31 120 SQL AREA
7440 10131 98 0 TABLE/PROCEDURE
1627 2329 0 0 BODY
279 344 0 0 TRIGGER
62 62 0 0 INDEX
233 264 0 0 CLUSTER
88 294 0 0 QUEUE
1 4 0 0 RULESET
17 17 0 0 SUBSCRIPTION
121 216 0 0 EDITION
3 0 0 0 DBLINK
59 0 0 0 OBJECT ID
3530 0 0 0 SCHEMA
1 0 0 0 DBINSTANCE
901 901 0 0 SQL AREA STATS
906 0 0 0 SQL AREA BUILD
16 rows selected.
Oracle 9i中通过新的方式记录Library Cache的使用状况。按不同的Hash Chain Size代表Library Cache中包含不同对象的个数。0表示Free的Bucket，>20表示包含超过20个对象的Bucket的个数。从以上列表中看到,包含一个对象的Buckets有217个，包含0个对象的Buckets有130855个。
重启数据库：
13:40:07 sys@felix SQL>startup force
ORACLE instance started.
Total System Global Area 417546240 bytes
Fixed Size 2228944 bytes
Variable Size 335547696 bytes
Database Buffers 75497472 bytes
Redo Buffers 4272128 bytes
Database mounted.
Database opened.
14:11:24 sys@felixSQL>select * from scott.emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
------------------------------ ------------------ ---------- ------------ -------------------- ----------
7369 SMITH CLERK 7902 17-DEC-80 800 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7566 JONES MANAGER 7839 02-APR-81 2975 20
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7788 SCOTT ANALYST 7566 19-APR-87 3000 20
7839 KING PRESIDENT 17-NOV-81 5000 10
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7876 ADAMS CLERK 7788 23-MAY-87 1100 20
7900 JAMES CLERK 7698 03-DEC-81 950 30
7902 FORD ANALYST 7566 03-DEC-81 3000 20
7934 MILLER CLERK 7782 23-JAN-82 1300 10
14 rows selected.
felix SQL>selectSQL_TEXT,VERSION_COUNT,HASH_VALUE,to_char(HASH_VALUE,'xxxxxxxxxx') HEX,ADDRESS
from v$sqlarea where sql_text like 'select * from%emp%';
select sql_text,
username,
ADDRESS,
HASH_VALUE,
to_char(HASH_VALUE, 'xxxxxxxxxx') HEX_HASH_VALUE,
CHILD_NUMBER,
CHILD_LATCH
from v$sqla, dba_users b
wherea.PARSING_USER_ID = b.user_id
andsql_text like 'select * from %emp%';
这里可以看出V$SQLAREA和V$SQL两个视图的不同之处，V$SQL中为每一条SQL保留一个条目，而V$SQLAREA中根据SQL_TEXT进行GROUP BY，通过version_count计算子指针的个数。
在以上两次查询中，两条SQL 语句因为其代码完全相同，所以其ADDRESS、HASH_VALUE也完全相同。这就意味着，这两条SQL语句在共享池中的存储位置是相同的（尽管其执行计划可能不同），代码得以共享。在SQL解析过程中，Oracle将SQL文本转换为相应的ASCII数值，然后根据数值通过Hash函数计算其HASH_VALUE，再通过HASH_VALUE在Shared Pool中寻找是否存在相同的SQL语句，如果存在则进入下一步骤；如果不存在则尝试获取Shared Pool Latch，请求内存，存储该SQL代码。
注意到以上输出，仅仅是大小写的不同使得原本相同的SQL语句变成了两条“不同的代码”，所以从这里可以看出，SQL的规范编写非常重要。
SQL解析首先要进行的是语法解析，语法无误后进入下一个步骤，进行语义分析，在此步骤中，Oracle需要验证对象是否存在、相关用户是否具有权限、引用的是否是相同的对象。
对于先前的查询，实际上emp表来自不同的用户，那么SQL的执行计划也就不同了（当
然影响SQL执行计划的因素还有很多，包括优化器模式等），通 过 对 象 依 赖 关 系 可 以 看 到 这 个不同：
看一下Library Cache的结构:
Buffer Cache的管理，其中BucketàBHàBuffer的管理方式与以上LibraryCache的管理原理完全类似。
Library Cache Handle可以被看作库缓存对象的概要信息，Handle上存有指针指向LibraryCache Object，Handle中还包含对象名、namespace、时间戳、引用列表、锁定对象及pin对象列表等信息。这里还需要说明的是Handle上的指针指向的是Library Cache Object的Heap 0，库缓存对象可能占用多个内存Heap，Heap 0则记录了控制信息，包括对象类型、对象依赖表、指向其他Heap的指针等。
下图列举了主要Shared Pool对象的具体内存结构组成。
如果以上两个CRSR访问的是同一个对象，那么这两个SQL才会是真的共享。如果SQL虽然是相同的，访问的却是不同用户的数据表，子指针的概念就体现出来了。
接下来的Data Blocks是个重要的部分，每个控制块包含一个heap descriptor，指向相应的heap memory，这个heap memory包含的就是Diana Tree、P-Code、SourceCode、Shared Cursor Context Area等重要数据，也就是通常所说的，解析过的SQL及执行计划树，真正到这里以后SQL才得以共享，也就真正地避免了硬解析。
至于Dictionary Cache信息则可以通过如下命令进行转储：
ALTER SESSION SET EVENTS 'immediate trace namerow_cache level N';
这里的N可以取的值如下：
（1）转储dictionarycache的统计信息；
（2）转储hash表的汇总信息；
（3）转储dictionarycache中的对象的结构信息。
使用Level 1进行转储，转储出来的内容就是V$ROWCACHE中的统计信息
比方说，能否知道是哪个数据库，哪个表，哪个index占用了buffer Pool么？”当时我没有找到这个问题的答案，但是我一直记着这个问题。直到SQL server 2005 版本出现，这个问题迎刃而解。答案就是使用动态视图（DMV）sys.dm_os_buffer_descriptors。这个DMV非常强大。根据SQL Server 联机丛书，这个视图的作用是 “返回有关 SQL Server 缓冲池中当前所有数据页的信息。可以使用该视图的输出，根据数据库、对象或类型来确定缓冲池内数据库页的分布”。具体点说，这个视图能够返回buffer pool里面一个8K 的data page的下列属性：(1)该页属于哪个数据库(2)该页属于数据库哪个文件(3)该页的Page_ID(4)该页的类型。可以根据这个来判断此页时索引页还是数据页(5)该页内有多少行数据(6)该页有多少可用空间。(7)该页从磁盘读取以来是否修改过。有了上面的信息，我们就可以很方便的统计出几种很有用的数据，如下。1. Buffer Pool的内存主要是由那个数据库占了？SELECTcount(*)*8 as cached_pages_kb,CASE database_id WHEN 32767 THEN'ResourceDb' ELSEdb_name(database_id) ENDAS Database_nameFROMsys.dm_os_buffer_descriptorsGROUPBYdb_name(database_id),database_idORDERBY cached_pages_kb DESC;结果如下：从上面的结果可以看到数据库AdventureWorks占用了大概30MB左右的缓冲池空间。注意该DMV 并不返回Buffer Pool里面有关非数据页(如执行计划的缓存等)的信息。也就是说这个DMV并没有返回Buffer Pool里面所有页面的信息。2. 再具体一点，当前数据库的哪个表或者索引占用Pool缓冲空间最多? SELECTcount(*)*8 AS cached_pages_kb ,obj.name ,obj.index_id,b.type_desc,b.nameFROMsys.dm_os_buffer_descriptorsAS bd INNERJOIN ( SELECTobject_name(object_id)AS name ,index_id ,allocation_unit_id,object_id FROMsys.allocation_unitsAS au INNERJOINsys.partitionsAS p ON au.container_id = p.hobt_id AND(au.type= 1 OR au.type= 3) UNIONALL SELECTobject_name(object_id)AS name ,index_id, allocation_unit_id,object_id FROMsys.allocation_unitsAS au INNERJOINsys.partitionsAS p ON au.container_id = p.partition_id AND au.type= 2 )AS obj ON bd.allocation_unit_id = obj.allocation_unit_id LEFTJOINsys.indexes b on b.object_id= obj.object_idAND......余下全文>>
error : ORA 4031
Text : unable to allocate %s bytes of shared memory (%s,%s,%s)
----------------------------------------------------------------------------------------------------------------
Cause : More shared memory is needed than was allocated in the shared pool.
Action : Either use the dbms_shared_pool package to pin large packages, reduce your use of
shared memory, or increase the amount of available shared memory by increasing the value of
the init.ora parameter "shared_pool_size".
检查共享内存
SELECT free_space, avg_free_size, used_space, avg_used_size,
request_failures, last_failure_size
FROM v$shared_pool_reserved;
如果
REQUEST_FAILURES > 0
and
LAST_FAILURE_SIZE > SHARED_POOL_RESERVED_MIN_ALLOC
则增加
SHARED_POOL_RESERVED_MIN_ALL、SHARED_POOL_RESERVED_SIZE
如果
REQUEST_FAILURES > 0 and
LAST_FAILURE_SIZE < SHARED_POOL_RESERVED_MIN_ALLOC
or
REQUEST_FAILURES =0 and
LAST_FAILURE_SIZE < SHARED_POOL_RESERVED_MIN_ALLOC
则增大
SHARED_POOL_RESERVED_MIN_ALL
降低
SHARED_POOL_RESERVED_SIZE
另外动态SQL，非共享SQL和其他过多解析的SQL也会导致04031，这类问题需要调整应用。
在业务高峰期进行编译、DDL也会导致共享内存过量使用。