 |
|
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
The first report we will examine shows how
individual users are utilizing the shared pool. Before we can run
the report a summary view of the V$SQLAREA view must be created, I
unimaginatively call this view the SQL_SUMMARY view. The code for
the SQL_SUMMARY view is shown in Figure 4.
rem FUNCTION: Creates summary of v_$sqlarea and
Remote DBA_users for use in
rem sqlmem.sql and sqlsummary.sql
reports
rem
rem
create or replace view sql_summary as
select
username, sharable_mem, persistent_mem, runtime_mem
from
sys.v_$sqlarea a, Remote DBA_users b
SEE CODE DEPOT FOR FULL SCRIPTS
Figure 4: Example SQL Script to Create A View
to Monitor Pool Usage By User
Once the SQL_SUMMARY view is created the script
in Figure 5 is run to generate a summary report of SQL areas used by
user. This shows the distribution of SQL areas and may show you that
some users are hogging a disproportionate amount of the shared pool
area. Usually, a user that is hogging a large volume of the shared
pool is not using good SQL coding techniques which is generating a
large number of non-reusable SQL areas.
 |
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 &
scripts.
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. |
 |
|