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 FUNCTION: Generate a summary of SQL Area Memory Usage
rem FUNCTION: uses the sqlsummary view.
rem showing user SQL memory usage
column areas heading Used|Areas
column sharable format 999,999,999 heading Shared|Bytes
column persistent format 999,999,999 heading
column runtime format 999,999,999 heading Runtime|Bytes
column username format a15 heading "User"
column mem_sum format 999,999,999 heading Mem|Sum
start title80 "Users SQL Area Memory Use"
set pages 59 lines 80
break on report
compute sum of sharable on report
compute sum of persistent on report
compute sum of runtime on report
compute sum of mem_sum on report
sum( persistent_mem) Persistent,
sum( runtime_mem) Runtime ,
group by username
order by 2;
pause Press enter to continue
set pages 22 lines 80
Figure 5: Example SQL Script To Report On SQL
Area Usage By User
Example output from the script in Figure 5 is
shown in Figure 6. In the example report no one user is really
hogging the SQL area. If you have a particular user that is hogging
SQL areas, the script in Figure 6 will show you what SQL areas they
have and what is in them. This report on the actual SQL area
contents can then be used to help teach the user how to better
construct reusable SQL statements.
||If you like
Oracle tuning, you may enjoy the book
Oracle Tuning: The Definitive Reference
, with over 900 pages of BC's favorite tuning tips &
You can buy it directly from the
publisher and save 30%, and get instant access to the code
depot of Oracle tuning 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.