Path: g2news2.google.com!postnews.google.com!j9g2000vbp.googlegroups.com!not-for-mail
From: Charles Hooper <hooperc2...@yahoo.com>
Newsgroups: comp.databases.oracle.server
Subject: Re: Surprising Performance Changes with Oracle 11.2.0.1 (Long Post)
Date: Sat, 5 Sep 2009 13:26:42 -0700 (PDT)
Organization: http://groups.google.com
Lines: 182
Message-ID: <15629bf2-94c4-48a1-a958-daf0ea47f6ed@j9g2000vbp.googlegroups.com>
References: <1ea99c67-8713-4ece-a0f5-85f66851b016@v2g2000vbb.googlegroups.com> 
	<stydnRbjqr7nYjzXnZ2dnUVZ8nSdnZ2d@bt.com> <20921d95-425c-4a2e-8442-cd075cef541a@q14g2000vbi.googlegroups.com> 
	<5Judncssy_PJDj_XnZ2dnUVZ8uydnZ2d@bt.com>
NNTP-Posting-Host: 205.208.133.184
Mime-Version: 1.0
Content-Type: text/plain; charset=windows-1252
Content-Transfer-Encoding: quoted-printable
X-Trace: posting.google.com 1252182402 26919 127.0.0.1 (5 Sep 2009 20:26:42 GMT)
X-Complaints-To: groups-abuse@google.com
NNTP-Posting-Date: Sat, 5 Sep 2009 20:26:42 +0000 (UTC)
Complaints-To: groups-abuse@google.com
Injection-Info: j9g2000vbp.googlegroups.com; posting-host=205.208.133.184; 
	posting-account=xVXeFwkAAAAz3xgWc6VZyjXxx1jx4jb4
User-Agent: G2/1.0
X-HTTP-UserAgent: Mozilla/4.0 (compatible; MSIE 7.0; Windows NT 6.0; WOW64; 
	Trident/4.0; SLCC1; .NET CLR 2.0.50727; Media Center PC 5.0; .NET CLR 
	3.5.21022; .NET CLR 3.5.30729; MDDC; .NET CLR 3.0.30729),gzip(gfe),gzip(gfe)

On Sep 5, 12:35=A0pm, "Jonathan Lewis" <jonat...@jlcomp.demon.co.uk>
wrote:
> "Charles Hooper" <hooperc2...@yahoo.com> wrote in message
>
> news:20921d95-425c-4a2e-8442
>
> CREATE TABLE T1 (
> =A0 ID NUMBER,
> =A0 DESCRIPTION VARCHAR2(80));
>
> INSERT INTO T1
> SELECT
> =A0 CEIL(ABS(SIN(ROWNUM/9.9999)*10000)),
> =A0 'This is the long description for this number '|| TO_CHAR(CEIL(ABS
> (SIN(ROWNUM/9.9999)*10000)))
> FROM
> =A0 (SELECT
> =A0 =A0 ROWNUM RN
> =A0 FROM
> =A0 =A0 DUAL
> =A0 CONNECT BY
> =A0 =A0 LEVEL<=3D10000),
> =A0 (SELECT
> =A0 =A0 ROWNUM RN
> =A0 FROM
> =A0 =A0 DUAL
> =A0 CONNECT BY
> =A0 =A0 LEVEL<=3D10000);
>
> 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. =A0On 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. =A0(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: =A0when 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 ext=
ra
> 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 =3D 1,744,830,464 =3D 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) =93immediately tune
all of the SQL in your database to favor index scans over full-table
scans=94?  :-)

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.