Rem Filename   : sgamem.sql
Rem 
Rem Parameters : None
Rem
Rem Author     :
Rem RDBMS      : Oracle V8.x, Oracle V9.x
Rem
Rem Modification history
Rem --------------------
Rem 25-May-2000 Initial creation.
Rem 25-Mar-2002 Updated documentation.
Rem 26-Apr-2004 - It appears that in Oracle 9.2 the buffer cache 
Rem               no longer is included in v$sgastats if the 
Rem               "old-style" parameter db_block_buffers is used 
Rem               instead of the "new" db_cache_size parameter.
Rem               Display a warning message if this is the case.
Rem             - Added computation of the total SGA size.
Rem 09-Jun-2005 Enlarged Current size column to cope with values
Rem             up to 10 Gb.
Rem 16-May-2006 Adapted to display the Java pool areas properly.
Rem
Rem ===========================================================================================
Rem
Rem Description : Lists the current memory usage per area in the SGA.
Rem               Lesser important area's are grouped into "sum of rest" and
Rem               "PL/SQL" represents the sum of "PL/SQL DIANA" and "PL/SQL MPCODE".
Rem               The KQLS heap is logically part of the dictionary cache, 
Rem               but physically part of the library cache.
Rem
Rem Sample output
Rem -------------
Rem -------------------------------------------------------------------------------------------
Rem Database              : PDB1 on server tcheflxb
Rem Startup date and time : 11-MAY-2006 15:17:05  ( uptime = 4 days, 16 hours and 25 minutes )
Rem Current date and time : 16-MAY-2006 07:43:01
Rem -------------------------------------------------------------------------------------------
Rem 
Rem Pool           Area                             Current size
Rem -------------- ------------------------------ --------------
Rem java pool      free memory                        26.050.560
Rem java pool      memory in use                       7.503.872
Rem shared pool    PL/SQL                              7.992.080
Rem shared pool    dictionary cache                    4.274.432
Rem shared pool    free memory                        83.826.784
Rem shared pool    library cache                     152.222.592
Rem shared pool    sql area                          260.931.664
Rem shared pool    sum of rest                        77.955.008
Rem                buffer_cache                    1.056.964.608
Rem                fixed_sga                             738.560
Rem                log_buffer                          1.312.768
Rem                                               --------------
Rem sum                                            1.679.772.928
Rem
Rem ==========================================================================================

SET FEEDBACK      OFF
SET TRIMSPOOL     ON
SET VERIFY       OFF
SET LINESIZE 3500

alter session set nls_numeric_characters = ",.";

@@header_dbinfo 91 DCS

COLUMN pool     FORMAT A14              HEADING "Pool"
COLUMN name     FORMAT A30              HEADING "Area"
COLUMN CurSize  FORMAT 9G999G999G999    HEADING "Current size"

BREAK ON REPORT
COMPUTE SUM OF CurSize       ON REPORT

SELECT pool, 
       name, 
       bytes           "CurSize"
  FROM v$sgastat
 WHERE pool is null
    OR name in ( 'free memory', 'db_block_buffers', 'dictionary cache', 'library cache', 'sql area', 'memory in use' )
union all
SELECT pool, 
       'PL/SQL', 
       sum(bytes)      "CurSize"
  FROM v$sgastat
 WHERE name like 'PL/SQL%'
 GROUP BY pool
union all
SELECT pool, 
       'sum of rest', 
       sum(bytes)      "CurSize"
  FROM v$sgastat
 WHERE pool is not null
   AND name not in ( 'free memory', 'db_block_buffers', 'dictionary cache', 'library cache', 'sql area', 'memory in use' )
   AND name not like 'PL/SQL%'
 GROUP BY pool
 ORDER BY 1,2;

PROMPT

SET PAGESIZE 0

-- If the "old-style" block buffer parameters are used, then block buffers are not included when
-- selecting from v$sgastats.
 
SELECT CASE WHEN (version > '8') and (p.value > 0)
              THEN 'TAKE NOTE : Buffer cache is not included due to the use of the
            db_block_buffers parameter.
            The db_block_buffers value is not the same as the buffer cache.' 
            END ActiveState
  FROM v$instance i, 
       v$parameter p
 WHERE p.name = 'db_block_buffers';

CLEAR BREAKS
CLEAR COMPUTES

SET FEEDBACK ON
SET PAGESIZE 9999