Ixora News - September 2000

Welcome to the September 2000 issue of "Ixora News" - available online at http://www.ixora.com.au/newsletter/2000_09.htm.
(Please note: This newsletter is no longer hosted at ListBot, so the way to unsubscribe has changed. To unsubscribe send a blank email to quit-news@ixora.com.au.)

In this issue ...

Get answers fast	- A mailing list for advanced performance tuning answers.
The cool pool	- Shared pool fragmentation problems are history in 8.1.6.
DBWn's new tricks	- This old dog can juggle multiple write batches under 8i.
APT scripts news	- Changes to our collection of advanced performance tuning scripts.
Going to OpenWorld?	- Catch the Ixora performance tuning seminar too!

Get answers fast

One of the great attractions of the Ixora web site is its Q&A section where you can read detailed, knowledgeable answers to many advanced performance tuning questions. You can now read those answers as they become available, without even visiting our web site. We have established an email mailing-list to which the new answers are sent. To subscribe, just send a blank email to join-answers@ixora.com.au. (You can unsubscribe at any time by sending a blank email to quit-answers@ixora.com.au). New answers are also now posted automatically to the Ixora web site, so please remember to search our web site before asking questions so that you can avoid repeating frequently asked questions.

The cool pool

At last shared pool free memory fragmentation problems are a thing of the past. For years Oracle DBAs have had to keep the shared pool big enough to avoid ORA-4031 errors, but small enough to avoid the risk of shared pool latch contention associated with free memory fragmentation. In some cases this has been a very delicate and difficult balance to maintain.

In 8.1.6, Oracle have made a very simple but highly effective change to shared pool management. Instead of having just 11 free lists covering a wide range of chunk sizes each, there are now 255 free lists. In particular, there are now 199 dedicated free lists for each possible chunk size under 812 bytes. That means that there are no more free list scans for small chunks. These scans used to cause extended retention of the shared pool latch and thus latch contention. Also the remaining 56 free lists now mostly cover a size range of only 64 bytes (except for the last 6) so while there will still be scans on those free lists, they will be much more brief than before.

Because of this it now safe to size the shared pool generously without introducing an unacceptable risk of shared pool latch contention. In fact, this change has been so effective that an upgrade to 8.1.6 is now our default recommendation for clients affected by this problem on earlier releases of Oracle.

DBWn's new tricks

DBWn got a lot smarter in Oracle 8.0, but it still could not start a new batch of writes until the previous batch had completed. So, although it could write multiple blocks in parallel (either directly using operating system asynchronous I/O, or via multiple I/O slaves), just one slow disk could nevertheless affect total write throughput.

Under Oracle 8i DWBn is inherently asynchronous. That is, it can start a new batch of writes while other batches are still being written. To support this, the write list is now in two parts. The main write list contains candidate buffers for writing as before, and there is an auxiliary write list containing buffers that are busy being written already. The replacement list is also in two parts. The main list has in-use buffers, and the auxiliary list has buffers returned from being written (it also has scanned unpinned cold clean buffers for which no write was needed). The subdivision of these lists makes scanning more effective.

There have also been improvements in the priority with which particular types of blocks are written. Previously, pinged blocks were the only class of buffers that got special treatment. Now there are separate queues (and priorities) for the various types of checkpoint writes. This means that high priority buffers are written more quickly than before. Even more reason to upgrade to 8i if you have not done so already.

APT scripts news

A bug in some of the buffer cache scripts for 8.1
There was a bug in some of the APT scripts for buffer cache performance tuning that has now been fixed. With the introduction of tempfiles it is no longer possible to join X$BH.FILE# (and X$KCBFWAIT.INDX, V$SESSION_WAIT.P1 and others) directly to FILE$.FILE# (and related data dictionary columns and dependent data dictionary views) with an offset of 1. There is a discontinuity in the FILE# range used in the X$ tables, corresponding to the order in which datafiles and tempfiles are listed in the controlfile. It seems that this can be worked around by joining to X$KCCFE.FETSI with an offset of 1, and thence from X$KCCFE.FENUM to the data dictionary. This has now been done. If you have downloaded any of these scripts, please obtain a new copy.

session_cursor_cache.sql
It is not widely understood that soft parses are not merely the complement of hard parses if the session cursor cache facility is in use (and it should be). Session cursor cache hits avoid library cache access entirely, whereas soft parses are hits while accessing the library cache. When tuning the session_cached_cursors parameter to reduce load on the library cache, it is useful to look at the breakdown of parse operations into
* session cursor cache hits
* library cache hits
* library cache misses.
To assist with such tuning, the Oracle 8 version of the session_cursor_cache.sql script has been enhanced to report this breakdown.

child_sleeps.sql
Latch contention is a common problem in advanced Oracle performance tuning. When latch contention affects a set of child latches of the same type, one important tuning approach to consider is that of increasing the number of child latches. If the contention is evenly distributed over the set of existing child latches, then increasing the number of 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. This new script may be used to investigate the distribution of sleeps over child latches to assess the potential benefit of increasing the number of latches.

dump_logfile.sql and redo_time.sql
These new scripts will be of interest to those who are interested in learning more about Oracle internals. One way to learn is to build a test case and then examine the contents of the redo log files to see what redo was generated. These scripts are intended to support that sort of experimentation. The dump_logfile.sql script dumps the contents of a redo log file to the trace file for the current process in a human (?) readable format. One interesting thing about this script, even if you're not interested in the internals of log files, is that it demonstrates how to execute code conditionally in an SQL*Plus script despite not having an IF statement available. The redo_time.sql script can be used to express times in decimal using the special formula required when specifying minimum and maximum time boundaries for the dumping of redo.

Going to OpenWorld?

If so, you have an opportunity to linger for a day in San Francisco to meet Steve Adams, Ixora's principle consultant, and to pick up all the otherwise unavailable advanced Oracle performance tuning information contained in his "SGA Internals and Advanced Instance Tuning" seminar. This seminar is Steve at his best, explaining what he knows best - how Oracle really works and how it should be tuned for the most demanding of applications. Details are available on the Ixora web site at http://www.ixora.com.au/seminars/index.htm. If you are one of those who indicated that you would like to attend but have not yet registered, please do so soon. We need a few more registrations to help cover our costs.