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.

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

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

Received on Wed Apr 27 2005 - 17:52:11 CDT

RE: optimizer_ ???