Diving Into the Shared Pool - An In Depth Look at Tuning the Shared Pool (Part 1) Oracle Tips by Mike Ault

Monitoring and Tuning the Shared Pool

REM
REM Report on SQL Area Reuse by user
REM
column garbage           format 9,999,999,999 heading 'Non-Shared
SQL'
column good             format 9,999,999,999 heading 'Shared SQL'
column good_percent       format 999.99         heading 'Percent
Shared'
set feedback off
break on report
compute sum of garbage on report
compute sum of good on report
compute avg of good_percent on report
@title80 'Shared Pool Utilization'
spool rep_out\&db\sql_garbage
select
      a.users,
      a.garbage,
      b.good,
      (b.good/(b.good+a.garbage))*100 good_percent
from
      sql_garbage a, sql_garbage b
SEE CODE DEPOT FOR FULL SCRIPTS
/
spool off
set feedback off
clear columns
clear breaks
clear computes

Figure 10: Example Report Script For SQL Reuse Statistics

SEE CODE DEPOT FOR FULL SCRIPTS

Expert Remote DBA BC is America's oldest and largest Remote DBA Oracle support provider.  Get real Remote DBA experts, call BC Remote DBA today.