服务器 频道

关于shared pool的深入探讨之二

  【IT168 服务器学院】 我们继续把前面的问题展开一下.

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

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

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

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

    首先记录一下测试环境:

    Code:        [Copy to clipboard]
    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 - Production

    我们看一下x$ksmsp的结构:

    Code:        [Copy to clipboard]
    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)

    我们关注以下几个字段:

    KSMCHCOM是注释字段,每个内存块被分配以后,注释会添加在该字段中.

    x$ksmsp.ksmchsiz代表块大小

    x$ksmsp.ksmchcls列代表类型,主要有四类,说明如下:

    free

    Free chunks--不包含任何对象的chunk,可以不受限制的被分配.

    recr

    Recreatable chunks--包含可以被临时移出内存的对象,在需要的时候,这个对象可以被重新创建.例如,许多存储共享sql代码的内存都是可以重建的.

    freeabl

    Freeable chunks--包含session周期或调用的对象,随后可以被释放.这部分内存有时候可以全部或部分提前释放.但是注意,由于某些对象是中间过程产生的,这些对象不能临时被移出内存(因为不可重建).

    perm
   
    Permanent memory chunks--包含永久对象.通常不能独立释放.

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

    不过注意:Oracle的某些版本(如:10.1.0.2)在某些平台上(如:HP-UX PA-RISC 64-bit)查询该视图可能导致过度的CPU耗用,这是由于bug引起的.

    我们看一下测试:

    Code:        [Copy to clipboard]
    初始启动数据库,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

    这就是由于shared pool中进行sql解析,请求空间,进而导致请求free空间,分配、分割从而产生了更多,更细碎的内存chunk

    由此我们可以看出,如果数据库系统中存在大量的硬解析,不停请求分配free的shred pool内存除了必须的shared pool latch等竞争外,还不可避免的会导致shared pool中产生更多的内存碎片(当然,在内存回收时,你可能看到chunk数量减少的情况)

    我们看以下测试:

    Code:        [Copy to clipboard]

    首先重新启动数据库:

    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.

    我们简单分析一下以上结果:

    首先free memory的大小减少了89228,这说明sql解析存储占用了一定的内存空间而chunk从90增加为93,这说明内存碎片增加了.

    在下面的部分中,我会着手介绍一下KGL handles,   KGLS heap这两个非常重要的shared pool中的内存结构.

0
相关文章