bufferpool和sharedpool详解（之四，重要视图、以及转储）

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的结构:

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) 块，

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 来自 x$ksmsp

12:20:50 13 GROUP BY ksmchcom,ksmchcls) a

12:20:50 14 由 a.ksmchcom 分组；

已创建 2788 行。

12:20:51 sys@felix SQL> 

执行查询：

12:22:30 sys@felix SQL>select count(*) fromdba_objects;

COUNT(*)

----------

75614

13:18:32 sys@felix SQL>

比较查询对比共享池内存分配的变化：

选择.ksmchcom，

a.chunk，

a.sum，

b.chunk，

b.sum，

(a.chunk - b.chunk) c_diff,

(a.sum - b.sum) s_diff

来自（选择 a.ksmchcom，

SUM(a.CHUNK) CHUNK,

SUM(a.recr) recr,

SUM(a.freeabl) freeabl,

SUM(a.SUM) SUM

来自（选择 ksmchcom，

COUNT(ksmchcom) 块，

DECODE(ksmchcls, 'recr',SUM(ksmchsiz), NULL) recr,

DECODE(ksmchcls,'freeabl', SUM(ksmchsiz), NULL) freeabl,

SUM(ksmchsiz) SUM

来自 x$ksmsp

按 ksmchcom、ksmchcls 分组）a

按 a.ksmchcom 分组) a,

e$ksmsp b

wherea.ksmchcom = b.ksmchcom

和(a.chunk - b.chunk) 0;

KSMCHCOM CHUNK SUM CHUNK SUM C_DIFF S_DIFF

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

KGLS^2a03296c 3 12288 1 4096 2 8192

可用内存 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

保留站 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

修改 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

凯基证券 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

参数表 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 闩锁 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

参数句柄 28 83328 24 71424 4 11904

已选择 42 行。

13:18:43 sys@felix SQL>

12:22:30 sys@felix SQL>

简单分析一下以上结果：首先free内存的大小减少了89228（增加到另外5个组件中），这说明SQL存储解析占用了一定的内存空间；而Chunk从164增加为200，这说明内存碎片增加了，碎片增加是共享池性能下降的开始。

1.2.6 共享池的转储与分析

使用如下命令可以对共享池LibraryCache信息进行转储分析：

ALTER SESSION SET EVENTS'直接跟踪名称 LIBRARY_CACHE 级别 LL';

其中LL代表Level级别，对于9.2.0及以后版本，不同级别含义如下：

（1）Level =1，转储库缓存统计信息；

（2）Level =2，转储哈希表概要；

（3）Level =4，转储Library Cache对象，只包含基本信息；

（4）Level =8，转储Library Cache对象，包含详细信息（如子引用、pin waiters等）；

（5）Level =16，增加堆大小信息；

（6）Level =32，增加堆信息。

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'立即跟踪名称 LIBRARY_CACHE 级别 4';

会话已更改。

13:32:24 sys@felix SQL> 

13:39:49 sys@felix SQL>a30 的 col 命名空间

13:40:04 sys@felix SQL>选择获取、引脚、重新加载、无效、来自v$librarycache的命名空间； 

获取引脚重新加载无效命名空间

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

6802 43040 31 120 SQL 区域

7440 10131 98 0 表/程序

1627 2329 0 0 身体

279 344 0 0 触发

62 62 0 0 索引

233 264 0 0 集群

88 294 0 0 队列

1 4 0 0 规则

17 17 0 0 订阅

121 216 0 0 版

3 0 0 0 DBLINK

59 0 0 0 对象 ID

3530 0 0 0 架构

1 0 0 0 DBINSTANCE

901 901 0 0 SQL 区域统计

906 0 0 0 SQL 区域构建

已选择 16 行。 

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>启动力

ORACLE 实例已启动。 

系统全局区域总计 417546240 字节

固定大小 2228944 字节

变量大小 335547696 字节

数据库缓冲区 75497472 字节

重做缓冲区 4272128 字节

数据库已安装。

数据库已打开。 

14:11:24 sys@felixSQL>从 scott.emp 中选择 *； 

EMPNO ENAME 工作经理 HIREDATE SAL COMM DEPTNO

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

7369 史密斯职员 7902 17-DEC-80 800 20

7499 艾伦推销员 7698 20-FEB-81 1600 300 30

7521 病房推销员 7698 22-FEB-81 1250 500 30

7566 琼斯经理 7839 02-APR-81 2975 20

7654 马丁推销员 7698 28-SEP-81 1250 1400 30

7698 布莱克 经理 7839 01-MAY-81 2850 30

7782 克拉克经理 7839 09-JUN-81 2450 10

7788 斯科特 分析师 7566 19-APR-87 3000 20

7839 国王总统 17-NOV-81 5000 10

7844 特纳推销员 7698 08-SEP-81 1500 0 30

7876 亚当斯职员 7788 23-MAY-87 1100 20

7900 詹姆斯·克拉克 7698 03-DEC-81 950 30

7902 福特分析师 7566 03-DEC-81 3000 20

7934 米勒职员 7782 23-JAN-82 1300 10 

已选择 14 行。 

felix SQL>selectSQL_TEXT,VERSION_COUNT,HASH_VALUE,to_char(HASH_VALUE,'xxxxxxxxxx') HEX,ADDRESS

来自 v$sqlarea where sql_text like 'select * from%emp%';

选择sql_text，

用户名，

地址，

HASH_VALUE，

to_char(HASH_VALUE, 'xxxxxxxxxx') HEX_HASH_VALUE,

CHILD_NUMBER，

CHILD_LATCH

来自 v$sqla, dba_users b

其中.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中的统计信息