Re: Surprising Performance Changes with Oracle (Long Post)

From: Charles Hooper <>
Date: Sat, 5 Sep 2009 13:26:42 -0700 (PDT)
Message-ID: <>

On Sep 5, 12:35 pm, "Jonathan Lewis" <> wrote:
> "Charles Hooper" <> wrote in message
> news:20921d95-425c-4a2e-8442
>   CEIL(ABS(SIN(ROWNUM/9.9999)*10000)),
>   'This is the long description for this number '|| TO_CHAR(CEIL(ABS
> (SIN(ROWNUM/9.9999)*10000)))
>   FROM
>     DUAL
>     LEVEL<=10000),
>   FROM
>     DUAL
>     LEVEL<=10000);
> Charles,
> My numbers may be wrong, but I estimate that your table is
> about 6.5GB and your index is about 1.5GB, on which you do
> a range scan of 4% for a total 600MB likely to be buffered.
> When you finish the range scan versions, how much of the
> buffer cache is still free.  On one hand we might expect the
> entire table and the section of index to be buffered - leaving
> about 1GB free - on the other hand Oracle may have been
> re-using buffers for the range scan even though the number of
> free buffers was huge.  (Consider the possibility that you are
> reading into the cold half only - this probably shouldn't be
> happening after a flush buffer cache, but if it is your buffer
> cache is effectively 4GB instead of 8GB).
> A possible interpretation of the big difference in figures is as
> follows:  when doing the range scan you visit many blocks in
> the table 3 or 4 times (due to the cyclic but non-uniform nature
> of your data). If Oracle is re-using buffers instead of consuming
> free buffers all the time then you have to re-read a lot of buffers.
> When you enable direct I/O, all those reads come from disk - if
> async i/o is also enabled many of those reads might be competing
> with each other through different AIO processes.
> When you disable direct I/O you have an extra 4GB of file system
> buffer backing the Oracle buffer cache, and do far fewer real disk
> accesses.
> When you do the tablescan, you visit each block only once - when
> you do direct I/O you get readahead benefits from the hardware
> and don't waste CPU double-buffering through the file system.
> When you disable direct IO you use more CPU because of the double
> buffering - but because of the long physical reads you don't lose any extra
> time on the physical I/O.
> You might like to reboot the hardware between runs to eliminate any
> filesystem and SAN caching if you want to do a painfully rigorous test,
> of course, but I don't think it would affect my guess by much.
> --
> Regards
> Jonathan Lewis
> Author: Cost Based Oracle: Fundamentals
> The Co-operative Oracle Users' FAQ


Those are very good estimates. The table and index reside in an ASSM autoallocate tablespace. The table and index statistics looked like this during one of the test runs (either on 64 bit Windows with or 64 bit Windows with, but it sometimes changed in repeated tests):
------- -------- ------------ ---------

IND_T1        16           64         1
IND_T1        63        1,024        63
IND_T1       120        8,192       960
IND_T1        10       65,536       640
T1            16           64         1
T1            63        1,024        63
T1           120        8,192       960
T1             1       15,360        15
T1             1       22,528        22
T1             1       63,488        62
T1            82       65,536     5,248


------ -------- -------- ------------ ------------- -----------------
     3  212,992  208,854  100,000,000        10,000
900,324         90

212,992 * 8,192 = 1,744,830,464 = 1.625 GB

The table extents add up to about 6.22GB, but of course it is possible that the last 64MB extent was not fully used (this seems to be confirmed by the number of physical reads).

Unfortunately, I did not check how much free space was available in the buffer cache following the runs. The full tablescans primarily performed direct path reads which I believe would prevent the blocks from being cached in the buffer cache (this was also a bit of a surprise as I was expecting db file scattered read waits). Considering that there were 2,573,633 consistent block reads and 2,508,560 physical block reads during the test with with direct I/O and asych I/O enabled, and only 838,370 consistent block reads and 813,120 physical block reads during the full tablescan, it would seem that not many of the blocks remained in the buffer cache. On Windows, __DB_CACHE_SIZE had a value of about 1,375,731,712 at the end of the test. I did not check the value on Linux after the test run. On Windows I actually performed one other test, which essentially set the KEEP buffer cache to a very small value. This allowed the __DB_CACHE_SIZE parameter to increase from roughly 1.3GB to roughly 6.6GB which decreased the time for the full tablescan to 31.68 seconds, and the index range scan decreased to 32 minutes and 40.27 seconds.

Mladen, thanks for sharing that information.

This thread seems to fit in nicely with a couple recent blog posts on Jonathan's website: Queue Time, and Real World. However, I must say that everyone in this thread did not jump to the conclusion that when selecting 2.55% of a table, that an index should be used rather than a full tablescan.

There were, of course, several surprises: * A tablescan of a "large" table would correctly be preferred by Oracle when selecting 2.55% of the rows in the test table. * A tablescan is significantly faster in some cases than an index range scan when a small portion of the data from the table is needed. * Direct I/O and Asynch I/O, which seem to be frequently recommended to improve performance, do not always improve performance, and may in fact drastically affect performance.
* 64 bit Windows faired reasonably well with Linux when Direct I/O and Asynch I/O were enabled in Linux, when the same hardware is used for both platforms.
* Linux seemed to be a bit slower than with Direct I/ O and Asynch I/O enabled, but of course does not suffer as badly from adaptive cursor sharing problems as * Oracle used direct path reads rather than db file scattered reads during the tablescan.
* Others?

So, should the OPTIMIZER_INDEX_COST_ADJ parameter be set to the lower number to (quoting from a posting on the Internet) “immediately tune all of the SQL in your database to favor index scans over full-table scans”? :-)

Please don't spend too much time considering how to fix this test case (it was intended as a simple set up, which evolved a bit when was released and I obtained *amazing* performance improvements. There were other tests too, but I will save those for later.

Aman, thanks for the compliment.

Charles Hooper
IT Manager/Oracle DBA
K&M Machine-Fabricating, Inc. Received on Sat Sep 05 2009 - 15:26:42 CDT

Original text of this message