ITPub博客

首页 > 应用开发 > IT综合 > SHARED POOL (1)

SHARED POOL (1)

原创 IT综合 作者:zhyuh 时间:2006-02-07 14:37:05 0 删除 编辑
检查当前 shared pool 空间使用情况,包括已用空间,剩余空间,使用率等[@more@]

REM Script to report on shared pool usage
REM
column shared_pool_used format 9,999.99
column shared_pool_size format 9,999.99
column shared_pool_avail format 9,999.99
column shared_pool_pct format 999.99
start title80 'Shared Pool Summary'
spool shared_pool.out
select
sum(a.bytes)/(1024*1024) shared_pool_used,
max(b.value)/(1024*1024) shared_pool_size,
(max(b.value)-sum(a.bytes))/(1024*1024) shared_pool_avail,
(sum(a.bytes)/max(b.value))*100 shared_pool_pct
from v$sgastat a,v$parameter b
where a.name in
( 'table definiti',
'dictionary cache',
'library cache',
'sql area',
'PL/SQL DIANA')
and b.name='shared_pool_size';
spool off
ttitle off


执行结果如下:
Date: 02/04/06 Page: 1
Time: 09:22 PM Shared Pool Summary SYS
SDN09T database

SHARED_POOL_USED SHARED_POOL_SIZE SHARED_POOL_AVAIL SHARED_POOL_PCT
-------------------------------- ------------------------------- --------------------------------- ----------------------------
89.07 96.00 6.93 92.78

一般建议shared pool的使用百分比维持在90%以下。如果该值维持在95%以上,要确定常用的package和procedure都保存在内存里。如果该数据库的绝大多数sql都不具有重用性,则shared pool只要比fixed部分容量再大一些就够了。

空间使用情况也可以根据v$sgastat 中的shared pool 的free memory值计算出,一般认为free memory值比真实的要稍微偏大一点。
the 'free memory' statistic does slightly overstate the free memory. I think it includes some of the padding for alignment purposes as being free. I suppose it is technically free, but it is certainly not usable. --http://www.ixora.com.au

下面sql可以比较两种方法所计算出的内存使用情况的差别
select 'According USED' method,
sum(a.bytes)/(1024*1024) shared_pool_used,
max(b.value)/(1024*1024) shared_pool_size,
(max(b.value)-sum(a.bytes))/(1024*1024) shared_pool_avail,
(1-sum(a.bytes)/max(b.value))*100 free_pct
from v$sgastat a,v$parameter b
where a.name in
('table definiti',
'dictionary cache',
'library cache',
'sql area',
'PL/SQL DIANA')
and b.name='shared_pool_size'
UNION ALL
select 'According FREE' method,
(b.value-a.bytes)/(1024*1024) shared_pool_used,
b.value/(1024*1024) shared_pool_size,
a.bytes/(1024*1024) shared_pool_avail,
(a.bytes/b.value)*100 free_pct
from v$sgastat a,v$parameter b
where a.name='free memory'
and a.pool='shared pool'
and b.name='shared_pool_size';

执行结果如下:
METHOD SHARED_POOL_USED SHARED_POOL_SIZE SHARED_POOL_AVAIL FREE_PCT
-------------- ---------------- ---------------- ----------------- ---------
According USED 89.1485748 96 6.85142517 7.1369012
According FREE 88.9823685 96 7.01763153 7.3100328

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/207/viewspace-814058/,如需转载,请注明出处,否则将追究法律责任。

上一篇: 深入了解SCN
下一篇: SHARED POOL (2)
请登录后发表评论 登录
全部评论
  • 博文量
    233
  • 访问量
    2012659