Oracle Advanced Performance Tuning Scripts

Latching Scripts

latch_sleeps.sql

If there are latch free waits, then this script can be used to further diagnose the problem. For each latch type, it reports the estimated impact of sleeps, and the number of sleeps per get. It also reports the number of other waits while holding each latch type, together with the latch level.

To reduce latch free waits, one should concentrate on the latches with the greatest impact. However, if there are a significant number of other waits while holding those latches, then one should regard those sleeps as a secondary symptom of another problem, such as contention for a higher level latch.

View sample listing

Download script for: 7.3, 8.0 or 8.1

child_sleeps.sql

This script may be used to investigate the distribution of sleeps over child latches. If sleeps for a set of latches are evenly distributed, then increasing the number of child latches should help to spread and reduce the contention. However, if the distribution of sleeps is skewed, then changing the number of child latches is not likely to have much effect.

Download script for: 7.3, 8.0 or 8.1

latch_gets.sql

This script reports the breakdown of willing-to-wait gets for each latch type, into simple gets, spin gets and sleep gets. Spin gets and sleep gets are latch gets that require spinning or sleeping respectively. Simple gets require neither.

View sample listing

Download script for: 7.3, 8.0 or 8.1

latch_spins.sql

This script compares spin gets to sleep gets as an indicator of the effectiveness of spinning, for each latch type.

View sample listing

Download script for: 7.3, 8.0 or 8.1

tune_spin_count.sql

This script can be used to dynamically tune the _spin_count parameter of an active instance. It first reports the spin hit rate and average cost of spinning over the life of the instance so far. It then prompts the user to dynamically modify the spin count and wait for a while to measure the effectiveness of the change. After waiting, the spin hit rate and average cost of spinning over the interval are reported. Under uniform load, this script can be run several times to find the optimum spin count range.

View sample listing

Download script for: 8.0, 8.1

latch_types.sql

This scripts prints a list of the latch types, and reports the number of child latches for each type. If a latch type has multiple child latches, V$LATCH_CHILDREN can be used to determine whether activity against the child latches is evenly distributed. V$LATCH_PARENT should also be checked to see whether there has been any activity against the parent latch.

View sample listing

Download script for: 7.3, 8.0 or 8.1

latch_levels.sql

This script is similar to the one above, except that it reports the latches by their levels.

Download script for: 7.3, 8.0 or 8.1

latch_where.sql

For those who are very familiar with the layers of the Oracle architecture and their functionality, this script may help them to know where in the code latch requests are failing.

View sample listing

Download script for: 7.3, 8.0 or 8.1

latch_where_now.sql

This is a similar script to the one above. It is intended to be run during an episode of latch contention to identify the code location where the misses are being sustained at that time.

Download script for: 8.0 or 8.1


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