Oracle Advanced Performance Tuning Scripts

Buffer Cache Scripts

ideal_cache_size.sql

This scripts suggest an "ideal" number of buffers for each configured buffer pool under Oracle 8i. As such it provides a similar facility to that of the old db_block_lru_statistics and db_block_lru_extended_statistics parameters. The script assumes that the ideal number of buffers in each pool is the current number, plus the number of cold buffers due to be heated, less free buffers and hot buffers due to be cooled.

Of course, the ideal will fluctuate from moment to moment. So the script should be run several times under distinct workload peaks before drawing any firm conclusions.

Download script for: 8.1

buffer_pool_miss_rates.sql

This script reports the cache miss rates for each of the DEFAULT, KEEP and RECYCLE buffer pools. Please see our tip Calculating the Cache Hit and Miss Rates for a discussion of the issues.

Download script for: 8.0 or 8.1 Note: This script fails at 8.1.7.0 due to bug 1491213.

tunable_cache_miss_rate.sql

The tunable cache miss rate is the cache miss rate exclusive of multiblock reads. Please see our tip Calculating the Cache Hit and Miss Rates for more information.

Download script for: 7.3, 8.0 or 8.1 Note: Please use the 7.3 version of the script at 8.1.7.0.

buffer_busy_waits.sql

This script reports the number of buffer busy waits broken down by both block class and tablespace. It should be used as a first step in diagnosing buffer busy wait problems. The script below, or the trace_waits.sql script can be used for further diagnosis.

Download script for: 7.3, 8.0, 8.1

buffer_busy_sample.sql

This script looks at V$SESSION_WAIT 5 times in quick succession to sample the buffer busy wait parameters, and capture the active SQL statement of the waiting sessions. If the sample is representative, this can provide enough information to solve buffer busy wait problems.

Download script for: 7.3, 8.0 or 8.1

suggest_table_freelists.sql

This script is only valid for insert intensive tables with just one freelist, that have never been reorganized. It uses the average number of interested transaction list slots per block (last time the table was analyzed) as an indicator of the average number of concurrent inserts and thus the number of process freelists that might be required to avoid buffer busy waits. The number of freelists is then rounded up to a suitable prime number.

Warning: The statistics need to have been gathered with the ANALYZE command, not DBMS_STATS, because DBMS_STATS does not gather the average free space per block.

Download script for: 8.1

cached_blocks.sql

This script lists the segments that presently have blocks in the buffer cache, and the number of blocks cached. It is dependent on the APT_EXTENTS table which is created by the create_apt_extents.sql script below.

Download script for: 8.0, or 8.1

hot_blocks.sql

This script shows the distribution of hot blocks to hash latches. If multiple hot blocks map to the same hash latch, that increases the risk of contention for that latch. This script is dependent on the APT_EXTENTS table which is created by the create_apt_extents.sql script below.

Download script for: 8.1

hot_hash_latches.sql

This script lists the cache buffers chains latches with more than twice the average number of sleeps. Because long hash chain increase the risk of contention for these latches, this script shows the number of buffers protected by each latch, and in particular, the number of buffers containing consistent read block copies (clones).

Download script for: 7.3, 8.0 or 8.1

blocks_on_hot_latches.sql

This script lists the blocks protected by the cache buffers chains latches with more than twice the average number of sleeps. The script should be run repeatedly so that blocks that do not persist in the cache can be disregarded. Look for and tune the cache intensive SQL accessing the remaining blocks.

This script is dependent on the APT_EXTENTS table which is created by the create_apt_extents.sql script below.

Download script for: 7.3, 8.0, 8.1

replace_dual.sql

In some cases the data block for SYS.DUAL can be a hot block in cache. This script replaces the SYS.DUAL table with a view onto X$DUAL, thereby eliminating the cache access entirely.

Please note: This is not a normal, supported procedure. So do not do it on a production system, unless you have discussed the matter fully with Oracle Support beforehand.

Download script for: 8.0 or 8.1

buffer_pool_latches.sql

This script reports the latch activity against the LRU latches for each of the DEFAULT, KEEP and RECYCLE buffer pools. Of course, the idea is to distribute the latches according to the activity against the buffer pools, rather than just the size of the pools.

Download script for: 8.0 or 8.1

create_apt_extents.sql

This script creates, or re-creates, the APT_EXTENTS table, which is essentially just a copy of DBA_EXTENTS. This table is used by other APT scripts to efficiently determine the segments to which particular database blocks belong. If database extent usage may have changed, the APT_EXTENTS table should be recreated before use.

It is also a good idea to schedule a job to re-create APT_EXTENTS every night. The reason is that it might be helpful in some recovery situations. For example, if a segment is accidentally dropped, this information can be used to determine exactly which datafiles will be needed for the recovery. Similarly, if a datafile in a locally managed tablespace is lost, it may be useful to know which segments are affected. This information may not otherwise be available, because the used extent information for segments in locally managed tablespaces resides only in the segments themselves, and not in the data dictionary.

Download script for: 7.3, 8.0, 8.1

lookup_block.sql

This script can be used to lookup a hot block in DBA_EXTENTS.

Download script for: 7.3, 8.0 or 8.1


Ixora Pty Ltd.   All rights reserved.
12-Oct-2007 22:22
Search   Questions   Feedback   Up   Home