Top Oracle shared pool scripts

Top Oracle shared pool scripts Oracle Tips by Burleson Consulting

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