 |
|
Monitoring Oracle SGA & PGA Memory
Changes
Oracle Tips by Burleson Consulting |
The scripts referenced in this tech note are available for download from my book "Oracle
Tuning: The Definitive Reference".
When you make global changes to Oracle you often
have to aim at a one-size-fits-all setting, geared to be optimal to the majority
of your users. These global parameters include the parameters that govern
the RAM regions within Oracle:
Monitoring shared_pool_size
The shared_pool_size
parameter governs the RAM size of the shared pool, including the library cache,
used to hold SQL executable code and starting in Oracle9i shared_pool_size
can be modified with "alter system" commands.
In general, if the library
cache miss ratio is greater than one, the DBA should consider adding to the
shared_pool_size. You can pin package objects into the shared_pool_size
region with the dbms_shared_pool.keep procedure.
connect internal;
@/usr/oracle/rdbms/admin/dbmspool.sql
execute dbms_shared_pool.keep('sys.standard');
My notes on shared_pool_size
include:
Monitoring db_cache_size
The db_cache_size initialization parameter
(formerly db_block_buffers) governs the size of the RAM data buffer cache
region. Oracle has the v$db_cache_advice utility to determine the marginal
benefit (in terms of reducing disk reads) by adding more data cache.
My notes on monitoring data buffer caches
includes:
Monitoring pga_aggregate_target
When you make changes to pga_aggregate_target you need to track changes in
disk sorts and the number of hash joins. Tracking sorts is easy with an
AWR report for metric (sorts (disk)), but tracking hash joins is trickier.
This script interrogates dba_hist_sql_plan and displays the number if hash joins
performed between the snapshot periods. When you increase
pga_aggregate_target you need to know if the additional RAM was enough to
trigger changes from NESTED LOOP to HASH joins or reduce disk sorts. If
not, the RAM is being wasted and it can be re-allocated to an SGA component.
awr_hash_join_alert.sql
col c1 heading Date format a20
col c2 heading Hash|Join|Count format 99,999,999
col c3 heading Rows|Processed format 99,999,999
col c4 heading Disk|Reads format 99,999,999
col c5 heading CPU|Time format 99,999,999
accept hash_thr char prompt Enter Hash Join Threshold:
ttitle Hash Join Threshold|&hash_thr
select
to_char(sn.begin_interval_time,'yy-mm-dd hh24') c1,
count(*) c2,
sum(st.rows_processed_delta) c3,
sum(st.disk_reads_delta) c4,
sum(st.cpu_time_delta)
c5
from
dba_hist_snapshot sn,
dba_hist_sql_plan p,
dba_hist_sqlstat st
where
st.sql_id = p.sql_id
and
sn.snap_id = st.snap_id
and
sn.dbid = st.dbid
and
p.operation = 'HASH JOIN'
having
count(*) > &hash_thr
group by
begin_interval_time;
For tracking nested loop joins, this script may
help:
awr_nested_join_alert.sql
col c1 heading Date format a20
col c2 heading Nested|Loops|Count format 99,999,999
col c3 heading Rows|Processed format 99,999,999
col c4 heading Disk|Reads format 99,999,999
col c5 heading CPU|Time format 99,999,999
accept nested_thr char prompt Enter Nested Join Threshold:
ttitle Nested Join Threshold|&nested_thr
select
to_char(sn.begin_interval_time,'yy-mm-dd hh24') c1,
count(*) c2,
sum(st.rows_processed_delta) c3,
sum(st.disk_reads_delta) c4,
sum(st.cpu_time_delta)
c5
from
dba_hist_snapshot sn,
dba_hist_sql_plan p,
dba_hist_sqlstat st
where
st.sql_id = p.sql_id
and
sn.snap_id = st.snap_id
and
sn.dbid = st.dbid
and
p.operation = NESTED LOOPS
having
count(*) > &hash_thr
group by
begin_interval_time;
SEE CODE DEPOT FOR FULL SCRIPTS
2007
PGA Update: Oracle technology is constantly changing, so don't
miss my new notes on updates to Oracle PGA
behavior. Also see these important notes on over-riding the
Oracle PGA defaults.
My notes on monitoring pga_aggregate_target
includes:
Monitoring SGA and PGA RAM regions
The Ion
tool is also a great alternative to cut-and-paste spreadsheet graphics and
Ion shows you instant graphs of changes in memory usage:
