对DBA来说shared pool的tuning有可能是经常的，我们分配多大的shared pool是合适的，这时候我们就会想到，我现在的shared pool　还有多少是free的呢。是否需要加大shared pool size或做其它方面的tuning.下面的SQL将列出当前shared pool的使用情况：

Select s.pool,
s.name,
s.bytes "Free Bytes",
Round((s.bytes / p.value) * 100, 2) "Perc Free",
p.value / (1024 * 1024) "SP Size MB"
from sys.v_$parameter p, sys.v_$sgastat s
where s.name = 'free memory'
and s.POOL = 'shared pool'
and p.name = 'shared_pool_size';

输出结果：

SQL> Select s.pool,
2 s.name,
3 s.bytes "Free Bytes",
4 Round((s.bytes / p.value) * 100, 2) "Perc Free",
5 p.value / (1024 * 1024) "SP Size MB"
6 from sys.v_$parameter p, sys.v_$sgastat s
7 where s.name = 'free memory'
8 and s.POOL = 'shared pool'
9 and p.name = 'shared_pool_size';

POOL NAME Free Bytes Perc Free SP Size MB
----------- -------------------------- ---------- ---------- ----------
shared pool free memory 21644336 43 48

SQL>