Re: optimizer_ ???
- From: "jaromir nemec" <jaromir@xxxxxxxxxxxx>
- To: <Christian.Antognini@xxxxxxxxxxxx>, "Peter Alteheld" <palteheld@xxxxxxxx>, "Niall Litchfield" <niall.litchfield@xxxxxxxxx>
- Date: Fri, 29 Apr 2005 00:28:27 +0200
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
----- Original Message -----
From: "Christian Antognini" <Christian.Antognini@xxxxxxxxxxxx>
To: "Peter Alteheld" <palteheld@xxxxxxxx>; "Niall Litchfield"
<niall.litchfield@xxxxxxxxx>
Cc: <oracle-l@xxxxxxxxxxxxx>
Sent: Wednesday, April 27, 2005 11:47 PM
Subject: RE: optimizer_ ???
--
//www.freelists.org/webpage/oracle-l
Other related posts:
