搏击

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

2019-09-10 18:24:44来源:励志吧0次阅读

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

其实我们可以从数据库内部监控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中的内存结构.

查看本文来源

宝宝大便
小孩流鼻血怎么止血
婴儿有眼屎是怎么回事
一岁宝宝消化不良怎么办
分享到: