Monitoring Oracle SGA & PGA Memory Changes Oracle Database Tips by Donald Burleson

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. 

You can query the AWR and see the changes to the pool sizes in you are using automatic memory management (AMM) (e.g. the memory_target parameter).  This script will display the automatic pool size changes made by AMM.  Note to too-frequent AMM resize operations can hurt performance. Always make sure that you have enough RAM so that the pool sizes are not starved for RAM.

-- script by Denis L.
col snap_time       format a20 heading "Snap Time"
col instance_number format 990 heading "SID"
col snap_id heading "Snap ID"
col name            format a30 heading "Name"
col old_value       format a15 heading "Old Value"
col new_value       format a15 heading "New Value"
col diff            format a15 heading "Numeric|Difference"

col instance_name new_value V_INSTANCE noprint
select instance_name from v$instance;
accept V_NBR_DAYS prompt "Please enter the number of days to report upon: "

spool awr_parm_changes_&&V_INSTANCE
 select to_char(s.begin_interval_time, 'DD-MON-YYYY HH24:MI:SS') snap_time,
       p.instance_number,
       p.snap_id,
       p.name,
       p.old_value,
       p.new_value,
       decode(trim(translate(p.new_value, '0123456789', '          ')),
              '',
              trim(to_char(to_number(p.new_value) - to_number(p.old_value),
                           '999999999999990')),
              '') diff
  from (select dbid,
               instance_number,
               snap_id,
               parameter_name name,
               lag(trim(lower(value))) over(partition by dbid, instance_number, parameter_name order by snap_id) old_value,
               trim(lower(value)) new_value,
               decode(nvl(lag(trim(lower(value)))
                          over(partition by dbid,
                               instance_number,
                               parameter_name order by snap_id),
                          trim(lower(value))),
                      trim(lower(value)),
                      '~NO~CHANGE~',
                      trim(lower(value))) diff
          from dba_hist_parameter) p,
       dba_hist_snapshot s
 where s.begin_interval_time between trunc(sysdate - 31) and
       sysdate
   and p.dbid = s.dbid
   and p.instance_number = s.instance_number
   and p.snap_id = s.snap_id
   and p.diff <> '~NO~CHANGE~'
 order by snap_time, instance_number;
 spool off          

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:

• Oracle shared_pool_size tuning

• Tips for Using Oracle Stored Procedures

• Oracle10g new automatic tuning Features

• Dynamic adjustment of the Oracle9i SGA

• Faster Performance Using Oracle8 Stored Procedures

• Oracle Server Bottlenecks

• Oracle Automatic Shared Memory Management ASMM

• Oracle shared_pool_size Tuning: Boost performance 

 

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:

 

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: