Top Oracle shared pool scripts

The Oracle shared pool has very few tuning knobs other than adjusting theshared_pool_size, setting cursor_sharing andtuning the SQL that reside within the library cache of the shared pool.

A shortage ofshared pool RAM may result in high library cache reloads, high row cachereloads, and shared pool latch contention. You may also see the error:"ORA-04031: Out of shared pool memory".

Also see Oracle shared poollatch internals.

Oracle shared poolscripts

Here are somecommon shared pool scripts for monitoring the shared pool.  For a completeset of shared pool scripts, see my book "Oracle Tuning: TheDefinitive Reference", with 950 pages of tuning tips andscripts. 

select * 
from(
select 
name, bytes/(1024*1024) MB 
from 
v$sgastat 
where 
pool ='shared pool' 
order by 
bytes desc
) 
where rownum < 20;

We also needscripts to monitor for non-reentrant SQL (SQL without bind variables, which canbe corrected by setting cursor_sharing=force). This shared pool script willidentify SQL where "executions=1", indicating nonreentrant SQL:

select 
count(1) num_sql, 
sum(decode(executions, 1, 1, 0)) one_use_sql, 
sum(sharable_mem)/1024/1024     meg_used, 
sum(decode(
executions,   1, 
sharable_mem, 0)
)/1024/1024                     mb_per
from 
v$sqlarea
where 
sharable_mem > 0;

This shared poolscript will display shared pool data from STATSPACK:

set lines80; 
set pages 999; 

column mydate heading 'Yr.  Mo Dy  Hr.' format a16 
column c1 heading "execs"    format 9,999,999 
column c2 heading "Cache Misses|While Executing"   format 9,999,999 
column c3 heading "Library Cache|Miss Ratio"    format 999.99999 

break on mydate skip 2; 

select  
to_char(snap_time,'yyyy-mm-dd HH24')  mydate, 
sum(new.pins-old.pins)              c1, 
sum(new.reloads-old.reloads)         c2, 
sum(new.reloads-old.reloads)/ 
sum(new.pins-old.pins)               library_cache_miss_ratio 
from  
stats$librarycache old, 
stats$librarycache new, 
stats$snapshot     sn 
see code depot for full scripts 
where 
new.snap_id = sn.snap_id 
and 
old.snap_id = new.snap_id-1 
and 
old.namespace = new.namespace 
group by 
to_char(snap_time,'yyyy-mm-dd HH24') 
; 

This shared poolscript will use the automatic workload repository (AWR) for a time-series lookat the shared pool:

columnenqueue_deadlocks format 999,999,999

select
to_char(sn.end_interval_time,'dd Mon HH24:mi:ss') mydate,
a.value enqueue_deadlocks
from
dba_hist_sysstat a,
dba_hist_snapshot sn
see code depot for full scripts
where
sn.snap_id = (select max(snap_id) from dba_hist_snapshot)
and a.snap_id = sn.snap_id
and a.stat_name = 'enqueue deadlocks'
;