Top Oracle shared pool scripts Oracle Tips by Burleson Consulting

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

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

Oracle shared pool scripts

Here are some common shared pool scripts for monitoring the shared pool.  For a complete set of shared pool scripts, see my book "Oracle Tuning: The Definitive Reference", with 950 pages of tuning tips and scripts. 

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

We also need scripts to monitor for non-reentrant SQL (SQL without bind variables, which can be corrected by setting cursor_sharing=force). This shared pool script will identify 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 pool script will display shared pool data from STATSPACK:

set lines 80; 
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 
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 pool script will use the Oracle 10gautomatoc workload repository for a time-series look at the shared pool:

column enqueue_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
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'
;
 

For a complete set of shared pool scripts, see the book "Oracle Tuning: The Definitive Reference", with 950 pages of tuning tips and scripts.  You can buy it direct from the publisher for 30%-off and get instant access to the code depot of Oracle tuning scripts.