Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> RE: optimizer_ ???

RE: optimizer_ ???

From: Christian Antognini <Christian.Antognini_at_trivadis.com>
Date: Wed, 27 Apr 2005 23:47:42 +0200
Message-ID: <7F0C000A3ABA6241A10C9ABF37EEB46D040749@MSXVS01.trivadis.com>


HI Peter, Niall

>Sure. What I, and maybe Niall too, am looking for are
>examples that show response time reduction by changing=20
>of execution plan due to the tweaking of oic or oica=20
>(and maybe even dfmrc) from default values to those=20
>which should be optimal.

As I wrote you in my second reply, it's not a problem to show whatever = you want... Therefore here an example with OIC only.

  1. Setup (create two tables: SMALL and LARGE)

CREATE TABLE small (col1, col2) TABLESPACE test AS=20 SELECT rownum*20, rownum FROM dba_objects WHERE rownum <=3D 97;

CREATE TABLE large (col1, col2, col3) TABLESPACE test PCTFREE 90 PCTUSED = 10 AS=20
SELECT mod(floor(rownum/2),2500), mod(floor(rownum/2),250), = rpad('-',50,'-')
FROM dba_objects WHERE rownum <=3D 10000;

INSERT INTO large SELECT * FROM large;
INSERT INTO large SELECT * FROM large;
INSERT INTO large SELECT * FROM large;
INSERT INTO large SELECT * FROM large;

CREATE INDEX large_i ON large (col1, col2) TABLESPACE test COMPRESS;

exec dbms_stats.gather_table_stats(ownname=3D>user, tabname=3D>'SMALL') exec dbms_stats.gather_table_stats(ownname=3D>user, tabname=3D>'LARGE', = cascade=3D>TRUE)

2) With OIC=3D0 the CBO chooses a hash join and lot of PIO are done = (even if I execute the statement many times the number of PIO doesn't = change). Note that the elapsed time is 14 seconds.

SQL> ALTER SESSION SET OPTIMIZER_INDEX_CACHING=3D0; SQL> SELECT count(s.col2), count(l.col3)=20   2 FROM small s, large l=20
  3 WHERE s.col1 =3D l.col1(+) AND l.col2(+) =3D 50;

COUNT(S.COL2) COUNT(L.COL3)
------------- -------------

          349 256

Elapsed: 00:00:14.06

Execution Plan



SELECT STATEMENT Optimizer=3DALL_ROWS (Cost=3D3628 Card=3D1 Bytes=3D64)   SORT (AGGREGATE)
    HASH JOIN (OUTER) (Cost=3D3628 Card=3D110 Bytes=3D7040)       TABLE ACCESS (FULL) OF 'SMALL' (TABLE) (Cost=3D2 Card=3D97 = Bytes=3D582)

      TABLE ACCESS (FULL) OF 'LARGE' (TABLE) (Cost=3D3626 Card=3D637 = Bytes=3D36946)

Statistics


          1  recursive calls
          0  db block gets
      13350  consistent gets
      13160  physical reads
          0  redo size
        370  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


3) With OIC=3D90 (the silver bullet!) the CBO chooses a nested loop and = no PIO are done (remember, the idea of OIC is to say that during a = nested loop the blocks are cached...). Note that the elapsed time is "0" = seconds!

SQL> ALTER SESSION SET OPTIMIZER_INDEX_CACHING=3D90; SQL> SELECT count(s.col2), count(l.col3)=20   2 FROM small s, large l=20
  3 WHERE s.col1 =3D l.col1(+) AND l.col2(+) =3D 50;

COUNT(S.COL2) COUNT(L.COL3)
------------- -------------

          349 256

Elapsed: 00:00:00.00

Execution Plan



SELECT STATEMENT Optimizer=3DALL_ROWS (Cost=3D3594 Card=3D1 Bytes=3D64)   SORT (AGGREGATE)
    NESTED LOOPS (OUTER) (Cost=3D3594 Card=3D110 Bytes=3D7040)       TABLE ACCESS (FULL) OF 'SMALL' (TABLE) (Cost=3D2 Card=3D97 = Bytes=3D582)

      TABLE ACCESS (BY INDEX ROWID) OF 'LARGE' (TABLE) (Cost=3D37 = Card=3D1 Bytes=3D58)

        INDEX (RANGE SCAN) OF 'LARGE_I' (INDEX) (Cost=3D0 Card=3D1)

Statistics


          1  recursive calls
          0  db block gets
        273  consistent gets
          0  physical reads
          0  redo size
        370  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

>How would you approach that? Is it experience, intuition=20
>or do you have any rules for finding such examples?

Experience and intuition always help, but the most important thing, = IMHO, is to understand how the CBO works and therefore how the different = parameters influence the CBO.=20

For the example above I asked myself:

After these thoughts...
- I created a small table with 50 rows and a large table with 80000 = rows.=20
- With default OIC I get the costs of the two execution plans I was = looking for (I forced them with hints).
- I adjusted the size of the tables and selectivity of the predicates to = have a cost for the hash join that was just higher than the cost for the = nested loop.
- Then by setting OIC to 90 the switch between hash join and nested loop = was produced...

HTH
Chris

--
http://www.freelists.org/webpage/oracle-l
Received on Wed Apr 27 2005 - 17:52:11 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US