RE: optimizer_ ???

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

Other related posts: