 |
|
Oracle Tips by Burleson |
Display Oracle data buffer
contents
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 Oracle9i.
See
code depot for full scripts
ttitle 'Contents of
Data Buffers'
drop table t1;
create table t1 as
select
. . .
from
dba_objects o,
v$bh bh
where
. . .
order by
count(distinct file# || block#) desc
;
select
t1.owner c0,
object_name c1,
case when object_type = 'TABLE PARTITION' then 'TAB PART'
. . .
from
t1,
dba_segments s
where
. . .
;
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
buffer caches.
Here is the report from
buf_blocks.sql when run against a large Oracle data warehouse
(Listing 3.2).
Contents of Data Buffers
Number of
Percentage
Blocks in
of object
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
ODSA WORKORDER
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
DB_AUDIT CUSTOMER_AUDIT
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
statements.
The above
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
Mike Ault,
one of the world's top Oracle experts, has finally consented to release his
complete collection of more than 450 Oracle scripts, covering every possible
area of Oracle administration and management.
This is
the definitive collection of Oracle monitoring and tuning scripts, and it
would take thousands of hours to re-create this vast arsenal of scripts from
scratch.
Mike has
priced his collection of 465 scripts at $39.95, less than a dime per script.
You can download them immediately at this link:
http://www.rampant-books.com/download_adv_mon_tuning.htm
|
