Oracle Advanced Performance Tuning Scripts

I/O Optimization Scripts

sparse_indexes.sql

When rows are deleted from a table, or when indexed values are updated, space is freed in index blocks that may not readily be able to be reused. So after some time, the indexes on such tables may contain a large proportion of unusable internal free space. Not only does this waste database space, it increases the number of disk blocks that Oracle needs to read when retrieving data via these indexes. The impact can be so severe that it can become more efficient for the cost-based optimizer to access the data via full table scans, rather than to use indexes at all.

This script reports the data density for sparse indexes as a percentage of the number of keys that could fit in the leaf blocks (without any compression), and the number of blocks of space being wasted. Indexes are regarded as sparse if the internal free space is more than twice PCTFREE. Very sparse indexes should be rebuilt, using the ALTER INDEX REBUILD command. From Oracle 8.1 onwards, you should consider using the ALTER INDEX COALESCE command regularly, to prevent such problems.

Warning 1: This script is only as good as your optimizer statistics!
Warning 2: Sparse compressed indexes are not likely to be noticed.

Download script for: 8.1

uncompressed_indexes.sql

This script attempts to identify indexes that would benefit from leaf compression, in order of priority. The saving from the compression of just one column, or of all columns, can be estimated from the statistics available in the data dictionary. However, for many indexes on 3 or more columns, it is not possible to work out the optimal number of columns to compress using just the statistics available in the data dictionary. Therefore, this script just reports the minimum and maximum number of columns to compress, and relies on the DBA to know the data well enough to work out what to do.

Warning: This script is only as good as your optimizer statistics!

Download script for: 8.1

sparse_tables.sql

This script reports the data density for sparse tables as a percentage of the number of rows that could fit below the high-water mark. Very sparse tables should be rebuilt. In release 8.1, consider using the ALTER TABLE MOVE command.

A new PCTFREE of 1 is recommended, on the assumption that there is no risk of row migration. A new PCTUSED is suggested to allow for a little more than one row between PCTFREE and PCTUSED. This may not be enough for tables with very high insert and delete activity.

Warning: This script is only as good as your optimizer statistics!

Download script for: 7.3, 8.0 or 8.1

row_migration.sql

This script reports the degree of row migration as a percentage of the number of rows, for each table with migrated rows. Tables with LONGs or possible row lengths greater than the block size are ignored, as it is not easy to distinguish row migration from chaining for such tables.

A suggested PCTFREE is calculated as the free space left by one less than the number of average rows that can fit in a block. A new PCTUSED is suggested to allow for a little more than one row between PCTFREE and PCTUSED. This may not be enough for tables with very high insert and delete activity.

Warning: This script is only as good as your optimizer statistics!

Download script for: 7.3, 8.0 or 8.1

unscaled_numbers.sql

This script looks for NUMBER columns for which no scale has been specified and which have an average data length of more than 9 bytes (more than 15 digits). It may be that such columns are storing values with much greater precision than required or expected because Oracle does not round the results of internal floating point arithmetic before storing them in such columns. The consequent waste of space has a performance impact and should therefore be avoided. Please see this tip for more information, including some examples of the problem.

Warning: This script is only as good as your optimizer statistics!

Download script for: 8.1

redundant_indexes.sql

This script identifies non-unique indexes that form a leading prefix of another index. Such indexes are almost always redundant. Redundant indexes must be maintained during DML operations. This increases undo and redo generation, and impacts the cache hit rate against other data, as well as causing unnecessary datafile I/O.

Download script for: 8.0 8.1

disk_sorts.sql

Eliminating disk sorts by increasing the sort_area_size parameter can be a very effective strategy for reducing physical I/O. This script shows the number of disk sorts that have been performed, their average size, and the peak number of concurrent disk sorts. All I/O against all temporary tablespaces is assumed to be associated with disk sorts, so the average disk sort size may be overstated if there are temporary tablespaces that allow PERMANENT contents. The peak number of concurrent disk sorts may also be overstated in that case.

Many DBAs are overly conservative with their sort_area_size parameter settings. In fact, when considering an increase in sort_area_size, the maximum additional amount of physical memory that might be required is the proposed parameter change multiplied by the peak number of concurrent disk sorts.

Download script for: 8.0, 8.1

multiblock_read_test.sql

This script is used to find the maximum effective multiblock read count as an aid to setting the db_file_multiblock_read_count parameter on systems using unbuffered I/O. The script prompts the user to enter the name of a large table to scan, and then does so with a large multiblock read count, and with event 10046 enabled at level 8. The trace file is then examined to find the largest multiblock read actually performed. Of course, the table chosen for this test should have large extents, and should not have many blocks in Oracle's buffer cache, otherwise the size of the multiblock reads performed will be artificially limited.

This script is Unix specific, and depends on the trace_file_name.sql script. That script needs to be customized prior to use.

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