| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> Re: optimizer_ ???
Hi Chris,
nice example with a precise explanation. But the most important thing, as you wrote..
> .. it's not a problem to show whatever you want...
So I have a small addendum to your example to illustrate the opposite side of the problem.
It takes not too much effort to find an select where the same change of the OIC has negative effect. The idea behind this is simple: assuming that index access cost nearly nothing doesn't scale to infinity.
Note that I simple inverted your example (small table gets large, large table small, index is chosen so that hash join is OK, the OIC change is overkill).
Of course the ratio of elapsed time is not so sharp as in your example (both select are not acceptable in OLTP) but in case of batch processing this should be taken in account.
Regards,
Jaromir D.B. Nemec
SQL> CREATE TABLE t1 (col1, col2) TABLESPACE lab1 AS
2 SELECT rownum*20, rownum FROM dual connect by 1= 1 and level <= 2000000;
Table created.
Elapsed: 00:00:18.68
SQL> CREATE TABLE t2 (col1, col2, col3) TABLESPACE lab1 PCTFREE 90 PCTUSED
2 10 AS
3 SELECT mod(floor(rownum/2),25000), mod(floor(rownum/2),25000),
4 rpad('-',50,'-')
5 FROM dual connect by 1= 1 and level <= 160000;
Table created.
Elapsed: 00:00:22.35
SQL> -- SQL> CREATE INDEX large_i ON t2 (col1, col2) TABLESPACE lab1 COMPRESS;
Index created.
Elapsed: 00:00:26.30
SQL> -- SQL> exec dbms_stats.gather_table_stats(ownname=>user, tabname=>'T1');
PL/SQL procedure successfully completed.
Elapsed: 00:00:45.60
SQL> exec dbms_stats.gather_table_stats(ownname=>user, tabname=>'T2',cascade=>TRUE);
PL/SQL procedure successfully completed.
Elapsed: 00:00:21.23
SQL> --- SQL> set autotrace on
SQL> ALTER SESSION SET OPTIMIZER_INDEX_CACHING=0; Session altered.
Elapsed: 00:00:00.02
SQL> SELECT count(s.col2), count(l.col1)
2 FROM t1 s, t2 l
3 WHERE s.col1 = l.col1(+) AND l.col2(+) = 50;
COUNT(S.COL2) COUNT(L.COL1)
2000000 0
Elapsed: 00:00:18.37
Execution Plan
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=1416 Card=1 Bytes=21
)
1 0 SORT (AGGREGATE) 2 1 HASH JOIN (OUTER) (Cost=1416 Card=2000000 Bytes=42000000
)
3 2 TABLE ACCESS (FULL) OF 'T1' (Cost=249 Card=2000000 Byt
es=22000000)
4 2 INDEX (FAST FULL SCAN) OF 'LARGE_I' (NON-UNIQUE) (Cost
=18 Card=6 Bytes=60)
Statistics
5 recursive calls
0 db block gets
2390 consistent gets
4598 physical reads
0 redo size
447 bytes sent via SQL*Net to client
499 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
1 rows processed
SQL> ALTER SESSION SET OPTIMIZER_INDEX_CACHING=90; Session altered.
Elapsed: 00:00:00.00
SQL> SELECT count(s.col2), count(l.col1)
2 FROM t1 s, t2 l
3 WHERE s.col1 = l.col1(+) AND l.col2(+) = 50;
COUNT(S.COL2) COUNT(L.COL1)
2000000 0
Elapsed: 00:00:28.02
Execution Plan
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=435 Card=1 Bytes=21)
1 0 SORT (AGGREGATE) 2 1 NESTED LOOPS (OUTER) (Cost=435 Card=2000000 Bytes=420000
00)
3 2 TABLE ACCESS (FULL) OF 'T1' (Cost=249 Card=2000000 Byt
es=22000000)
4 2 INDEX (RANGE SCAN) OF 'LARGE_I' (NON-UNIQUE) (Cost=1 C
ard=1 Bytes=10)
Statistics
0 recursive calls
0 db block gets
2002235 consistent gets
2199 physical reads
0 redo size
447 bytes sent via SQL*Net to client
499 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL> quit;
Disconnected from Oracle9i Enterprise Edition Release 9.2.0.5.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.5.0 - Production
-- http://www.freelists.org/webpage/oracle-lReceived on Thu Apr 28 2005 - 18:38:29 CDT
 |
 |