关于shared pool的深入探讨(二)

我们继续把前面的问题展开一下.

其实我们可以从数据库内部监控shared pool的空间碎片情况.

这涉及到一个内部视图x$ksmsp

X$KSMSP的名称含义为: [K]ernal [S]torage [M]emory Management [S]GA Hea[P]

其中每一行都代表着shared pool中的一个chunk

首先记录一下测试环境：

PHP code:

---

SQL> select * from v$version;

BANNER

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

Oracle9i Enterprise Edition Release 9.2.0.3.0 - Production

PL/SQL Release 9.2.0.3.0 - Production

CORE    9.2.0.3.0       Production

TNS for Linux: Version 9.2.0.3.0 - Production

NLSRTL Version 9.2.0.3.0 - 

SQL> desc x$ksmsp

 Name                                      Null?    Type

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

 ADDR                                               RAW(4)

 INDX                                               NUMBER

 INST_ID                                            NUMBER

 KSMCHIDX                                           NUMBER

 KSMCHDUR                                           NUMBER

 KSMCHCOM                                           VARCHAR2(16)

 KSMCHPTR                                           RAW(4)

 KSMCHSIZ                                           NUMBER

 KSMCHCLS                                           VARCHAR2(8)

 KSMCHTYP                                           NUMBER

 KSMCHPAR                                           RAW(4)

---

被重新创建.例如,许多存储共享sql代码的内存都是可以重建的.

可以全部或部分提前释放.但是注意，由于某些对象是中间过程产生的，这些对象不能

临时被移出内存(因为不可重建).

我们可以通过查询x$ksmsp视图来考察shared pool中存在的内存片的数量

不过注意:Oracle的某些版本(如:10.1.0.2)在某些平台上(如:HP-UX PA-RISC 64-bit)查

询该视图可能导致过度的CPU耗用,这是由于bug引起的.

我们看一下测试:

PHP code:

---

初始启动数据库,x$ksmsp中存在2259个chunk

SQL> select count(*) from x$ksmsp;

  COUNT(*)

----------

      2259

执行查询:

SQL> select count(*) from dba_objects;

  COUNT(*)

----------

     10491

此时shared pool中的chunk数量增加

SQL> select count(*) from x$ksmsp;

  COUNT(*)

----------

      2358

.

---

PHP code:

---

首先重新启动数据库:

SQL> startup force;

ORACLE instance started.

Total System Global Area   47256168 bytes

Fixed Size                   451176 bytes

Variable Size              29360128 bytes

Database Buffers           16777216 bytes

Redo Buffers                 667648 bytes

Database mounted.

Database opened.

创建一张临时表用以保存之前x$ksmsp的状态:

SQL> CREATE GLOBAL TEMPORARY TABLE e$ksmsp ON COMMIT PRESERVE ROWS AS

  2  SELECT      a.ksmchcom,

  3           SUM (a.CHUNK) CHUNK,

  4           SUM (a.recr) recr,

  5           SUM (a.freeabl) freeabl,

  6           SUM (a.SUM) SUM

  7      FROM (SELECT   ksmchcom, COUNT (ksmchcom) CHUNK,

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

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

 10                     SUM (ksmchsiz) SUM

 11                FROM x$ksmsp GROUP BY ksmchcom, ksmchcls) a

 12  where 1 = 0

 13  GROUP BY a.ksmchcom;

Table created.

保存当前shared pool状态:

SQL> INSERT INTO E$KSMSP

  2  SELECT      a.ksmchcom,

  3           SUM (a.CHUNK) CHUNK,

  4           SUM (a.recr) recr,

  5           SUM (a.freeabl) freeabl,

  6           SUM (a.SUM) SUM

  7      FROM (SELECT   ksmchcom, COUNT (ksmchcom) CHUNK,

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

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

 10                     SUM (ksmchsiz) SUM

 11                FROM x$ksmsp

 12            GROUP BY ksmchcom, ksmchcls) a

 13  GROUP BY a.ksmchcom

 14  /

41 rows created.

执行查询:

SQL> select count(*) from dba_objects;

  COUNT(*)

----------

     10492

比较前后shared pool内存分配的变化:

SQL> select a.ksmchcom,a.chunk,a.sum,b.chunk,b.sum,(a.chunk - b.chunk) c_diff,(a.sum -b.sum) s_diff

  2  from

  3  (SELECT   a.ksmchcom,

  4           SUM (a.CHUNK) CHUNK,

  5           SUM (a.recr) recr,

  6           SUM (a.freeabl) freeabl,

  7           SUM (a.SUM) SUM

  8      FROM (SELECT   ksmchcom, COUNT (ksmchcom) CHUNK,

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

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

 11                     SUM (ksmchsiz) SUM

 12                FROM x$ksmsp

 13            GROUP BY ksmchcom, ksmchcls) a

 14  GROUP BY a.ksmchcom) a,e$ksmsp b

 15  where a.ksmchcom = b.ksmchcom and (a.chunk - b.chunk) <>0

 16  /

KSMCHCOM              CHUNK        SUM      CHUNK        SUM     C_DIFF     S_DIFF

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

KGL handles             313     102080        302      98416         11       3664

KGLS heap               274     365752        270     360424          4       5328

KQR PO                  389     198548        377     192580         12       5968

free memory              93    2292076         90    2381304          3     -89228

library cache          1005     398284        965     381416         40      16868

sql area                287     547452        269     490052         18      57400

6 rows selected.

---