 |
|
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 WISE
tool is also a great alternative to cut-and-paste spreadsheet graphics and
WISE shows you instant graphs of changes in memory usage:
