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;