Show SGA Statistics (show_sga.sql)

Use at Your Own Risk

This script is for educational purposes only. Always test in development environments before production use. See our disclaimer for full terms.

What This Script Does

This script displays detailed SGA memory allocation statistics for all components across all RAC instances. Essential for understanding memory distribution within the SGA and identifying memory consumption patterns.

The Script

set pagesize 999
column name  format a40
column bytes format 999,999,999,999,999

select inst_id
      ,pool
      ,name
      ,bytes
from gv$sgastat
order by name, inst_id
;

Parameters

The script prompts for:

• None - this script requires no parameters

Usage

-- Basic usage
@show_sga.sql

Sample Output

   INST_ID POOL                           NAME                                                      BYTES
---------- ------------------------------ ---------------------------------------- -------------------
         1                                fixed_sga                                          3,694,600
         2                                fixed_sga                                          3,694,600
         1 shared pool                    ASH buffers                                        16,777,216
         2 shared pool                    ASH buffers                                        16,777,216
         1 shared pool                    CCursor                                               331,776
         2 shared pool                    CCursor                                               331,776
         1 buffer_cache                   buffer_cache                                    1,073,741,824
         2 buffer_cache                   buffer_cache                                    1,073,741,824
         1 shared pool                    dictionary cache                                   83,886,080
         2 shared pool                    dictionary cache                                   83,886,080
         1 shared pool                    free memory                                       134,217,728
         2 shared pool                    free memory                                       134,217,728
         1 large pool                     free memory                                        33,554,432
         2 large pool                     free memory                                        33,554,432
         1 java pool                      free memory                                        16,777,216
         2 java pool                      free memory                                        16,777,216
         1 shared pool                    library cache                                     100,663,296
         2 shared pool                    library cache                                     100,663,296
         1 shared pool                    sql area                                           67,108,864
         2 shared pool                    sql area                                           67,108,864