| Explain Plan Doubt [message #117972] |
Mon, 02 May 2005 05:32  |
sujit_ocp
Messages: 7
Registered: May 2005
Location: Chennai
|
Junior Member |
|
|
SELECT OG_ID
FROM TP_TR_OGGETTO
WHERE OG_LID = :BOL
AND OG_TYPE = :BOT
AND OG_OGGETTO_ACTUAL_DATE = TO_DATE(:BOGAD,'DD/MM/YYYY')
and OG_BANK = :BOB
and og_curr_status_id = :BOCSI;
SQL> @?\rdbms\admin\utlxpls
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 30 | 9 |
|* 1 | TABLE ACCESS BY INDEX ROWID| TP_TR_OGGETTO | 1 | 30 | 9 |
|* 2 | INDEX RANGE SCAN | TP_OG_CURR_LID_STATUS_ID_1001 | 6 | | 3 |
----------------------------------------------------------------------------------------------
Above is the plan for one Query which am supposed to tune.
My doubt is Why optimizer is not using all the available indexes in the plan.
e.g
TP_OG_CURR_LID_STATUS_ID_1001 index is a composite index : (og_curr_status_id,OG_LID)
Optimizer is using only the above index.
But the Other indexes are available in the Following columns and they are not composite.
1. OG_OGGETTO_ACTUAL_DATE
2. OG_TYPE.
Please If u have any observation let me know fast as it is Urgent.
Regards,
Sujit
|
|
|
|
|
|
| Re: Explain Plan Doubt [message #117998 is a reply to message #117990] |
Mon, 02 May 2005 09:22  |
sujit_ocp
Messages: 7
Registered: May 2005
Location: Chennai
|
Junior Member |
|
|
Thanks a lot Mahesh,
There is no Function Based Index on OG_OGGETTO_ACTUAL_DATE and
Tables are analyzed recently.
Should all the Tables be analyzed.
What could be the Other reason and How can we influence Optimizer to go for Other Indexes.
Regards,
sujit
|
|
|
|
|
|
| Re: Explain Plan Doubt [message #118005 is a reply to message #117972] |
Mon, 02 May 2005 09:47 |
smartin
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida
|
Senior Member |
|
|
Are you asking why the CBO is using just one of your indexes and not using all of them at the same time in some sort of combination effect to limit the returned rows even more?
Btree indexes don't work that way. The CBO will pick the index it feels is "best" and then scan it (using one of its scan methods, range scan, fast full scan, etc) and then use the rowid's it obtains to hit the table.
Depending on your situation you might want to make just one single large composite index on the 4 fields that you query regularly. Or, if these are the only columns in the table then consider an index organized table.
A bitmap index, on the other hand, can use a combination effect on all of the indexes to limit the results. But bitmaps are not good for data that is modified.
Make sure you have full stats on all of your tables, not just some of them. Use dbms_stats, and get histograms.
The CBO and its decisions are a big thing to understand, it is best to commit yourself to some serious reading of the entire oracle performance tuning guide.
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
