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