The OPTIMIZER_INDEX_COST_ADJTom Kyte in Oracle 10g (10.1.0.2) book Effective Oracle by Design, Chapter 6 Getting the Most Out of the Cost-Based Optimizer parameters described in OPTIMIZER_INDEX_COST_ADJ, and Oracle that can be understood as a plurality of execution (MultiBlock) I / O (such as a full table scan) cost and relative proportion of SINGLE-block I / O costs. TOM pointed out that the default value of this parameter is 100, and the two have the same price. If it is reduced, the CBO tends to use the index (ie, single I / O), which is tend to scan full menu (more Block I / O). Tom also gives a good example. In your own hand, I trial, the hard disk size, only one Oracle 10g (10.1.0.2), who knows that this parameter has changed at 10G. Let's first take a look at Tom's experiment: A. Create a data sheet. SQL> Drop Table T1; Table has been deleted. SQL> DROP TABLE T2; Table has been deleted. SQL> CREATE TABLE T1 2 AS 3 SELECT MOD (ROWNUM, 1000) ID, RPAD ('x', 300, 'x') Data 4 from all_Objects 5 WHERE ROWNUM <= 5000; Table has been created. SQL> ED written File Afiedt.buf 1 Create Table T2 2 AS 3 Select Rownum ID, RPAD ('x', 300, 'x') Data 4 from all_objects 5 * where rownum <= 1000 sql> / table has been created . B. Create an index and analyze. SQL> CREATE INDEX IDX_T1 ON T1 (ID); index has been created. SQL> CREATE INDEX IDX_T2 ON T2 (ID); index has been created. SQL> ed written file afiedt.buf 1 begin 2 dbms_stats.gather_table_stats 3 (user, 'T1', method_opt => 'for all indexed columns', cascade => true); 4 dbms_stats.gather_table_stats 5 (user,' T2 ', method_opt =>' for all indexed columns', cascade => true); 6 * end; SQL> / PL / SQL process has been successfully completed. C. Query the default value and set a good environment.
SQL> Set autot off sql> show parameters Optimizer_index_cost_adj; name type value -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ----------- ------------------------------ Optimizer_index_cost_adj integer 100 SQL> Set autoturonly exp STAT; D. The result of the query under default. SQL> ED has been written to file Afiedt.buf 1 Select * from T1, T2 2 WHERE T1.ID = T2.ID 3 * and T2.ID BETWEEN 50 and 55 SQL> / Have Has 30 rows.
Implementation plan ------------------------------------------------ ---------- 0 Select Statement Optimizer = Choose (COST = 13 Card = 5 Bytes = 1000) 1 0 Table Access (By INDEX ROWID) of 'T1' (COST = 2 Card = 1 Bytes = 100) 2 1 nested loops (COST = 13 card = 5 bytes = 1000) 3 2 Table access (by index rowid) of 'T2' (COST = 3 card = 5 bytes = 500) 4 3 INDEX ( Range scan) of 'idx _T2 '(index) (COST = 2 Card = 5) 5 2 INDEX (RANGE SCAN) of' IDX_T1 '(INDEX) (COST = 1 Card =
1) Statistics ------------------------------------------------------------------------------ ------------ 367 Recursive Calls 0 DB Block Gets 101 Consistent Gets 0 Physical Reads 0 Redo Size 1507 Bytes Sent Via SQL * Net To Client 523 BYtes Received Via SQL * Net from Client 3 SQL * NET Roundtrips to / from client 12 sorts (memory) 0 Sorts (Disk) 30 Rows Processed E. Modify the parameter value. SQL> ALTER session set Optimizer_index_cost_adj = 1; session has changed. SQL> ED has been written to file Afiedt.buf 1 Select * from T1, T2 2 WHERE T1.ID = T2.ID 3 * and T2.ID BETWEEN 50 and 55 SQL> / Have Has 30 rows.
Implementation plan ------------------------------------------------ ---------- 0 Select Statement Optimizer = Choose (COST = 2 Card = 5 Bytes = 1000) 1 0 Table Access (By Index RowID) of 'T1' (COST = 1 Card = 1 BYtes = 100) 2 1 nested loops (COST = 2 card = 5 bytes = 1000) 3 2 Table access (by index rot = 5) (COST = 1 card = 5 bytes = 500) 4 3 INDEX ( Range scan) of 'idx_t2' (index) (COST = 2 Card = 5) 5 2 Index (Range Scan) of 'Idx_t1' (Index) (COST = 1 Card =
1) Statistics ------------------------------------------------------------------------------ ------------ 1 Recursive Calls 0 DB Block Gets 48 Consistent Gets 0 Physical Reads 0 Redo Size 1507 Bytes Sent Via SQL * Net To Client 523 BYtes Received Via SQL * Net from Client 3 SQL * NET Roundtrips to / from client 0 sorts (memory) 0 sorts (Disk) 30 Rows Processed SQL> ALTER Session Set Optimizer_index_cost_adj = 50; session has changed. SQL> ED has been written to file Afiedt.buf 1 Select * from T1, T2 2 WHERE T1.ID = T2.ID 3 * and T2.ID BETWEEN 50 and 55 SQL> / Have Has 30 rows.
Implementation plan ------------------------------------------------ ---------- 0 Select Statement Optimizer = Choose (COST = 7 Card = 5 Bytes = 1000) 1 0 Table Access (By Index Rowid) of 'T1' (COST = 1 Card = 1 BYtes = 100) 2 1 NESTED LOOPS (COST = 7 Card = 5 Bytes = 1000) 3 2 Table Access (By Index Rowid) of 'T2' (COST = 2 Card = 5 BYtes = 500) 4 3 INDEX ( Range scan) of 'idx_t2' (index) (COST = 2 Card = 5) 5 2 Index (Range Scan) of 'Idx_t1' (Index) (COST = 1 Card =
1) Statistics ------------------------------------------------------------------------------ ------------ 1 Recursive Calls 0 DB Block Gets 48 Consistent Gets 0 Physical Reads 0 Redo Size 1507 Bytes Sent Via SQL * Net To Client 523 BYtes Received Via SQL * Net from Client 3 SQL * NET Roundtrips to / from client 0 sorts (memory) 0 sorts (Disk) 30 Rows Processed F. Continue to modify the parameter value and change it. SQL> ALTER session set Optimizer_index_cost_adj = 200; session has changed. SQL> ED has been written to file Afiedt.buf 1 Select * from T1, T2 2 WHERE T1.ID = T2.ID 3 * and T2.ID BETWEEN 50 and 55 SQL> / Have Has 30 rows.
Implementation plan ------------------------------------------------ ---------- 0 Select Statement Optimizer = Choose (COST = 26 Card = 5 Bytes = 1000) 1 0 Table Access (By Index Rowid) of 'T1' (COST = 4 Card = 1 BYtes = 100) 2 1 Nested Loops (COST = 26 Card = 5 Bytes = 1000) 3 2 Table Access (By Index Rowid) of 'T2' (COST = 6 Card = 5 Bytes = 500) 4 3 INDEX ( Range scan) of 'idx _T2 '(index) (COST = 2 Card = 5) 5 2 INDEX (RANGE SCAN) of' IDX_T1 '(INDEX) (COST = 1 Card =
1) Statistics ------------------------------------------------------------------------------ ------------ 1 Recursive Calls 0 DB Block Gets 48 Consistent Gets 0 Physical Reads 0 Redo Size 1507 Bytes Sent Via SQL * Net To Client 523 BYtes Received Via SQL * Net from Client 3 SQL * NET Roundtrips to / from client 0 sorts (memory) 0 sorts (Disk) 30 Rows Processed SQL> ALTER Session Set Optimizer_index_cost_adj = 500; session has changed. SQL> ED has been written to file Afiedt.buf 1 Select * from T1, T2 2 WHERE T1.ID = T2.ID 3 * and T2.ID BETWEEN 50 and 55 SQL> / Have Has 30 rows.
Implementation plan ------------------------------------------------ ---------- 0 Select Statement Optimizer = Choose (COST = 63 Card = 5 BYtes = 1000) 1 0 Table Access (By Index Rowid) of 'T1' (COST = 10 Car D = 1 bytes = 100) 2 1 nested loops (cost = 63 card = 5 bytes = 1000) 3 2 Table Access (full) of 't2' (COST = 13 Card = 5 by TES = 500) 4 2 INDEX Range scan) of 'idx_t 1 '(index) (COST = 1 Card =
1) Statistics ------------------------------------------------------------------------------ ------------ 1 Recursive Calls 0 DB Block Gets 90 Consistent Gets 0 Physical Reads 0 Redo Size 1507 Bytes Sent Via Sql * Net To Client 523 Bytes Received Via SQL * Net from Client 3 SQL * NET Roundtrips to / from client 0 sorts (memory) 0 sorts (Disk) 30 Rows Processed SQL> ALTER Session Set Optimizer_index_cost_adj = 1000; session has changed. SQL> ED has been written to file Afiedt.buf 1 Select * from T1, T2 2 WHERE T1.ID = T2.ID 3 * and T2.ID BETWEEN 50 and 55 SQL> / Have Has 30 rows.
Implementation plan ------------------------------------------------ ---------- 0 Select Statement Optimizer = Choose (COST = 66 Card = 5 Bytes = 1000) 1 0 Hash Join (COST = 66 Card = 5 BYtes = 1000) 2 1 Table Access (Full) of 'T2' (COST = 13 Card = 5 Byte S = 500) 3 1 Table Access (Full) of 'T1' (COST = 52 Card = 26 Byt ES =
2600) Statistics --------------------------------------------------------------------------------------------------------------------------- ------------ 1 Recursive Calls 0 DB Block Gets 271 Consistent Gets 213 Physical Reads 0 Redo Size 1651 BYtes Sent Via Sql * Net To Client 523 BYtes Received Via SQL * Net from Client 3 SQL * NET Roundtrips to / from client 0 sorts (memory) 0 Sorts (Disk) 30 Rows Processed SQL> ALTER SESSION SETOPTIMIZER_INDEX_COST_ADJ = 10000; session has changed. SQL> ED has been written to file Afiedt.buf 1 Select * from T1, T2 2 WHERE T1.ID = T2.ID 3 * and T2.ID BETWEEN 50 and 55 SQL> / Have Has 30 rows.
Implementation plan ------------------------------------------------ ---------- 0 Select Statement Optimizer = Choose (COST = 66 Card = 5 Bytes = 1000) 1 0 Hash Join (COST = 66 Card = 5 BYtes = 1000) 2 1 Table Access (Full) of 'T2' (COST = 13 Card = 5 Byte S = 500) 3 1 Table Access (Full) of 'T1' (COST = 52 Card = 26 Byt ES =
2600) Statistics --------------------------------------------------------------------------------------------------------------------------- ------------ 1 Recursive Calls 0 DB Block Gets 271 Consistent Gets 0 Physical Reads 0 Redo Size 1651 Bytes Sent Via Sql * Net To Client 523 BYtes Received Via SQL * Net from Client 3 SQL * NET Roundtrips to / from client 0 sorts (memory) 0 Sorts (Disk) 30 Rows Processed G. Today's impact on performance. SQL> SET TIMING ON SQL> ALTER session set optimizer_index_cost_adj = 100; session has changed. Time: 00: 00: 00.00 SQL> Ed 1 Select * from T1, T2 2 WHERE T1.ID = T2.ID 3 * and T2.ID BetWeen 50 and 55 SQL> / Selected 30 lines.
Time: 00: 00:00.02 Execution Plan ------------------------------------- ------------------- 0 Select Statement Optimizer = Choose (COST = 13 Card = 5 BYtes = 1000) 1 0 Table Access (By Index Rowid) of 'T1' Table) (COST = 2 card = 1 Bytes = 100) 2 1 NESTED LOOPS (COST = 13 Card = 5 Bytes = 1000) 3 2 Table Access (By Index RowID) of 'T2' (COST = 3 Card = 5 bytes = 500) 4 3 Index (Range Scan) of 'Idx_t2' (COST = 2 Card = 5) 5 2 INDEX (Range Scan) of 'Idx_t1' (COST = 1 Card =
1) Statistics ------------------------------------------------------------------------------ ------------ 0 Recursive Calls 0 DB Block Gets 48 Consistent Gets 0 Physical Reads 0 Redo Size 1507 Bytes Sent Via SQL * Net To Client 523 BYtes Received Via SQL * Net from Client 3 SQL * NET Roundtrips to / from client 0 sorts (memory) 0 sorts (Disk) 30 Rows Processed SQL> ALTER SESSION SETOPTIMIZER_INDEX_COST_ADJ = 1; the session has changed. Time: 00: 00: 00.00 SQL> Ed 1 Select * from T1, T2 2 WHERE T1.ID = T2.ID 3 * and T2.ID BetWeen 50 and 55 SQL> / Selected 30 lines.
Time: 00: 00:00.02 Execution Plan ------------------------------------- ------------------- 0 Select Statement Optimizer = Choose (COST = 2 card = 5 bytes = 1000) 1 0 Table Access (by index rowid) of 'T1' Table) (COST = 1 card = 1 Bytes = 100) 2 1 NESTED LOOPS (COST = 2 Card = 5 BYtes = 1000) 3 2 Table Access (By Index RowID) of 'T2' (COST = 1 Card = 5 bytes = 500) 4 3 index (Range scan) of 'idx_t2' (index) (COST = 2 Card = 5) 5 2 Index (Range Scan) of 'Idx_t1' (Index) (COST = 1 Card =
1) Statistics ------------------------------------------------------------------------------ ------------ 0 Recursive Calls 0 DB Block Gets 48 Consistent Gets 0 Physical Reads 0 Redo Size 1507 Bytes Sent Via SQL * Net To Client 523 BYtes Received Via SQL * Net from Client 3 SQL * NET Roundtrips to / from client 0 sorts (memory) 0 sorts (Disk) 30 Rows ProcessedSQL> ALTER session set Optimizer_index_cost_adj = 10000; session has changed. Time: 00: 00: 00.00 SQL> Ed 1 Select * from T1, T2 2 WHERE T1.ID = T2.ID 3 * and T2.ID BetWeen 50 and 55 SQL> / Selected 30 lines.
Time: 00: 00:00.03 Execution Plan ------------------------------------- ------------------- 0 Select Statement Optimizer = Choose (COST = 66 Card = 5 Bytes = 1000) 1 0 Hash Join (COST = 66 Card = 5 BYtes = 1000 2 1 Table Access (Full) of 'T2' (COST = 13 Card = 5 byte S = 500) 3 1 Table Access (Full) of 'T1' (Table) (COST = 52 Card = 26 Byt ES =
2600) Statistics --------------------------------------------------------------------------------------------------------------------------- ------------ 0 Recursive Calls 0 DB Block Gets 271 Consistent Gets 0 Physical Reads 0 Redo Size 1651 Bytes Sent Via Sql * Net To Client 523 BYtes Received Via SQL * Net from Client 3 SQL * NET Roundtrips to / from client 0 sorts (memory) 0 Sorts (Disk) 30 rows process is analyzed in detail. First, due to the difference in the test environment, TOM's test results are in the lack of value (100), it has been in the same value of 500 above, that is, the T2 full table scanning and T1 uses the index. In the TOM trial, the value is reduced until 0, and the access path becomes used to use two indexes, and the case where no index is not used. On the other hand, as TOM said, the larger value of Optimizer_Index_cost_adj, the more the optimizer is inclined to use full mete scan, the smaller the value, the more the optimizer tends to use the index. Again, we compare different points under the same access path. In the process of change from 1 to 200 (1-50-100-200), the cost calculated by the optimizer is continuously increased, and from 1000 to 10,000 are constant. This shows that this parameter is related to the cost of index I / O, and there is no relationship with the full surface scan, which is not contradictory as Tom, but it is obviously more accurate.
