Optimizer of parameters

The parameter Optimizer_index_cost_adj sets the cost-consumption relationship between the index scan and the full table scan, with a default value of 100, which means that Oracle calculates the query cost at the same cost as the index lookup and full table scan. The smaller the parameter value, the smaller the cost of searching through the index, and the greater the converse.

Use the following test to demonstrate the usage of this parameter.

1 View the current value of the parameter Optimizer_index_cost_adj


Sql> Conn/as SYSDBA









Connected.











Sql> Show Parameter Optimizer_index_cost_adj



















NAME TYPE VALUE

------------------------------------ ----------- ---------

Optimizer_index_cost_adj Integer 100

--the current default value for the parameter is 100



Sql>













Sql> Select Isses_modifiable,issys_modifiable



2 from V$parameter









3 where name= ' Optimizer_index_cost_adj ';



















Isses Issys_mod











----- ---------











TRUE FALSE

--Indicates that the parameter can be dynamically changed at the session level.

But cannot be changed dynamically at the system level











2 Create tables and indexes, and analyze the tables

Sql> Conn Scott/tiger







Connected.









Sql> CREATE TABLE Test (a number,b char (10));















Table created.





















Sql>











Sql> begin









2 for I in 1..10000 loop





3 INSERT INTO Test values (I,to_char (i));

4 End Loop;









5 commit;









6 end;









7/























Pl/sql procedure successfully completed.















Sql>











Sql> CREATE index idx_test_a on test (a);















Index created.





















Sql>











sql> Analyze table Test COMPUTE statistics



2 for Table









3 for all indexes







4 for all indexed columns;

















Table analyzed.





















Sql>












3 Set the parameter Optimizer_index_cost_adj to 100 to see the corresponding execution plan

Sql> Set Autotrace traceonly explain







Sql> alter session set OPTIMIZER_INDEX_COST_ADJ=100;





Session altered.





























Sql> SELECT * from Test where a=1;









Execution Plan













----------------------------------------------------------



0 SELECT STATEMENT optimizer=choose (cost=2 card=1 bytes=15)

1 0 TABLE ACCESS (by INDEX ROWID) of ' TEST ' (cost=2 card=1 byt

ES=15)





























2 1 INDEX (RANGE SCAN) of ' idx_test_a ' (non-unique) (cost=1

card=1)














In the above section, Oracle uses the correct lookup method, using indexes to find data.
The following shows how to get Oracle to look up data in a full table scan, although we know this is not a very efficient approach.

4 Change the parameter Optimizer_index_cost_adj to 1000, change Oracle's decision process

Sql> alter session set optimizer_index_cost_adj=1000;





















Session altered.





























Sql> SELECT * from Test where a=1;

























Execution Plan













----------------------------------------------------------



0 SELECT STATEMENT optimizer=choose (cost=4 card=1 bytes=15)

1 0 TABLE ACCESS (full) ' TEST ' (cost=4 card=1 bytes=15)

In the above demo, by changing the OPTIMIZER_INDEX_COST_ADJ parameter, Oracle uses a full table scan to perform the same query.

5) Summary
In an OLTP system, it is possible to consider setting the Optimizer_index_cost_adj parameter values small, so that the system tends to use the index, in the DSS system, it is possible to consider adjusting the parameters appropriately to influence the Oracle decision process.