| 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 |
How much memory does each Oracle process reserve for sorting?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.
A. sort_area_size
B. sort_area_retained_size
C. sort_area_size + sort_area_retained_size
D. none
E. none of the above
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.
DISK_SORTS AVERAGE_SIZE PEAK_CONCURRENT
---------- ------------ ---------------
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.)
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.
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 checkpointsThese 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%.
* up to 60% (of the rest) for slow checkpoints
* the rest for dirty list and incremental checkpoint writes
| Copyright © Ixora Pty Ltd |
|