« 《循序渐进Oracle》勘误表 | Blog首页 | Oracle 11g新特性:Rman备份跳过自由区间 »
关于Shared Pool探索的补充之一
作者:eygle | 【转载请注出处】|【云和恩墨 领先的zData数据库一体机 | zCloud PaaS云管平台 | SQM SQL审核平台 | ZDBM 数据库备份一体机】
链接:https://www.eygle.com/archives/2007/09/shared_pool_reserved_ksmspr.html
在以前的一篇文章中,我介绍过x$ksmsp的一些知识。链接:https://www.eygle.com/archives/2007/09/shared_pool_reserved_ksmspr.html
在x$ksmsp.ksmchcls的分类中,还有两外几个分类,这几个分类是R-free/R-freea/R-perm:
Connected to:
Oracle9i Enterprise Edition Release 9.2.0.4.0 - 64bit Production
With the Partitioning option
JServer Release 9.2.0.4.0 - Production
SQL> select KSMCHCLS,sum(KSMCHSIZ) from x$ksmsp
2 group by ksmchcls;
KSMCHCLS SUM(KSMCHSIZ)
-------- -------------
R-free 24836016
R-freea 1946032
R-perm 1904160
free 32459264
freeabl 294999744
perm 70090848
recr 144187104
7 rows selected.
这里的R指的是reserved,也就是说这些Heap的信息是和shared_pool_reserved_size相关的。
至于free/freeable/perm就无需解释了。
shared_pool_reserved_size的另外一个视图展现是V$SHARED_POOL_RESERVED.
我们可以从这个视图中获得关于shared pool reserverd的信息:
SQL> select * from V$SHARED_POOL_RESERVED;
FREE_SPACE AVG_FREE_SIZE FREE_COUNT MAX_FREE_SIZE USED_SPACE AVG_USED_SIZE
---------- ------------- ---------- ------------- ---------- -------------
USED_COUNT MAX_USED_SIZE REQUESTS REQUEST_MISSES LAST_MISS_SIZE MAX_MISS_SIZE
---------- ------------- ---------- -------------- -------------- -------------
REQUEST_FAILURES LAST_FAILURE_SIZE ABORTED_REQUEST_THRESHOLD ABORTED_REQUESTS
---------------- ----------------- ------------------------- ----------------
LAST_ABORTED_SIZE
-----------------
24998976 99597.5139 109 843632 3684512 14679.3307
142 347560 2817185 0 0 0
0 0 2147483647 0
0
关于这些信息的另外一个来源是X$KSMSPR内部表:
SQL> select ksmchcom,ksmchcls,sum(ksmchsiz)
2 from x$ksmspr group by ksmchcom,ksmchcls;
KSMCHCOM KSMCHCLS SUM(KSMCHSIZ)
---------------- -------- -------------
ksfqpar R-freea 328616
free memory R-free 25216256
PL/SQL MPCODE R-freea 81128
PL/SQL SOURCE R-freea 4640
obj htab chunk R-freea 82176
character set o R-freea 61592
permanent memor R-perm 1904160
reserved stoppe R-freea 2720
session param v R-freea 1004920
9 rows selected.
这部分信息和x$ksmsp视图记录的Reserved信息是完全一致的:
SQL> select KSMCHCOM,KSMCHCLS,sum(KSMCHSIZ)
2 from x$ksmsp where KSMCHCLS like 'R%' group by ksmchcom,ksmchcls;
KSMCHCOM KSMCHCLS SUM(KSMCHSIZ)
---------------- -------- -------------
ksfqpar R-freea 328616
free memory R-free 24591576
PL/SQL MPCODE R-freea 81128
PL/SQL SOURCE R-freea 4640
obj htab chunk R-freea 82176
character set o R-freea 61592
permanent memor R-perm 1904160
reserved stoppe R-freea 2720
session param v R-freea 1629600
9 rows selected.
-The End-
历史上的今天...
>> 2012-09-07文章:
>> 2010-09-07文章:
>> 2009-09-07文章:
>> 2006-09-07文章:
>> 2005-09-07文章:
By eygle on 2007-09-07 10:58 | Comments (0) | Internal | 1604 |