Ixora News - October 2000

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

In this issue ...

Index compression   - An 8i performance boon waiting for you.
The 8i buffer cache   - An explanation of the LRU algorithm in 8i.
SAME   - Stripe And Mirror Everything - Really?

Index compression

Do you have databases running on release 8.1? If so, have you tried the APT script uncompressed_indexes.sql? If not, let us encourage you to do so. You may well be sitting on a performance gold mine. For example:
SQL> @uncompressed_indexes

INDEX_NAME                                             MIN_COMPRESS MAX_COMPRESS
------------------------------------------------------ ------------ ------------
PROD.ORDERS_N2                                                    2            3
This suggests that the ORDERS_N2 index should be compressed. This index is a concatenated non-unique index over three columns. Under release 8.1 you have the option of leaving the index uncompressed, or compressing the first column, the first two columns, or all three columns of the index. In a compressed index, Oracle only records each distinct combination of the compressed columns once per leaf block. This saves disk space, but more importantly it save I/O during index range scans. The reduction in index size may also improve the cache hit rate. Here are the effects of compression on the ORDERS_N2 index:

  Compression    LEAF_BLOCKS  

To compress this index optimally we rebuilt it as follows:

alter index PROD.ORDERS_N2 rebuild compress 3;
In this case, index compression reduced the size of the index by 43%. That translates directly to a 43% performance gain for index range scans on this index. Gains of this order of magnitude are common with index compression.

To use the uncompressed_indexes.sql script, you should first ensure that you optimizer statistics are up to date. The script can then use those statistics to estimate the potential saving in disk blocks from index compression. For some indexes on 3 or more columns (such as the index in our example above) the script may not have sufficient information to be able to predict the optimal degree of compression. In such cases, the most simple approach is create a test table and index based on a representative sample of the production data, and then experiment with alternative degrees of index compression. The degree of compression to choose is that which results in the index with the smallest number of leaf blocks, as shown in DBA_INDEXES after the index has been reanalyzed.

The 8i buffer cache

For those still considering an upgrade to Oracle 8i the new LRU algorithm in the buffer cache is yet another inducement. The big improvement is that new buffers are no longer introduced into the DEFAULT buffer pool at the MRU end of the LRU list. Instead there is a set of "hot" buffers at the MRU end of the list and new buffers are introduced just below them. This means that hot buffers are much less likely to be aged out of the cache.

A touch count is maintained in the buffer header for each buffer. In concept the touch count represents the number of times that the buffer has been used since its LRU status was last changed. However, the touch count is not incremented at all for consistent read (CR) buffers (8.1.5 only), or for multiple uses of a current mode buffer in quick succession. Buffers with a touch count of 2 or more are eligible to be moved to the hot part of the list. They are moved there when seen by a foreground process that is scanning the LRU list for a reusable buffer, or when seen by DBWn while it is scanning the LRU list for dirty buffers.

The number of hot buffers can be seen in X$KCBWDS.HBUFS. This number is limited by the _db_percent_hot_default parameter, which defaults to 50%. When new hot buffers are moved to the hot region after that limit has been reached, then the oldest hot buffers are cooled, or pushed down into the top of the cold region.

Ideally, the hot part of the DEFAULT buffer pool should be large enough to cache the application's working set of intensively used buffers, and the cold part of the pool should be large enough to allow intensively used cooled buffers to be touched again prior to aging out. Less intensively but nevertheless frequently used buffers should be cached in the KEEP buffer pool in preference to having a large cold region in the DEFAULT buffer pool. Similarly, seldom used indexes and small tables should be cached in the RECYCLE buffer pool in preference to having a large cold region in the DEFAULT buffer pool.

By default, the KEEP and RECYCLE buffer pools do not have a hot region, but this can be changed by setting the _db_percent_hot_keep and _db_percent_hot_recycle parameters to non-zero values. Otherwise, the RECYCLE buffer pool has exactly the same LRU semantics as the DEFAULT buffer pool - it is only the name that is different - and the KEEP buffer pool differs only in that buffers read for long table scans are cached just below the hot region rather than at the LRU end and consistent read buffers are immediately frozen.

These changes to the buffer cache LRU mechanism (and others which we have not mentioned here) make the buffer cache in Oracle8i considerably more sophisticated and more tuneable than ever before. However, even without any specific tuning applications upgraded to Oracle8i will get a higher cache hit rate and reduced risk of LRU latch contention merely as a result of the upgrade.


Have you encountered the acronym, SAME? SAME applies to disk configuration and stands for Stripe And Mirror Everything. The suggestion is that if you stripe and mirror everything (except the online log files) you'll get the same performance as you would with careful planning. Do not be deceived! You may get adequate performance, but it will certainly not be best. Here's why.

I/O response time is comprised of waiting time plus service time. Service time is the time that it takes the hardware to service an I/O request. Waiting time may be needed if the hardware is busy. To optimize I/O performance you must eliminate waiting time and minimize service time.

If you adopt SAME you may well eliminate waiting time, but you will not minimize service time. Indiscriminate broad striping randomizes all I/O and makes service times uniform. But that uniform service time typically includes a seek, because there is very little locality of reference. Thus SAME is highly dependent on extensive caching and asynchronous prefetching to insulate the application from poor service times. While those facilities are available on most advanced disk arrays from EMC and other vendors, their performance is nevertheless limited by the native disk service times in the backend with the result that they fail to be effective under heavy I/O load.

The alternative to SAME is to configure multiple stripe sets of just a few disks each and to plan the allocation of segments to tablespaces and tablespaces to stripes sets with a view to preserving the physically sequential nature of logically sequential I/O where possible and separating related segments that might be subject to repeated alternating access. This minimizes services times at the expense of a marginally increased risk of waiting time, and the increased locality of reference greatly improves the effectiveness of the asynchronous prefetching.

It may be claimed that for OLTP applications the argument is almost moot because there is very little sequential I/O and almost no repeated alternating access between related segments. Granted. For OLTP applications the performance of SAME is adequate, and not much worse than optimal. But there are other considerations than just performance. Indiscriminate broad striping makes no allowance for how you might add extra disk capacity in future without either introducing a hot spot or undertaking a major disk reorganization.

So don't be deceived. SAME is growing in popularity not for its technical merits, which are few, but because it requires very little DBA skill. If you have the skill, why not use it and enjoy optimal I/O performance and maximum flexibility. Don't let vendor sales and marketing departments trick you into thinking that SAME is the same. It is not. The SAME campaign reflects more of the sales and business objectives of its sponsors than their putative commitment to technical excellence.

Copyright Ixora Pty Ltd Send Email Home