Re: optimizer_ ???

Hi Chris,



nice example with a precise explanation. But the most important thing, as 
you wrote..



> .. it's not a problem to show whatever you want...



So I have a small addendum to your example to illustrate the opposite side 
of the problem.



It takes not too much effort to find an select where the same change of the 
OIC has negative effect. The idea behind this is simple: assuming that index 
access cost nearly nothing doesn't scale to infinity.

Note that I simple inverted your example (small table gets large, large 
table small, index is chosen so that hash join is OK, the OIC change is 
overkill).



Of course the ratio of elapsed time is not so sharp as in your example (both 
select are not acceptable in OLTP) but in case of batch processing this 
should be taken in account.



Regards,



Jaromir D.B. Nemec



-----



SQL> CREATE TABLE t1 (col1, col2) TABLESPACE lab1 AS

  2  SELECT rownum*20, rownum FROM dual connect by 1= 1 and level <= 
2000000;



Table created.



Elapsed: 00:00:18.68

SQL> CREATE TABLE t2 (col1, col2, col3) TABLESPACE lab1 PCTFREE 90 PCTUSED

  2  10 AS

  3  SELECT mod(floor(rownum/2),25000), mod(floor(rownum/2),25000),

  4  rpad('-',50,'-')

  5  FROM dual connect by 1= 1 and level <=     160000;



Table created.



Elapsed: 00:00:22.35

SQL> --

SQL> CREATE INDEX large_i ON t2 (col1, col2) TABLESPACE lab1 COMPRESS;



Index created.



Elapsed: 00:00:26.30

SQL> --

SQL> exec dbms_stats.gather_table_stats(ownname=>user, tabname=>'T1');



PL/SQL procedure successfully completed.



Elapsed: 00:00:45.60

SQL> exec dbms_stats.gather_table_stats(ownname=>user, 
tabname=>'T2',cascade=>TRUE);



PL/SQL procedure successfully completed.



Elapsed: 00:00:21.23

SQL> ---

SQL> set autotrace on

SQL> ALTER SESSION SET OPTIMIZER_INDEX_CACHING=0;



Session altered.



Elapsed: 00:00:00.02

SQL> SELECT      count(s.col2), count(l.col1)

  2  FROM t1 s, t2 l

  3  WHERE s.col1 = l.col1(+) AND l.col2(+) = 50;



COUNT(S.COL2) COUNT(L.COL1)

------------- -------------

      2000000             0



Elapsed: 00:00:18.37



Execution Plan

----------------------------------------------------------

   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=1416 Card=1 Bytes=21

          )



   1    0   SORT (AGGREGATE)

   2    1     HASH JOIN (OUTER) (Cost=1416 Card=2000000 Bytes=42000000

          )



   3    2       TABLE ACCESS (FULL) OF 'T1' (Cost=249 Card=2000000 Byt

          es=22000000)



   4    2       INDEX (FAST FULL SCAN) OF 'LARGE_I' (NON-UNIQUE) (Cost

          =18 Card=6 Bytes=60)











Statistics

----------------------------------------------------------

          5  recursive calls

          0  db block gets

       2390  consistent gets

       4598  physical reads

          0  redo size

        447  bytes sent via SQL*Net to client

        499  bytes received via SQL*Net from client

          2  SQL*Net roundtrips to/from client

          2  sorts (memory)

          0  sorts (disk)

          1  rows processed



SQL> ALTER SESSION SET OPTIMIZER_INDEX_CACHING=90;



Session altered.



Elapsed: 00:00:00.00

SQL> SELECT      count(s.col2), count(l.col1)

  2  FROM t1 s, t2 l

  3  WHERE s.col1 = l.col1(+) AND l.col2(+) = 50;



COUNT(S.COL2) COUNT(L.COL1)

------------- -------------

      2000000             0



Elapsed: 00:00:28.02



Execution Plan

----------------------------------------------------------

   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=435 Card=1 Bytes=21)

   1    0   SORT (AGGREGATE)

   2    1     NESTED LOOPS (OUTER) (Cost=435 Card=2000000 Bytes=420000

          00)



   3    2       TABLE ACCESS (FULL) OF 'T1' (Cost=249 Card=2000000 Byt

          es=22000000)



   4    2       INDEX (RANGE SCAN) OF 'LARGE_I' (NON-UNIQUE) (Cost=1 C

          ard=1 Bytes=10)











Statistics

----------------------------------------------------------

          0  recursive calls

          0  db block gets

    2002235  consistent gets

       2199  physical reads

          0  redo size

        447  bytes sent via SQL*Net to client

        499  bytes received via SQL*Net from client

          2  SQL*Net roundtrips to/from client

          0  sorts (memory)

          0  sorts (disk)

          1  rows processed



SQL> quit;

Disconnected from Oracle9i Enterprise Edition Release 9.2.0.5.0 - Production

With the Partitioning, OLAP and Oracle Data Mining options

JServer Release 9.2.0.5.0 - Production

----- Original Message ----- 
From: "Christian Antognini" <Christian.Antognini@xxxxxxxxxxxx>
To: "Peter Alteheld" <palteheld@xxxxxxxx>; "Niall Litchfield" 
<niall.litchfield@xxxxxxxxx>
Cc: <oracle-l@xxxxxxxxxxxxx>
Sent: Wednesday, April 27, 2005 11:47 PM
Subject: RE: optimizer_ ??? 


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

Other related posts: