Databases Reference
In-Depth Information
WHERE PR.NAME = 'physical reads'
AND PRD.NAME = 'physical reads direct'
AND PRDL.NAME = 'physical reads direct (lob)'
AND SLR.NAME = 'session logical reads';
8.
Enable the
KEEP
and
RECYCLE
Buffer Cache:
SHOW PARAMETER DB_KEEP_CACHE_SIZE
SHOW PARAMETER DB_RECYCLE_CACHE_SIZE
ALTER SYSTEM SET DB_KEEP_CACHE_SIZE=16M;
ALTER SYSTEM SET DB_RECYCLE_CACHE_SIZE=16M;
9.
Evaluate the size of the
CUSTOMERS
table in the
SH
schema:
SELECT TABLE_NAME, BLOCKS FROM DBA_TABLES
WHERE OWNER = 'SH' AND TABLE_NAME = 'CUSTOMERS';
10. Evaluate the size of two indexes in the
SH
schema:
SELECT INDEX_NAME, LEAF_BLOCKS FROM DBA_INDEXES
WHERE OWNER = 'SH'
AND INDEX_NAME IN ('CUSTOMERS_YOB_BIX', 'CUSTOMERS_PK');
11. Move a table and two indexes to the
KEEP
Buffer Cache:
ALTER TABLE SH.CUSTOMERS STORAGE (BUFFER_POOL KEEP);
ALTER INDEX SH.CUSTOMERS_YOB_BIX STORAGE (BUFFER_POOL KEEP);
ALTER INDEX SH.CUSTOMERS_PK STORAGE (BUFFER_POOL KEEP);
12. Evaluate the statistics and Hit Ratio for various Buffer Pools:
SELECT
NAME,
PHYSICAL_READS AS "physical reads",
DB_BLOCK_GETS AS "DB block gets",
CONSISTENT_GETS AS "consistent gets",
1 - (PHYSICAL_READS / (DB_BLOCK_GETS + CONSISTENT_GETS))
AS "hit ratio"
FROM V$BUFFER_POOL_STATISTICS
WHERE DB_BLOCK_GETS + CONSISTENT_GETS > 0;
13. Query the objects in the Buffer Cache:
COL OBJECT_NAME FOR A30
COL OBJECT_TYPE FOR A20
