Ixora News - November 2000

Welcome to the November 2000 issue of "Ixora News" available online at http://www.ixora.com.au/newsletter/2000_11.htm.
(To unsubscribe send a blank email to quit-news@ixora.com.au.)

In this issue ...

Sort memory   - Do you tolerate unnecessary disk sorts?
cache buffers chains latches   - Why less is more under Oracle8i
Tuning checkpoints   - A new approach to an old problem
Seminars in Asia   - Learn about SGA internals and advanced performance tuning
Practical Oracle8i   - A new book from Jonathan Lewis

Sort memory

Try this quick quiz.
How much memory does each Oracle process reserve for sorting?

A.   sort_area_size
B.   sort_area_retained_size
C.   sort_area_size + sort_area_retained_size
D.   none
E.   none of the above
The most popular answer is A, however B also gets a lot of votes. A few clever people choose C, but the correct answer is D. Yes, none! The myth that each process requires an area of memory equal to the sort_area_size is wrong. Until a process begins a sort, it has not one byte reserved for sorting. And the myth that sort_area_retained_size is retained after each sort to be used for subsequent sorts is wrong also. No sort memory is retained for subsequent sorts.

Sort areas are dynamically allocated in "cursor work heaps" during cursor execution. They grow in small increments up to the limit set by the sort_area_size parameter. Cursor work heaps are allocated in the CGA if the data that they contain is only needed during the current call. Each execute and fetch call and every other SQL call including recursive calls uses its own CGA, dynamically allocated as a subheap of the PGA. When a call has finished its CGA is entirely freed. Sort areas required for DML statement execution can be allocated in the CGA, because the sorted data is not needed beyond the end of the call. However, rows sorted during the final sort of select statement execution are needed for subsequent fetch calls. If there is no sort_area_retained_size constraint, then such sorts are performed directly in the UGA. Otherwise they are performed in the CGA and a separate buffer of up to sort_area_retained_size is allocated in the UGA for the fetch phase. The sort_area_retained_size parameter also limits the amount of CGA sort memory that may be retained for a non-final sort row source. Of course, if a sort is smaller than sort_area_size but larger than sort_area_retained_size then temporary segment space will be needed to buffer some of its results.

Sort memory allocated in the CGA is of course freed to the PGA heap as soon as the call completes. Sort memory allocated in the UGA is released to the UGA heap once the last row has been fetched, and most of it is then immediately released to its parent heap (the PGA, or SGA in MTS connections). Free/unused PGA memory will be readily paged out by the operating system under memory pressure. So the maximum amount of physical memory required by an Oracle instance for sorting is approximately sort_area_size times the number of concurrent sorts only. No memory is reserved specifically for sorting.

The number of concurrent sorts can however be greater than the number of active sessions, because some query execution plans require multiple sorts, of which two may be active concurrently - one in the merge phase, producing rows from its row source, and one is the sort phase, consuming rows for its row source. Thus the maximum amount of sort memory that might be required by a single session is sort_area_size + sort_area_retained_size, which is why the answer C had some merit.
Many DBAs are overly conservative with their sort_area_size settings, and put up with a lot of moderately sized disk sorts, mainly because they believe that an increase would consume a lot of memory. In fact, the growth in physical memory requirements would be no more than the proposed increase in sort_area_size multiplied by the peak number of concurrent disk sorts. You can determine the peak number of concurrent disk sorts ever performed by an instance by looking at MAX_UTILIZATION for sort_segment_locks in V$RESOURCE_LIMIT (or temporary_table_locks if you have a permanent temporary tablespace). The APT script disk_sorts.sql on the Ixora web site reports the number of disk sorts, their average size, and the peak number of concurrent disk sorts, which is much of what is needed to choose a new value for the sort_area_size parameter. Here is an example.
---------- ------------ ---------------
       232        4914K               2
This instance has done 232 disk sorts with an average size of just less than 5M, and has had at most two concurrently active disk sorts. If the DBA were to increase the sort_area_size from 1M to 5M, that would save about 1.1G of foreground I/O time at a cost of just 8M of physical memory. (Please note that increasing the sort_area_size has virtually no impact on very large sorts. The objective is just to eliminate the moderately sized disk sorts.)

cache buffers chains latches

Some people have been confused by the number of cache buffers chains latches shown in V$LATCH_CHILDREN under release 8.1. Previously there was exactly one cache buffers chains latch per hash bucket (or hash chain). But under release 8.1, the old nexus between hash buckets and hash latches has been broken. Multiple hash chains can now be protected by the same latch. Although the default number of latches has gone down, the default number of hash chains has gone up by a factor of 8. The corresponding reduction in the average length of the hash chains of course reduces latch retention and thus the risk of latch contention is not increased despite the fact that there are now more buffers per latch.

Up to release 8.0 _db_block_hash_buckets could be used to set both the number of hash chains and latches. From 7.1 it was constrained to prime numbers, and used to default to

next_prime(db_block_buffers / 4)
Under release 8.1, _db_block_hash_buckets now defaults to 2 * db_block_buffers, and the new _db_block_hash_latches parameter must be used to control the number of hash latches if necessary. It is interesting to note that this parameter is constrained to binary powers so that Oracle can calculate which latch to use with a simple SHIFT operation, rather than a DIVIDE operation. The default number of hash latches depends on db_block_buffers. If db_block_buffers is less than 2052 buffers, then the default number of latches is
2 ^ trunc(log(2, db_block_buffers - 4) - 1)
If db_block_buffers is bigger than 131075 buffers, then the default number of latches is
2 ^ trunc(log(2, db_block_buffers - 4) - 6)
If db_block_buffers is between 2052 and 131075 buffers, then there are 1024 latches by default. Sites that have used _db_block_hash_buckets to combat cache buffers chains latch contention under previous releases should allow the value to default when upgrading to Oracle 8i. Remember that contention for these latches is almost always a symptom of one or more blocks being very hot due to unresolved application or SQL tuning problems.

Tuning checkpoints

In a properly configured and well tuned Oracle8 or 8i database, you should not need to worry about the speed of checkpointing. However, if you suddenly inherit a database with just two small log files, only a few datafile disks, no asynchronous I/O and a large cache, then you may want to accelerate the checkpoint processing. Under Oracle8, you might attempt to do this by increasing the value of the db_block_checkpoint_batch parameter, but that parameter is no longer available under release 8.1. A good thing too, because it is obsolete. In fact even at release 8.0, the value of db_block_checkpoint_batch has no impact on the speed of checkpoint processing.

Under Oracle7 urgent checkpoints were given priority, then up to db_block_checkpoint_batch blocks were allowed for slow checkpoints, and the rest were available for other writes. But from release 8.0 onwards, the write batch composition rules are

*   up to 80% for ping writes and urgent checkpoints
*   up to 60% (of the rest) for slow checkpoints
*   the rest for dirty list and incremental checkpoint writes
These batch composition figures are the defaults. They can be tuned with _db_block_hi_priority_batch_size and the complement of _db_block_med_priority_batch_size. Both parameters may appear to have zero values if not explicitly set. To speed up checkpoint processing (if necessary) you would reduce _db_block_med_priority_batch_size from its default value of 40% to say 20%.

Seminars in Asia

Ixora is pleased to be working with Oracle to bring the Ixora seminar entitled "SGA Internals and Advanced Instance Tuning" to several locations in south-east Asia at the end of this month. The seminar will be presented by Steve Adams of Ixora in Singapore on 27 November, Taiwan on 29 November and Hong Kong on 1 December 2000. Registration is via the Oracle web site in each host country.

Practical Oracle8i: Building Efficient Databases

Jonathan Lewis's new book "Practical Oracle8i: Building Efficient Databases" can now be ordered at Amazon. If you already know Jonathan's web site and the quality of his other contributions to the Oracle community, you will be expecting great things from this book. Rest assured, you will not be disappointed. For more information, please see our review on the Ixora web site's page of Advanced Oracle Resources.

Copyright Ixora Pty Ltd Send Email Home