Tuesday, 20 May 2008

Oracle Quick Tip: Querying Buffer Cache Usage

This SQL statement shows which objects in our schema are using the most blocks in the data buffer cache.
SELECT object_name, object_type type, COUNT(*) blocks
FROM v$bh
JOIN user_objects ON object_id = objd
GROUP BY object_name, object_type
ORDER BY blocks DESC;
OBJECT_NAME                   TYPE     BLOCKS 
----------------------------- -------- ------
MGMT_METRICS_RAW_PK INDEX 1960
MGMT_METRICS_1HOUR_PK INDEX 1183
MGMT_METRICS_1DAY_PK INDEX 349
MGMT_METRICS TABLE 149
MGMT_SYSTEM_PERF_LOG_IDX_01 INDEX 62
MGMT_CURRENT_METRICS_PK INDEX 29
MGMT_SYSTEM_PERFORMANCE_LOG TABLE 21
MGMT_TARGETS TABLE 14
MGMT_METRIC_COLLECTIONS TABLE 14
MGMT_JOB_EMD_STATUS_QUEUE TABLE 14
To display all objects, regardless of schema, use DBA_OBJECTS instead of USER_OBJECTS.

No comments: