Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> RE: optimizer_ ???

RE: optimizer_ ???

From: Peter Alteheld <palteheld_at_yahoo.de>
Date: Tue, 26 Apr 2005 10:37:25 +0200 (CEST)
Message-ID: <20050426083725.24260.qmail@web26505.mail.ukl.yahoo.com>


Hi Chris  

thank you very much for clearing this point so prompt. I haven't expected such a detailed example - thank you!  

How huge can the impact on the response time be by tweaking oic? Precisely: do you know of an example where you gain a response time reduction which is very impressive?  

Peter
Christian Antognini <Christian.Antognini_at_trivadis.com> wrote: Hi Peter

>on page 30 in the pdf you state that optimizer_index_caching
>has no influence on index range scans. Then why does the formula
>for the index range scan I/O cost -just two lines below that=20
>statement contain the oic paramter?

You are right; the explanation is not very good. I should really change = it...

Let show you the demo I do at this moment:

0) Test environment...

CREATE TABLE t (id, col1, col2) PCTFREE 80 PCTUSED 20 AS=20 SELECT rownum, mod(floor(rownum/2),1000), rpad('-',50,'-') FROM dba_objects WHERE rownum <=3D 10000;

CREATE INDEX i1 ON t (col1);

exec dbms_stats.gather_table_stats(ownname=3D>user, tabname=3D>'T', = cascade=3D>TRUE);

  1. Execute a simple select containing a range scan with OIC=3D0 and = OIC=3D100.=20
SQL> ALTER SESSION SET OPTIMIZER_INDEX_CACHING=3D0; SQL> SELECT * FROM t WHERE col1 =3D 11;

Execution Plan



SELECT STATEMENT Optimizer=3DALL_ROWS (Cost=3D7 Card=3D10 Bytes=3D580) TABLE ACCESS (BY INDEX ROWID) OF 'T' (Cost=3D7 Card=3D10 Bytes=3D580) INDEX (RANGE SCAN) OF 'I1' (NON-UNIQUE) (Cost=3D1 Card=3D10)

SQL> ALTER SESSION SET OPTIMIZER_INDEX_CACHING=3D100; SQL> SELECT * FROM t WHERE col1 =3D 11;

Execution Plan



SELECT STATEMENT Optimizer=3DALL_ROWS (Cost=3D7 Card=3D10 Bytes=3D580) TABLE ACCESS (BY INDEX ROWID) OF 'T' (Cost=3D7 Card=3D10 Bytes=3D580) INDEX (RANGE SCAN) OF 'I1' (NON-UNIQUE) (Cost=3D1 Card=3D10)

As you can see the costs are exactly the same, i.e. OIC has no impact on = simple range scans!

2) Execute a select containing a range scan in the inner loop of a = nested loop with OIC=3D0 and OIC=3D100.=20

SQL> ALTER SESSION SET OPTIMIZER_INDEX_CACHING=3D0; SQL> SELECT /*+ use_nl(t1 t2) */ * FROM t t1, t t2 WHERE t1.id =3D = t2.col1;

Execution Plan



SELECT STATEMENT Optimizer=3DCHOOSE (Cost=3D60061 Card=3D10000 = Bytes=3D1160000
NESTED LOOPS (Cost=3D60061 Card=3D10000 Bytes=3D1160000)
TABLE ACCESS (FULL) OF 'T' (Cost=3D61 Card=3D10000 Bytes=3D580000)
TABLE ACCESS (BY INDEX ROWID) OF 'T' (Cost=3D6 Card=3D10000 =
Bytes=3D5800
INDEX (RANGE SCAN) OF 'I1' (NON-UNIQUE) (Cost=3D1 Card=3D10000)

SQL> ALTER SESSION SET OPTIMIZER_INDEX_CACHING=3D100; SQL> SELECT /*+ use_nl(t1 t2) */ * FROM t t1, t t2 WHERE t1.id =3D = t2.col1;

Execution Plan



SELECT STATEMENT Optimizer=3DCHOOSE (Cost=3D50061 Card=3D10000 = Bytes=3D1160000
NESTED LOOPS (Cost=3D50061 Card=3D10000 Bytes=3D1160000)
TABLE ACCESS (FULL) OF 'T' (Cost=3D61 Card=3D10000 Bytes=3D580000)
TABLE ACCESS (BY INDEX ROWID) OF 'T' (Cost=3D5 Card=3D10000 =
Bytes=3D5800
INDEX (RANGE SCAN) OF 'I1' (NON-UNIQUE) As you can see the costs are different. In the second execution plan = OIC=3D100, therefore the costs of the index access are 0 (the costs are = missing in the execution plan).

3) Let's do some mathematics...

SQL> show parameter db_file_multiblock_read_count

NAME TYPE VALUE

------------------------------------ ------- =
------------------------------

db_file_multiblock_read_count integer 8

SQL> SELECT blocks FROM user_tables WHERE table_name =3D 'T';

BLOCKS



400

SQL> SELECT blevel, leaf_blocks, clustering_factor, 2 1/distinct_keys selectivity
3 FROM user_indexes
4 WHERE index_name =3D 'I1';

BLEVEL LEAF_BLOCKS CLUSTERING_FACTOR SELECTIVITY

---------- ----------- ----------------- -----------
1 21 5202 .001

Cost of the full table scan (outer loop of the nested loop):

400 / (1.6765*8^0.6581) =3D 60.72 (rounded to 61)

Cost of the table access via index range scan (inner loop of the nested = loop):

OIC=3D0 =3D=3D> (1+21*0.001)*(1-0/100)+5202*0.001 =3D 6.223 (rounded = to 6)
OIC=3D100 =3D=3D> (1+21*0.001)*(1-100/100)+5202*0.001 =3D 5.202 (rounded = to 5)

Cost of the nested loop:

OIC=3D0 =3D=3D> 61 + 10000 * 6 =3D 60061 OIC=3D100 =3D=3D> 61 + 10000 * 5 =3D 50061 OK?

Have fun,
Chris

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

		
---------------------------------
Gesendet von Yahoo! Mail - Jetzt mit 250MB kostenlosem Speicher

--
http://www.freelists.org/webpage/oracle-l
Received on Tue Apr 26 2005 - 04:41:42 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US