RE: optimizer

From: Christian Antognini <Christian.Antognini_at_trivadis.com>
Date: Tue, 26 Apr 2005 16:18:06 +0200
Message-ID: <7F0C000A3ABA6241A10C9ABF37EEB46D04070A@MSXVS01.trivadis.com>

Hi Niall

>> How huge can the impact on the response time be by tweaking oic?=20
>> Precisely: do you know of an example where you gain a response=20
>> time reduction which is very impressive?
>
>I had cause to Google this just recently and I can't find any examples
>of changes to response time.

It's really easy to build such an example... here a very simple one...

SQL> set timing on
SQL> set autotrace trace stat exp

SQL> ALTER SESSION SET OPTIMIZER_INDEX_CACHING=3D0; SQL> SELECT count(*)
2 FROM t t1, t t2
3 WHERE t1.id =3D t2.col1(+) AND t2.col1(+) > 900;

Elapsed: 00:00:00.07

Execution Plan

SELECT STATEMENT Optimizer=3DALL_ROWS (Cost=3D1022 Card=3D1 Bytes=3D9) SORT (AGGREGATE)
HASH JOIN (OUTER) (Cost=3D1022 Card=3D51993 Bytes=3D467937) TABLE ACCESS (FULL) OF 'T' (Cost=3D954 Card=3D51993 = Bytes=3D259965)

INDEX (RANGE SCAN) OF 'I1' (NON-UNIQUE) (Cost=3D13 Card=3D5152 = Bytes=3D20608)

Statistics

          0  recursive calls
          0  db block gets
      17405  consistent gets
      13835  physical reads
          0  redo size
        305  bytes sent via SQL*Net to client
        495  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL> ALTER SESSION SET OPTIMIZER_INDEX_CACHING=3D90; SQL> SELECT count(*)
2 FROM t t1, t t2
3 WHERE t1.id =3D t2.col1(+) AND t2.col1(+) > 900;

Elapsed: 00:00:01.05

Execution Plan

SELECT STATEMENT Optimizer=3DALL_ROWS (Cost=3D959 Card=3D1 Bytes=3D9) SORT (AGGREGATE)
NESTED LOOPS (OUTER) (Cost=3D959 Card=3D51993 Bytes=3D467937) TABLE ACCESS (FULL) OF 'T' (Cost=3D954 Card=3D51993 = Bytes=3D259965)

INDEX (RANGE SCAN) OF 'I1' (NON-UNIQUE) (Cost=3D1 Card=3D1 = Bytes=3D4)

Statistics

          0  recursive calls
          0  db block gets
     227517  consistent gets
      13445  physical reads
          0  redo size
        305  bytes sent via SQL*Net to client
        495  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

As you can see by switching OIC from its default value to 90, which his = one of my preferred silver bullets ;-), the query is much slower = (factors, not percents...).

Of course with a higher number of joined tables and more data the = differences could be much much higher!

HTH
Chris

--
http://www.freelists.org/webpage/oracle-l

Received on Tue Apr 26 2005 - 10:22:44 CDT

RE: optimizer_ ???