Home » RDBMS Server » Performance Tuning » Cost-based optimization slows down my query
Cost-based optimization slows down my query [message #65093] Wed, 28 April 2004 09:07 Go to next message
David Peters
Messages: 6
Registered: April 2004
Junior Member
Under what circumstances would a cost-based optimizer choose to ignore an index and do a full table scan instead?

I have the cost-based optimizer set on a query, and the system is choosing to run a full table scan on an indexed column of my table. 

All of the indexes and columns are analyzed, as seen in my explain plan, and the costs are present.

If I set the /*+ index */ hint, it uses the index.  If I set my session to OPTIMIZER_GOAL = RULE it uses my index.

 

 

 

 

 

 
Re: Cost-based optimization slows down my query [message #65096 is a reply to message #65093] Wed, 28 April 2004 23:06 Go to previous messageGo to next message
Daljit Singh
Messages: 290
Registered: October 2003
Location: Texas
Senior Member
Hi,

In case of cost based optimizer FTS doesn't mean that ur query is slow. Even if ur tables are analyzed check the cardinality of the columns in where clause and the amount of data in table.

The best and important way is to create a histogram on the columns which are in where clause.

If after that also u see FTS in plan so its mean that FTS is better than using indexes.

Daljit Singh.
Re: Cost-based optimization slows down my query [message #65102 is a reply to message #65096] Thu, 29 April 2004 05:11 Go to previous messageGo to next message
David Peters
Messages: 6
Registered: April 2004
Junior Member
Daljit,

Thats where I think the problem lies - the CBO believes that a full table scan is better than using the index. If you look at the statistics it generates, this is true.

However, the actual results prove different. If I force it to use an index using the /*+ index(i) +/ hint, the query runs a lot faster.

I dont know why a full scan is so slow, though, as the table is very small.

I changed the parameter for optimizer_index_cost_adj from 100 (default) to 10 and it seems to choose to use indexes, and this fixes the problem.
Re: Cost-based optimization slows down my query [message #65109 is a reply to message #65102] Fri, 30 April 2004 10:16 Go to previous messageGo to next message
shoblock
Messages: 325
Registered: April 2004
Senior Member
The cost based optimizer has two parts - FIRST_ROWS and ALL_ROWS. ALL_ROWS is gear towards best throughput (ideal for data whses when you want to compare ALL data for a massive report). FIRST_ROWS is geared towards fastest first row retrieval - bringing some data back quickly.

ALL_ROWS assumes that you want to process all the data in the table, all the time. Hence the full table scans.

FIRST_ROWS assumes you just want something back quickly (for a user screen), so it uses indexes more often.

RULE has been around forever, and functions the same as it always has - from a list of rules, which are applied in the same order, always, and makes no assumptions as to your intentions.

The big thing here is this - when your optimizer is in CHOOSE mode (the default) it always chooses ALL_ROWS (this is its definition, and you can't do anything about it). So, you can use the RULE hint, and also try the FIRST_ROWS hint, query by query. Changing the database to FIRST_ROWS mode is an option, however, it will most likely have adverse affects on some other queries, so changing it in a production system is not recommended.
Re: Cost-based optimization slows down my query [message #65111 is a reply to message #65109] Mon, 03 May 2004 07:19 Go to previous message
David Peters
Messages: 6
Registered: April 2004
Junior Member
Thanks for that explanation.

Im new to tuning, but it seems odd to me that ALL_ROWS is the default. How often (in most applications) is all data in a table being returned in a query?
Previous Topic: Optimize performance for Update Opertaion
Next Topic: Simple SELECT statement taking too long
Goto Forum:
  


Current Time: Thu Mar 28 05:41:45 CDT 2024