Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Mailing Lists -> Oracle-L -> RE: optimizer_ ???
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.
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
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
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-lReceived on Wed Apr 27 2005 - 17:52:11 CDT