How to Check SGA Components Memory Distribution on Database 9i

  • by

SGA Components

For database 9i, there is no dynamic view gv$sgainfo like 10g or 11g that can represent current memory distribution of every component. Another dynamic view gv$sga_dynamic_components might help, but the information is incomplete.

If you have memory issues on database 9i, you could seek gv$sgastat for help, which can provides more detailed information AFTER some processes and combinations. Let’s see the sql statement.

$ cat -n CheckSGA.sql
     1  SET pagesize 20;
     2  column "Now Used (MB)" format 9,999.99;
     3  SELECT *
     4  FROM
     5    (SELECT inst_id,
     6      pool component,
     7      SUM(bytes)/1024/1024 "Now Used (MB)"
     8    FROM gv$sgastat
     9    WHERE pool IS NOT NULL
    10    GROUP BY inst_id,
    11      pool
    12    UNION ALL
    13    SELECT inst_id,
    14      name component,
    15      bytes/1024/1024 "Now Used (MB)"
    16    FROM gv$sgastat
    17    WHERE pool IS NULL
    18    )
    19  ORDER BY 1,2;

Then we execute the script as the following:

SQL> @CheckSGA.sql

   INST_ID COMPONENT                  Now Used (MB)
---------- -------------------------- -------------
         1 buffer_cache                       40.00
         1 fixed_sga                           2.12
         1 java pool                           4.00
         1 large pool                          4.00
         1 log_buffer                          2.29
         1 shared pool                       280.00
         2 buffer_cache                       40.00
         2 fixed_sga                           2.12
         2 java pool                           4.00
         2 large pool                          4.00
         2 log_buffer                          2.29
         2 shared pool                       264.01

12 rows selected.

The result shows the memory distribution of every SGA component by instances.

Leave a Reply

Your email address will not be published. Required fields are marked *