buffer pool和shared pool详解（之四，重要视图、以及转储）

最新推荐文章于 2024-03-15 08:00:00 发布

原创最新推荐文章于 2024-03-15 08:00:00 发布 · 2.4k 阅读

1 ·

CC 4.0 BY-SA版权

文章标签：

#latch #管理 #oracle #数据库 #shared pool

Oracle 系列专栏收录该内容

123 篇文章

订阅专栏

本文深入探讨Oracle数据库Shared Pool的工作原理，包括X$KSMSP视图的使用，Shared Pool内存分配机制，以及如何通过Library Cache和Row Cache的转储分析来诊断性能问题。

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