Home » RDBMS Server » Performance Tuning » Explain Plan Doubt
Explain Plan Doubt [message #117972] Mon, 02 May 2005 05:32 Go to next message
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 #117990 is a reply to message #117972] Mon, 02 May 2005 08:19 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10694
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
Is there a Function Based index on OG_OGGETTO_ACTUAL_DATE?
CBO decides to use /ignore an index based on many reasons including
the statistics available and skewness of data.
Are the tables/indexes analyzed?
Re: Explain Plan Doubt [message #117998 is a reply to message #117990] Mon, 02 May 2005 09:22 Go to previous messageGo to next message
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 #118002 is a reply to message #117998] Mon, 02 May 2005 09:31 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10694
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
Try again with a function based index.
Analyze all indexes on table TP_TR_OGGETTO and Table itself.
Look into docs for CBO basis.

>>What could be the Other reason and How can we influence Optimizer to go for Other Indexes

CBO will not use the index and prefers to go with Full Table Scan based on the data. If there are too much rows returned, CBO prefers NOT to use index and a full table scan is NOT always harmful.
So it all depends on data.



Re: Explain Plan Doubt [message #118005 is a reply to message #117972] Mon, 02 May 2005 09:47 Go to previous messageGo to next message
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.
Re: Explain Plan Doubt [message #118010 is a reply to message #118005] Mon, 02 May 2005 10:11 Go to previous message
sujit_ocp
Messages: 7
Registered: May 2005
Location: Chennai
Junior Member
Thanks for ur Advice.

I will do that.

Regards,

Sujit
Previous Topic: what should be approximate Size of SGA ?
Next Topic: dbms_stats
Goto Forum:
  


Current Time: Tue Jan 19 22:36:52 CST 2021