When monitoring SGA size and utilization, you need to think about the data buffers (controlled by the INIT.ORA parameter DB_BLOCK_BUFFERS) and the rest of the SGA. In Chapter 3, we presented mechanisms for tracking the usage of the data buffers. This section focuses on the rest of the SGA. There are two dynamic performance views that provide information about how the SGA is being utilized (see Chapter 14, for more information on these views). These are the V$RESOURCE_LIMIT and V$SGASTAT views.

The scripts presented here do two things: first, they create a separate table in which to store current values from these tables; second, they store the values. The first set of scripts should be run only once. The second set should be run on a periodic basis, depending upon your circumstances.

The following script creates the storage tables. Note that you may want to modify this script to specify tablespace and storage parameters.

CREATE TABLE dba$resource_limit
             (timestamp            DATE,
              resource_name        VARCHAR2(30),
              current_utilization  NUMBER,
              max_utilization      NUMBER,
              initial_allocation   VARCHAR2(10),
              limit_value          VARCHAR2(10));

CREATE TABLE dba$sgastat
             (timestamp            DATE,
              pool                 VARCHAR2(30),
              name                 VARCHAR2(30),
              bytes                NUMBER);

The next script copies current values into the storage table for future reference:

INSERT INTO dba$resource_limit
                (timestamp,
                 resource_name,
                 current_utilization, 
                 max_utilization,
                 initial_allocation,
                 limit_value
                )
SELECT TRUNC(SYSDATE),
       resource_name, 
       current_utilization,
       max_utilization, 
       initial_allocation, 
       limit_value 
FROM v$resource_limit;

INSERT INTO dba$sgastat
                (timestamp,
                 pool,
                 name,
                 bytes
                )
SELECT TRUNC(SYSDATE),
       pool,
       name,
       bytes
FROM v$sgastat;
COMMIT;