||Oracle Tips by Burleson
Display Oracle data buffer
This is the most important script
in this text because it provides a detailed analysis of those objects
in the data buffers. This information is critical when considering an
alteration to the data buffer sizes.
Many thanks to Randy Cunningham
for the developing this sophisticated and powerful script. Please note
that the script below only works with Oracle.
code depot for full scripts
ttitle 'Contents of
drop table t1;
create table t1 as
. . .
. . .
count(distinct file# || block#) desc
case when object_type = 'TABLE PARTITION' then 'TAB PART'
. . .
. . .
A sample listing from this
exciting report is shown below. We can see that the report lists the
tables and indexes that reside inside the data buffer. This is
important information for the Oracle professional who needs to know
how many blocks for each object reside in the RAM buffer. To
effectively manage the limited RAM resources, the Oracle DBA must be
able to know the ramifications of decreasing the size of the data
Here is the report from
buf_blocks.sql when run against a large Oracle data warehouse
Contents of Data Buffers
Object Object Buffer
Buffer Buffer Block
Owner Name Type Cache
Blocks Pool Size
------------ -------------------------- -----------
---------- ------- -------
DW01 WORKORDER TAB PART
94,856 6 DEFAULT 8,192
DW01 HOUSE TAB PART
50,674 7 DEFAULT 16,384
TABLE 28,481 2 DEFAULT 16,384
DW01 SUBSCRIBER TAB PART
23,237 3 DEFAULT 4,096
ODS WORKORDER TABLE
19,926 1 DEFAULT 8,192
DW01 WRKR_ACCT_IDX INDEX
8,525 5 DEFAULT 16,384
DW01 SUSC_SVCC_IDX INDEX
8,453 38 KEEP 32,768
DW02 WRKR_DTEN_IDX IDX PART
6,035 6 KEEP 32,768
DW02 SUSC_SVCC_IDX INDEX
5,485 25 DEFAULT 16,384
DW02 WRKR_LCDT_IDX IDX PART
5,149 5 DEFAULT 16,384
DW01 WORKORDER_CODE TABLE
5,000 0 RECYCLE 32,768
DW01 WRKR_LCDT_IDX IDX PART
4,929 4 KEEP 32,768
DW02 WOSC_SCDE_IDX INDEX
4,479 6 KEEP 32,768
DW01 SBSC_ACCT_IDX INDEX
4,439 8 DEFAULT 32,768
DW02 WRKR_WKTP_IDX IDX PART
3,825 7 KEEP 32,768
TABLE 3,301 99 DEFAULT 4,096
DW01 WRKR_CLSS_IDX IDX PART
2,984 5 KEEP 32,768
DW01 WRKR_AHWO_IDX INDEX
2,838 2 DEFAULT 32,768
DW01 WRKR_DTEN_IDX IDX PART
2,801 5 KEEP 32,768
Listing 3.2 – Specific objects in
the Oracle RAM data buffers
This is an interesting report
because we see three object types (tables, indexes, and partitions),
and we also see the sub-sets of the DEFAULT pool for KEEP and RECYCLE.
Also, note that all indexes are defined in the largest supported block
size (db_32k_cache_size), and multiple buffer pools of 4K, 8K,
16K and 32K sizes are defined.
The output of this script is
somewhat confusing because of the repeated DEFAULT buffer pool name.
This is misleading because the KEEP and RECYCLE buffer pools are
sub-sets of db_cache_size and can ONLY accommodate objects with
the DEFAULT db_block_size.
Conversely, any block sizes that
are NOT the default db_block_size, go into the buffer pool
named DEFAULT. As you can see from the output listing, there are
really 6 mutually exclusive and independently-sized buffer pools, and
four of them are called "DEFAULT."
It is interesting to run this
report repeatedly because the Oracle data buffers are so dynamic.
Running the script frequently allows us to view the blocks entering
and leaving the data buffer. We can see the midpoint insertion method
in action and the hot and cold regions as they update. Each time a
block is re-referenced it moves to the head of the MRU chain on the
hot side of the data buffer. Blocks that are accessed less frequently
will age-out, first moving into the cold region and eventually being
paged-out to make room for new incoming blocks.
This buf_blocks.sql script
is even more important when considering a decrease to a cache size.
When you issue an alter system command to decrease the cache size,
Oracle will grab pages from the least recently used (LRU) end of the
buffer. Depending on the amount of RAM removed, an alter system
command will un-cache data blocks that may be needed by upcoming SQL
text is an excerpt from "Creating
a Self Tuning Oracle Database", by Rampant TechPress. It is only
$9.95 and all scripts in this tips can be immediately downloaded.
Complete Oracle Script Collection Available
Burleson Consulting shares their personal arsenal of
Oracle data dictionary scripts in this comprehensive download. With
decades of experience using Oracle monitoring scripts and Oracle tuning
scripts, BC experts share their secrets for navigating the Oracle data
Packed with 681 ready-to-use Oracle scripts, this is
the definitive collection for every senior Oracle DBA.
It would take man-years to develop these scripts
from scratch, making this download the best value in the Oracle
priced this collection of 681 scripts at $79.95.
You can download them immediately at this link: