| Analyze table and Index usage by CBO [message #64796] |
Tue, 03 February 2004 13:13  |
Venkatramanan
Messages: 10
Registered: October 2000
|
Junior Member |
|
|
Friends,
I'm facing a strange problem. When I execute analyze table
delete statistics, the CBO seems to be taking the best access path and when I issue compute statistics, it is not using indexes properly and as a result my complex UPDATE statement runs indefinitely. It takes only 3 minutes when the index usage is correct.
What I don't understand is that the CBO has to pick the correct access path when there are enough statistics present, but it is happening vice-versa in my case.
Pl. help..
Venkat
|
|
|
|
| Re: Analyze table and Index usage by CBO [message #64797 is a reply to message #64796] |
Tue, 03 February 2004 15:28  |
Thiru
Messages: 1089
Registered: May 2002
|
Senior Member |
|
|
Hi,
when your optimizer_mode=choose and you dont have any statistics on the database objects,Rule BAsed optimizer is used to device the execution plan instead of CBO and RBO loves indexes. Which version of Oracle are you using ?
If on 9i,you can adjust the following paramaters to influence the CBO
OPTIMIZER_INDEX_CACHING
OPTIMIZER_INDEX_COST_ADJ
Optimizer_Index_caching tells the CBO to assume so much Index is cached in memory,making Index scans via Nested loops look cheaper and hence choose to use the Index instad of Full table scan or other join methods like sort merge.Default is 0. For eg,if you set it to 80 , you are telling CBO that 80% of your indexes are in the buffer cache which could tilt the decision in favor of Nested loops.
Optimizer_Index_Cost_Adj defaults to 100% which means the index scan is evaluated at the regular cost. Setting this to 60 ,for example,makes the CBO evaluate the cost of the index scan at 60% of the regular cost and makes it index friendlier.
Also if DB_FILE_MULTIBLOCK_READ_COUNT is high( 32 & above),it makes full table scans cheaper and CBO may decide to ignore the indexes.
To CBO, full table scans are not evil and Index scans are not all that good. By default,CBO tries to optimize for throughput (all_rows) and not response( first_rows). And all_rows tries to favour Full table scans/Hash Joins and Sort Merge joins and First_rows tends to favour Nested_loops over the other join methods.
there are other factors too,but this should get you going.
-Thiru
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
