RE: optimizer_ ???

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

Other related posts: