| strange optimizer behaviour... [message #58541] |
Tue, 16 September 2003 02:34  |
Mat
Messages: 22
Registered: November 2001
|
Junior Member |
|
|
Hi everybody,
We have got a billing application that uses a complex request with several joins, order by and group by, the parameter optimizer_mode is CHOOSE, and we update statistics (both on tables and indexes) just before launching the request. In this case, we can see in the execution plan that the optimizer do not use indexes, thus drastically increasing time response. Deleting statistics for indexes, the optimizer changes the execution plan to use indexes !!! According to me, the optimizer must have statistics on all objects of the schema (including indexes) to compute an efficient execution plan (using indexes in our case).
How to explain such a behaviour ?
Thanks !
|
|
|
|
| Re: strange optimizer behaviour... [message #58551 is a reply to message #58541] |
Tue, 16 September 2003 10:25  |
Sanjay
Messages: 236
Registered: July 2000
|
Senior Member |
|
|
Sometimes depending on the Analyzed info, the optimizer behaves like that. Too bad of a design from Oracle.
Better, if you know which index it should hit or which you want it to hit, put a hint in the SQL which will do as told:
SELECT /*+ INDEX (table_name index_name) */ .... FROM ...
Note: You mentioned that you have set the optimizer for CHOOSE. Can you set it to ALL_ROWS or FIRST_ROWS ?
Good luck.
|
|
|
|
| Re: strange optimizer behaviour... [message #58552 is a reply to message #58541] |
Tue, 16 September 2003 10:26 |
Sanjay
Messages: 236
Registered: July 2000
|
Senior Member |
|
|
Sometimes depending on the Analyzed info, the optimizer behaves like that. Too bad of a design from Oracle.
Better, if you know which index it should hit or which you want it to hit, put a hint in the SQL which will do as told:
SELECT /*+ INDEX (table_name index_name) */ .... FROM ...
Note: You mentioned that you have set the optimizer for CHOOSE. Can you set it to ALL_ROWS or FIRST_ROWS ?
Good luck.
|
|
|
|
| Re: strange optimizer behaviour... [message #58556 is a reply to message #58541] |
Tue, 16 September 2003 18:37 |
Todd Barry
Messages: 4819
Registered: August 2001
|
Senior Member |
|
|
The default setting for optimizer_index_cost_adj is 100 which is way too high. Try setting this to a much lower figure - which reduces the "cost" that Oracle associates with indexes - and see how the execution plan changes.
You can change this at the session level to easily see how the plan changes:
alter session set optimizer_index_cost_adj = 10; |
|
|
|
| Re: strange optimizer behaviour... [message #58557 is a reply to message #58541] |
Tue, 16 September 2003 18:46 |
Peter
Messages: 62
Registered: August 2000
|
Member |
|
|
The optimizer does not always come up with the most efficient explain plan. You will find that sometimes using the rule based optimizer will be more efficient (thus why deleting statistics makes your query run faster). You can try making some changes to the optimizer behaviour by making alteration to the following 2 initialization parameters
optimizer_index_caching
and
optimizer_index_cost_adj
(I am not going to go into detail and explain these look them up)
of course this will have implications on execution plans for the whole database
OR
as the previous post stated the use of hints is a very effective method in fixing the way the query is executed
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
