诊断shared_pool 利用

如何分析当前数据库shared pool 利用率

1
SQL> select (u.used/a.bytes)*100 shared_pool_usage
2 from (select sum(bytes) used
3 from v$sgastat
4 where pool = 'shared pool'
5 and name <> 'free memory') u,
6 (select sum(bytes) bytes from v$sgastat
7 where pool='shared pool') a
8 /

SHARED_POOL_USAGE
-----------------
45.8586752414703

说明：
共享池利用率 40%-60% 属于安全
60%-80% 适当
>80% 分配不足

2
SQL> select (f.bytes/u.bytes)*100 shared_pool_usage_free_ratio
2 from (select sum(bytes) bytes from v$sgastat
3 where pool='shared pool' and name <> 'free memory') u,
4 (select sum(bytes) bytes from v$sgastat
5 where pool='shared pool' and name = 'free memory') f
6 /

SHARED_POOL_USAGE_FREE_RATIO
----------------------------
118.938190200381

说明：
空闲内存与共享池当前使用内存的比率，最佳比率 20%
<20% 适当
20%-100% 关注过多
>100% 过度分配

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

转载于:http://blog.itpub.net/593324/viewspace-376185/