SGA Components RAC (SGAComponentsRAC.sql)

What This Script Does

This comprehensive script analyzes SGA components across all RAC instances, providing detailed information about dynamic SGA memory management, resize operations, and minimum/maximum component sizes. Essential for understanding automatic memory management behavior and planning SGA tuning.

The Script

rem  Investigate SGA components
rem     These queries do not impact performance on the
rem     database and you can run them
rem     as often as you like

set lines 132
set pages 999
set termout off
set trimout on
set trimspool on

spool sga_components.out
col inst_id format 999 head "Instance #"
col component for a25
col status format a10 head "Status"
col initial_size for 999,999,999,999 head "Initial"
col parameter for a25 heading "Parameter"
col final_size for 999,999,999,999 head "Final"
col changed head "Changed At"
col current_size for 999,999,999,999 head "Current Size"
col min_size for 999,999,999,999 head "Min Size"
col max_size for 999,999,999,999 head "Max Size"
col granule_size for 999,999,999,999 head "Granule Size"

break on report
compute sum of current_size on report


select inst_id, component, current_size, min_size, max_size, granule_size
from gv$sga_dynamic_components
order by component, inst_id
/

col last_oper_type for a15   head "Operation|Type"
col last_oper_mode for a15  head "Operation|Mode"
col lasttime for a25 head "Timestamp"

select inst_id, component, last_oper_type, last_oper_mode,
  to_char(last_oper_time, 'mm/dd/yyyy hh24:mi:ss') lasttime
from gv$sga_dynamic_components
order by component, inst_id
/
clear breaks
break on inst_id skip 2

select inst_id, component, parameter, initial_size, final_size, status,
to_char(end_time ,'mm/dd/yyyy hh24:mi:ss') changed
from gv$sga_resize_ops
order by 1, 7
/

rem These values tend to help find explicit (minimum settings)
rem  for the components to help auto-tuning
rem  steer clear of over-aggressive moving of memory
rem  withing the SGA

clear breaks

col low format 999,999,999,999 head "Lowest"
col high format 999,999,999,999 head "Highest"
col lowMB format 999,999 head "MBytes"
col highMB format 999,999 head "MBytes"

select inst_id, component, min(final_size) low, (min(final_size/1024/1024)) lowMB,
           max(final_size) high, (max(final_size/1024/1024)) highMB
from gv$sga_resize_ops
group by inst_id, component
order by component, inst_id
/


clear breaks

col name format a40 head "Name"
col resizeable format a4 head "Auto?"

select * from gv$sgainfo order by inst_id
/

spool off
set termout on
set trimout off
set trimspool off
clear col

Parameters

The script prompts for:

None - this script requires no parameters

Usage

-- Basic usage
@SGAComponentsRAC.sql

Sample Output

Instance #  COMPONENT                 Current Size     Min Size         Max Size     Granule Size
---------- ------------------------- -------------- -------------- -------------- --------------
         1 DEFAULT buffer cache        1,073,741,824    671,088,640    1,879,048,192       16,777,216
         1 KEEP buffer cache                        0              0       16,777,216       16,777,216
         1 RECYCLE buffer cache                     0              0       16,777,216       16,777,216
         1 DEFAULT 2K buffer cache                  0              0       16,777,216        2,097,152
         1 DEFAULT 4K buffer cache                  0              0       16,777,216        4,194,304
         1 DEFAULT 8K buffer cache                  0              0       16,777,216        8,388,608
         1 DEFAULT 16K buffer cache                 0              0       16,777,216       16,777,216
         1 DEFAULT 32K buffer cache                 0              0       16,777,216       33,554,432
         1 Shared Pool                    335,544,320    151,142,400      671,088,640        4,194,304
         1 Large Pool                     33,554,432     16,777,216       67,108,864        4,194,304
         1 Java Pool                      16,777,216      4,194,304       67,108,864        4,194,304
         1 Streams Pool                            0              0       67,108,864        4,194,304
---------- ------------------------- -------------- -------------- -------------- --------------
sum                                  1,459,617,792

Instance #  COMPONENT                 Operation       Operation       Timestamp
                                     Type            Mode
---------- ------------------------- --------------- --------------- -------------------------
         1 DEFAULT buffer cache      STATIC          MANUAL
         1 KEEP buffer cache         STATIC          MANUAL
         1 RECYCLE buffer cache      STATIC          MANUAL
         1 DEFAULT 2K buffer cache   STATIC          MANUAL
         1 DEFAULT 4K buffer cache   STATIC          MANUAL
         1 DEFAULT 8K buffer cache   STATIC          MANUAL
         1 DEFAULT 16K buffer cache  STATIC          MANUAL
         1 DEFAULT 32K buffer cache  STATIC          MANUAL
         1 Shared Pool               GROW            IMMEDIATE       01/05/2025 14:30:25
         1 Large Pool                STATIC          MANUAL
         1 Java Pool                 STATIC          MANUAL
         1 Streams Pool              STATIC          MANUAL