Optimizer_index_cost_adj This initialization parameter represents a percentage, the value ranges between 1 and 10,000. This parameter represents a comparison of index scanning and full mete scanning costs. The default value 100 represents an index scan into an equivalent conversion and a full mete scan cost.
These parameters have a significant impact on the implementation of CBO, and the default value is usually adjusted for the database. Generally speaking to Optimizer_Index_caching can be set to 90 or so for most OLTP systems, Optimizer_index_cost_adj can be set between 10 and 50. For data warehouses and DSS systems, it may not be simple to set Optimizer_Index_cost_adj to 50, usually we need to repeatedly adjust a reasonable value. More specific can be calculated based on statistics, DB File Scattered Reads / DB File Sequential Reads.
This paper explores and illustrates the use of this parameter by experiment.
We see the default value of Optimizer_Index_cost_adj.
[Oracle @ Jumper Udump] $ SQLPLUS EYGLE / EYGLE
SQL * Plus: Release 9.2.0.3.0 - Production on Mon Jun 28 17:11:15 2004
CopyRight (C) 1982, 2002, Oracle Corporation. All Rights Reserved.
Connected to: Oracle9i Enterprise Edition Release 9.2.0.3.0 - Productionwith The Partitioning, OLAP AND ORACLE DATA MINING OPTIONSJSERVER Release 9.2.0.3.0 - Production
SQL> Show parameter Optimizer_index_cost_adj
Name Type Value --------------------------------- ------------------------------ Optimizer_index_cost_adj integer 100sql>
Create a test table:
SQL> CREATE TABLE T AS SELECT * FROM DBA_OBJECTS;
Table created.
SQL> CREATE INDEX IND_OWNER ON T (OWNER);
Index created.
SQL> Analyze Table T Compute Statistics;
Table analyzed.
We observe the cost of full table scanning and index visits:
SQL> SET Autotrace TraceonlySql> Select * from T where Owner = 'Eygle'; Execution Plan ------------------------------- --------------------------- 0 Select Statement Optimizer = Choose (COST = 14 card = 476 bytes = 36652) 1 0 Table Access (Full ) Of 't' (COST = 14 card = 476 bytes = 36652) SQL> SELECT / * INDEX (T Ind_owner) * / * from T where = 'Eygle'; Execution Plan --------- ------------------------------------------------- 0 Select Statement Optimizer = Choose (COST = 16 Card = 476 BYtes = 36652) 1 0 Table Access (By Index Rowid) of 'T' (COST = 16 Card = 476 BYtes = 36652) 2 1 Index (Range Scan) of 'Ind_owner '(Non-Unique) (COST = 2 Card = 476) Oracle When selecting a different access path, a full-metric scan and index scan are comparative.
When compared, Oracle converts the cost of the index scan into the cost of full mete scans, compares the COST scanned by the full surface. This conversion requires a conversion factor. Just Optimizer_index_cost_adj:
Optimizer_index_cost_adj * (Index Scan Cost) = Equivalent of Full Scan Cost
This equivalent Full Scan Cost is coming to compare with full-tunition scanning costs.
The critical value of this conversion factor is actually the ratio of Full Scan Cost and Index Scan Cost.
which is:
Optimizer_index_cost_adj = Full Scan Cost / INDEX SCAN COST
SQL> Set Autotrace Offsql> SELECT (14/16) * 100 from DUAL
(14/16) * 100 ----------- 87.5
1 row selected.
We look at the performance of the program by adjusting Optimizer_Index_COST_ADJ:
SQL> Set Autotrace Traceonly
SQL> ALTER session set optimizer_index_cost_adj = 87;
Session altered.
SQL> SELECT * from T where = 'Eygle'
Execution Plan ------------------------------------------------ ---------- 0 Select Statement Optimizer = Choose (COST = 14 Card = 476 BYtes = 36652) 1 0 Table Access (by Index Rowid) of 'T' (COST = 14 Card = 476 BYtes = 36652 2 1 Index (Range Scan) of 'Ind_owner' (COST = 2 Card = 476) At this point the index cost is low. The equivalent full metric scanning cost is:
87% * (Index Scan Cost) At this point Oracle selected an index. SQL> ALTER session set optimizer_index_cost_adj = 88; Session altered. SQL> SELECT * from T where = 'Eygle' Execution Plan ------------------------------------------------ ---------- 0 Select Statement Optimizer = Choose (COST = 14 Card = 476 BYtes = 36652) 1 0 Table Access (Full) of 'T' (COST = 14 Card = 476 BYtes = 36652) At this time, the use of index costs is high. The equivalent full mete scan cost is: 88% * (INDEX SCAN COST)> Full Scan Cost So Oracle chose full table scan. Reference documentation: http://www.evdbt.com/searchintelligencecbo.doc Monday, June 28, 2004 17:03 If You Have Any Question, please mail to eye@itpub.net.
