Re: Surprising Performance Changes with Oracle 11.2.0.1 (Long Post)

On Sep 5, 12:35 pm, "Jonathan Lewis" <jonat...@xxxxxxxxxxxxxxxxxx>
wrote:
"Charles Hooper" <hooperc2...@xxxxxxxxx> wrote in message

news:20921d95-425c-4a2e-8442

CREATE TABLE T1 (
  ID NUMBER,
  DESCRIPTION VARCHAR2(80));

INSERT INTO T1
SELECT
  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
  (SELECT
    ROWNUM RN
  FROM
    DUAL
  CONNECT BY
    LEVEL<=10000),
  (SELECT
    ROWNUM RN
  FROM
    DUAL
  CONNECT BY
    LEVEL<=10000);

COMMIT;

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
http://jonathanlewis.wordpress.com

Author: Cost Based Oracle: Fundamentals
http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html

The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html

Jonathan,

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
11.1.0.7 or 64 bit Windows with 10.2.0.4, but it sometimes changed in
repeated tests):
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 11.2.0.1 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 11.2.0.1 seemed to be a bit slower than 11.1.0.6 with Direct I/
O and Asynch I/O enabled, but of course 11.2.0.1 does not suffer as
badly from adaptive cursor sharing problems as 11.1.0.6.
* 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 11.2.0.1
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.
.

Relevant Pages

  • Re: RfD: BUFFER:
    ... same area of memory. ... BUFFER: permits implementers to create buffers in any available ...    256 BUFFER: Xbuff ... Note that in systems with separated code and data space, ...
    (comp.lang.forth)
  • Re: Ring buffers
    ... create buffer size cells allot ...   full? ... test get-ring. ... It may be simpler for the optimising compiler to ...
    (comp.lang.forth)
  • Re: Bidirectional streams
    ... If you open a stream to a file, write some stuff to the file and now do ...   ... File-position is not defined in terms that differe based on the ... of bytes/characters in the buffer. ...
    (comp.lang.lisp)
  • Re: render error detected, EIR: 0x00000010
    ...      Remove logical context setup. ... command is to clear the absolute buffer. ... I can ignore the error message;-) ... I am more worried about Acer machine where I am not getting XWindows. ...
    (Linux-Kernel)