RE: optimizer_ ???
- From: "Christian Antognini" <Christian.Antognini@xxxxxxxxxxxx>
- To: "Niall Litchfield" <niall.litchfield@xxxxxxxxx>
- Date: Tue, 26 Apr 2005 16:18:06 +0200
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
--
//www.freelists.org/webpage/oracle-l
Other related posts:
