RE: optimizer

From: Christian Antognini <Christian.Antognini_at_trivadis.com>
Date: Tue, 26 Apr 2005 09:22:26 +0200
Message-ID: <7F0C000A3ABA6241A10C9ABF37EEB46D0406F7@MSXVS01.trivadis.com>

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);

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

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

Received on Tue Apr 26 2005 - 03:26:44 CDT

RE: optimizer_ ???