Oracle Advanced Performance Tuning Scripts

Query Optimization Scripts


This script reports the number of segments in each schema with and without statistics. In general, either all of the segments, or none of the segments in a schema should have statistics.

Download script for: 7.3, 8.0 or 8.1


This script checks the current size of database segments against their size when they were last analyzed, and reports segments for which their is a difference as having stale statistics.

Download script for: 7.3, 8.0, 8.1


This script reports the percentage of load on the instance due to the most expensive few SQL statements. Tuning these statements can often make huge improvements in overall system performance.

Download script for: 7.3, 8.0 or 8.1.5, 8.1.6+


This script identifies the most cache intensive SQL statements. Tuning these statements can often make significant improvements in overall system performance, particularly on CPU bound systems suffering cache buffers chains latch contention and/or buffer busy waits.

Download script for: 7.3, 8.0 or 8.1.5, 8.1.6+


This script can be used to examine the possible access paths to a table when tuning a problem query. By default it will look at the table subject to the most disk reads. For each column it reports the number of nulls, the average selectivity of equality predicates, and the access paths available for that column. This can be used to select the optimal join order for the query and thus the optimal join mechanism and access path for the table. It can also be used to identify unhelpful indexes on non-selective columns, and the absence of potentially useful indexes.

The script also identifies problems with the storage of the table itself including poor data density, row migration, cluster block chaining, freelist contention and inappropriate caching directives. Each of these issues can affect query execution and may be the cause of poor performance.

Similarly, for each index the script reports the index data density and a percentage metric as to how well-ordered the table rows are with respect to the index key. This can be used to identify when poor index or table condition is affecting index range scan performance.

The script also shows which index columns are compressed and reports the average column length in bytes and the number of entries per key for each indexed column (or percentage selectivity for less selective columns). This can be used to check whether the index is optimally compressed.

For indexed columns with histograms the script reports the proportion of popular values and the density (median selectivity) of non-popular values. If there are few popular values and the density is approximately equal to the average selectivity, then either the histogram has too few buckets or it is redundant because the column values are well distributed.

This script is dependent on the index_access_paths.sql script below, and the apt_format and next_prime functions from the Prerequisite Scripts page.

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

View sample listing

Download script for: 8.1


This script is required for the table_access_paths.sql script above. It is not intended to be use independently.

Download script for: 8.1


Histograms enable the optimizer to more accurately estimate the selectivity of literal predicates against columns with skewed data distributions. This can help the optimizer to choose a better access path and possibly join order for certain queries than might otherwise be the case. However, redundant histograms on columns with uniformly distributed data, and unduly large histograms on columns with skewed data distributions just increase the CPU cost of parsing and waste space in the shared pool. Therefore histograms should only be created where they are beneficial, and should not be any larger than necessary. Please note that histograms on columns that are not indexed can nevertheless be beneficial because they inform the optimizer of the cardinality of their table for the query and can thus influence the join order.

This script reports whether a particular column of a particular table has any popular values or a sufficiently skewed data distribution to warrant a histogram. If so, it recommends the optimal number of histogram buckets to use.

Be warned that this script has to do a full scan of the table (or a suitable index) to determine the data distribution of the column. That can take a while on a large table.

Download script for: 8.1.6+


People sometimes do not bother to define columns as NOT NULL in the data dictionary, even though these columns should not contain nulls, and indeed never do contain nulls because the application ensures that a value is always supplied. You may think that this is a matter of indifference, but it is not. The optimizer sometimes needs to know that a column is not nullable, and without that knowledge it is constrained to choose a less than optimal execution plan (more).

This script identifies columns that appear to be candidates for NOT NULL constraints.

Download script for: 8.0 or 8.1


When considering indexing changes or other physical changes that will affect the optimizer, it is important to consider all the SQL statements that are dependent on the table, rather than just the "problem query". This script assists with that by listing all the SQL statements currently in the library cache that are dependent on a particular table.

Download script for: 8.0 or 8.1

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