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

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Sat, 5 Sep 2009 17:35:35 +0100
Message-ID: <5Judncssy_PJDj_XnZ2dnUVZ8uydnZ2d_at_bt.com>

"Charles Hooper" <hooperc2000_at_yahoo.com> 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; CREATE INDEX IND_T1 ON T1(ID); EXEC DBMS_STATS.GATHER_TABLE_STATS
(OWNNAME=>USER,TABNAME=>'T1',CASCADE=>TRUE, METHOD_OPT=>'FOR ALL
COLUMNS SIZE 1') ANALYZE INDEX IND_T1 VALIDATE STRUCTURE; Test script:
set linesize 150
set pagesize 2000
ALTER SYSTEM FLUSH BUFFER_CACHE;
ALTER SYSTEM FLUSH SHARED_POOL;
SET ARRAYSIZE 100 spool /u01/app/oracle/results/BCHRTestShortNoAsync.txt

SET AUTOTRACE TRACEONLY EXPLAIN
ALTER SESSION SET OPTIMIZER_INDEX_COST_ADJ=100; SELECT
  ID,
  DESCRIPTION
FROM
  T1
WHERE
  ID BETWEEN 1 AND 400; ALTER SESSION SET OPTIMIZER_INDEX_COST_ADJ=5; SELECT
  ID,
  DESCRIPTION
FROM
  T1
WHERE
  ID BETWEEN 1 AND 400; SET ARRAYSIZE 100
SET AUTOTRACE TRACEONLY STATISTICS SET TIMING ON ALTER SYSTEM FLUSH BUFFER_CACHE;
ALTER SYSTEM FLUSH BUFFER_CACHE;
ALTER SESSION SET TRACEFILE_IDENTIFIER='IND_COST_LARGE100'; ALTER SESSION SET EVENTS '10046 TRACE NAME CONTEXT FOREVER, LEVEL 12'; ALTER SESSION SET OPTIMIZER_INDEX_COST_ADJ=100; SELECT
  ID,
  DESCRIPTION
FROM
  T1
WHERE
  ID BETWEEN 1 AND 400; ALTER SYSTEM FLUSH BUFFER_CACHE;
ALTER SYSTEM FLUSH BUFFER_CACHE;
ALTER SESSION SET TRACEFILE_IDENTIFIER='IND_COST_LARGE5'; ALTER SESSION SET EVENTS '10046 TRACE NAME CONTEXT FOREVER, LEVEL 12'; ALTER SESSION SET OPTIMIZER_INDEX_COST_ADJ=5; SELECT
  ID,
  DESCRIPTION
FROM
  T1
WHERE
  ID BETWEEN 1 AND 400; spool off

Charles Hooper
IT Manager/Oracle DBA
K&M Machine-Fabricating, Inc.

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

Received on Sat Sep 05 2009 - 11:35:35 CDT