Script to Identify Objects and Amount of Blocks in the Buffer Pools – Default, Keep, Recycle, nK Cache

Applies to:

Oracle Server – Enterprise Edition – Version: 9.2.0.1 to 10.2.0.1
Information in this document applies to any platform.

Purpose

This script will provide the objects that are currently pinned to the buffer pools as well as the number of blocks from the object that are currently loaded into memory.

Software Requirements/Prerequisites

Tested on :
9.2.0.7
10.1.0.3
10.2.0.1

Configuring the Script

The base (X$) table is used; therefore, this must be performed by connecting as :
SQL> conn / as sysdba

Running the Script

1. Copy the provided script into the textpad/editor and save it as :
dbbuffer.sql
2. Start a SQL*Plus session.
$ sqlplus /nolog
3. Connect to the database with the SYSDBA privilege.
SQL> conn / as sysdba
4. Run the script :
SQL> @dbbuffer.sql
5. Inspect the dbbuffer.log for success/failure of the script.
$ more dbbuffer.log

Script:

spool dbbuffer.log

select decode(pd.bp_id,1,'KEEP',2,'RECYCLE',3,'DEFAULT',
         4,'2K SUBCACHE',5,'4K SUBCACHE',6,'8K SUBCACHE',
         7,'16K SUBCACHE',8,'32KSUBCACHE','UNKNOWN') subcache,
         bh.object_name,bh.blocks
from x$kcbwds ds,x$kcbwbpd pd,(select /*+ use_hash(x) */ set_ds,
         o.name object_name,count(*) BLOCKS
         from obj$ o, x$bh x where o.dataobj# = x.obj
         and x.state !=0 and o.owner# !=0
         group by set_ds,o.name) bh
where ds.set_id >= pd.bp_lo_sid
and ds.set_id <= pd.bp_hi_sid
and pd.bp_size != 0
and ds.addr=bh.set_ds;

spool off;

Sample Output:

SUBCACHE     OBJECT_NAME                        BLOCKS
------------ ------------------------------ ----------
DEFAULT      MGMT_SYSTEM_PERF_LOG_IDX_02             2
DEFAULT      MGMT_COLLECTION_TASKS_IDX01             1
DEFAULT      RLM$SCHACTIONORDER                      1
DEFAULT      MGMT_JOB_EXEC_SUMMARY                   3
DEFAULT      MGMT_JOB_EXEC_SUMM_IDX04                1
DEFAULT      MGMT_JOB_PURGE_VALS_IDX01               1
DEFAULT      MGMT_NOTIFY_QTABLE                      3
DEFAULT      MGMT_POLICY_ASSOC_EVAL_DETAILS          2
DEFAULT      INVENTORY_IX                            6
DEFAULT      MGMT_HA_RMAN_CONFIG_ECM_PK              1
DEFAULT      MGMT_SYSTEM_PERF_LOG_IDX_01             3

© 2010, www.oracledatabase12g.com. 版权所有.文章允许转载,但必须以链接方式注明源地址,否则追究法律责任.

相关文章 | Related posts:

1. Script:List Buffer Cache Details
2. How to Find all corrupted objects associated with one or many contiguous corrupted blocks reported in V$DATABASE_BLOCK_CORRUPTION or alert log / trace file
3. gc lost blocks diagnostics
4. SCRIPT: VALIDATE.SQL to ANALYZE .. VALIDATE STRUCTURE objects in a Tablespace
5. EVENT: 10231 "skip corrupted blocks on _table_scans_"
6. Oracle Block Cache Layer And Its Corresponding Corruption Error Messages.
7. Script to Collect RAC Diagnostic Information (racdiag.sql)
8. RMAN 11G : Data Recovery Advisor – RMAN command line example
9. Know more about Buffer Cache and Latch
10. How to Perform a Health Check on the Database [ID 122669.1]