RE: optimizer_ ???

  • From: "Christian Antognini" <Christian.Antognini@xxxxxxxxxxxx>
  • To: "Peter Alteheld" <palteheld@xxxxxxxx>, "Niall Litchfield" <niall.litchfield@xxxxxxxxx>
  • Date: Wed, 27 Apr 2005 23:47:42 +0200

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:

- How does OIC influence the CBO? Makes the cost of the inner loop of =
the nested loop cheaper.

- What I want to show? Execution plan switch by tweaking OIC, i.e. from =
hash join to nested loop. Since with a nested loop more indexes can be =
used, I want 1) two full scans for the hash join 2) an index scan based =
on the join key for the nested loop (the hash join cannot use such an =
index!).

- How many rows should return the outer loop? Neither few nor many. In =
fact 1) when the outer loop returns lot of rows the nested loop cost =
will be very high and therefore very slow compared to the hash join 2) =
when the outer loop returns few rows the impact of OIC is very small =
and, in addition, the nested loop is usually superior.

- How many rows should process the inner loop? Via the index on the join =
key few rows should be returned otherwise the impact of the table access =
(clustering factor) will be too high. But, for the hash join it should =
be quite expensive to find out these rows, i.e. the table should be =
quite large.

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



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

Other related posts: