Re: Surprising Performance Changes with Oracle 126.96.36.199 (Long Post)
Date: Sat, 5 Sep 2009 13:26:42 -0700 (PDT)
On Sep 5, 12:35 pm, "Jonathan Lewis" <jonat..._at_jlcomp.demon.co.uk> wrote:
> "Charles Hooper" <hooperc2..._at_yahoo.com> wrote in message
> CREATE TABLE T1 (
> ID NUMBER,
> DESCRIPTION VARCHAR2(80));
> INSERT INTO T1
> 'This is the long description for this number '|| TO_CHAR(CEIL(ABS
> ROWNUM RN
> CONNECT BY
> ROWNUM RN
> CONNECT BY
> 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
> 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.
> 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
188.8.131.52 or 64 bit Windows with 10.2.0.4, but it sometimes changed in
SEGMENT EXTENTS EXT_SIZE_KB TOTAL_MB
------- -------- ------------ ---------
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
DBA_INDEXES reported the CLUSTERING_FACTOR as 101,149,320. INDEX_STATS reported the following with an 8KB block size: HEIGHT BLOCKS LF_BLKS LF_ROWS DISTINCT_KEYS MOST_REPEATED_KEY PCT_USED
------ -------- -------- ------------ ------------- ----------------- ---------- 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 184.108.40.206 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 220.127.116.11 seemed to be a bit slower than 18.104.22.168 with Direct I/ O and Asynch I/O enabled, but of course 22.214.171.124 does not suffer as badly from adaptive cursor sharing problems as 126.96.36.199. * Oracle used direct path reads rather than db file scattered reads during the tablescan.
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 188.8.131.52 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.
IT Manager/Oracle DBA
K&M Machine-Fabricating, Inc. Received on Sat Sep 05 2009 - 15:26:42 CDT