Home » RDBMS Server » Performance Tuning » Analyze table and Index usage by CBO
Analyze table and Index usage by CBO [message #64796] Tue, 03 February 2004 13:13 Go to next message
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 Go to previous message
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
Previous Topic: Query Temp Tablespace allocation problem
Next Topic: Query Optimization
Goto Forum:
  


Current Time: Tue Sep 29 10:31:53 CDT 2020