OraFAQ Forum: Performance Tuning » Help : Understanding the Explain Plan

Home » RDBMS Server » Performance Tuning » Help : Understanding the Explain Plan

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Help : Understanding the Explain Plan [message #249901]

Fri, 06 July 2007 08:19

orasaket
Messages: 70
Registered: November 2006

Member

Hi,

I am using Oracle 9i R2 on Linux

Please refer following query and Explain Plan


select * from com_process_log where nprocessid in(select nparamcd from com_param_system_m where nparamcd=702);

--------------------------------------------------------------------------------------
| Id  | Operation                   |  Name                  | Rows  | Bytes | Cost  |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                        |    62 |  2790 |    74 |
|   1 |  TABLE ACCESS BY INDEX ROWID| COM_PROCESS_LOG        |    17 |   714 |    18 |
|   2 |   NESTED LOOPS              |                        |    62 |  2790 |    74 |
|   3 |    SORT UNIQUE              |                        |       |       |       |
|   4 |     INDEX FAST FULL SCAN    | XPKCOM_PARAM_SYSTEM_M  |     4 |    12 |     3 |
|   5 |    INDEX RANGE SCAN         | XPKPROCESS_LOG         |    17 |       |     7 |
--------------------------------------------------------------------------------------

There is Composite index on IPARAMTYPECD, NPARAMCD
CREATE UNIQUE INDEX XPKCOM_PARAM_SYSTEM_M ON COM_PARAM_SYSTEM_M(IPARAMTYPECD, NPARAMCD)

Following is the existing data
com_param_system_m = 1534 rows
distinct values for iparamtypecd = 45
com_process_log = 61912 rows

My Queries are,
How come we have 'FAST FULL SCAN' of XPKCOM_PARAM_SYSTEM_M instead of 'SKIP SCAN'?
Is it because of NPARAMCD being NOT NULL?

We have a single row returning for this query
What does the ROWS field in PLAN show - 4?

What is SORT(UNIQUE) in the Plan?

How the Nested Join is formed here?
I believe it would be beween the following

rows returned from Index acceess of com_param_system_m
rows returned from com_process_log table - retrieved from rowid access using index xpkcom_process_log

Please Advice

Also if i am changing my query as below, why com_param_system is doing FTS?

Also why after nested loop join we are accessing com_process_log table again?


select * from com_process_log cpl,cpsm where nprocessid =nparamcd and nparamcd=702

--------------------------------------------------------------------------------
| Id  | Operation                   |  Name            | Rows  | Bytes | Cost  |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                  |    93 |  8928 |    93 |
|   1 |  TABLE ACCESS BY INDEX ROWID| COM_PROCESS_LOG  |    17 |   714 |    18 |
|   2 |   NESTED LOOPS              |                  |    93 |  8928 |    93 |
|   3 |    TABLE ACCESS FULL        | CPSM             |     5 |   270 |     5 |
|   4 |    INDEX RANGE SCAN         | XPKPROCESS_LOG   |    17 |       |     7 |
--------------------------------------------------------------------------------

Please advice

Thanks and Regards,
OraSaket

Report message to a moderator

Re: Help : Understanding the Explain Plan [message #249910 is a reply to message #249901]

Fri, 06 July 2007 08:45

Michel Cadot
Messages: 68716
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Quote:

How come we have 'FAST FULL SCAN' of XPKCOM_PARAM_SYSTEM_M instead of 'SKIP SCAN'?

Because FFS is faster than SS in your case. FFS allows Oracle to use multiple blocks read. SS scans chained blocks. Depending on the number of blocks FFS is faster than SS.

Quote:

We have a single row returning for this query
What does the ROWS field in PLAN show - 4?

It is an estimate number.

Quote:

What is SORT(UNIQUE) in the Plan?

As you have a "in (select ..." Oracle does not care of duplicate values, it just have to know distinct ones.

Quote:

How the Nested Join is formed here?

For each distinct value returned by XPKCOM_PARAM_SYSTEM_M, Oracle searches corresponding rows in XPKPROCESS_LOG.

Quote:

Also if i am changing my query as below, why com_param_system is doing FTS?

There is no such table in the following plan.
If you qualify the column name we may be know from which table they come.
Basically Oracle scans the full cpsm table to get all the nprocessid=702 from which it searches all corresponding com_process_log rowids through the index and finally from these rowids the other columns of com_process_log.

Regards
Michel

Report message to a moderator

Re: Help : Understanding the Explain Plan [message #249966 is a reply to message #249901]

Fri, 06 July 2007 11:12

orasaket
Messages: 70
Registered: November 2006

Member

Superb !!

Michel thanks for the explaination.

I have one important query on this.

When you seniors start looking in to the queries posted by us,for Tuning, what is you starting point?
First you check joins, Then Index Access or something else?

In case you are first checking 'how data is filtered throgh where clauses or between joins', can you suggest me any thumb rules?

I know this is very generic question but these inputs will help me alot.

Thanks and Regards,
OraSaket

Report message to a moderator

Re: Help : Understanding the Explain Plan [message #249977 is a reply to message #249966]

Fri, 06 July 2007 11:58

Michel Cadot
Messages: 68716
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Quote:

I know this is very generic question but these inputs will help me alot.

This is the reason I never answer in Performance Tuning to SQL tuning question, there are many parameters that may interfere in the plan: statistics, indexes, optimizer parameters, nls parameters, object descriptions, contraints and so on that it would take hours to ask all questions.
Others here like these questions maybe they will more deeply answer you.

Regards
Michel

Report message to a moderator

Re: Help : Understanding the Explain Plan [message #250101 is a reply to message #249977]

Sat, 07 July 2007 20:27

rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia

Senior Member

A rule of thumb will never capture all of the things that go through an expert's mind when looking at a SQL/plan. Nor will applying such a rule-of-thumb make you an expert Wink

But in an attepmt to answer your question, the things that occur to me every time I look at a SQL or Plan are:

Is this SQL supposed to return a small volume of data, or a large volume?
If small volume, is it performing any full scans on large segments, or any long range or skip scans?
If large volume, is it performing any nested operations (NL joins, filters) over a large data set?

There's more to it than that of course, 85 pages more to be precise. More of my thoughts on tuning here.

Ross Leishman

Report message to a moderator

Re: Help : Understanding the Explain Plan [message #250149 is a reply to message #249901]

Sun, 08 July 2007 12:04

orasaket
Messages: 70
Registered: November 2006

Member

Thank you very much

There is very useful material on the link provided

Ross and Michel Thanks for your time and useful responses

Regards,
OraSaket

Report message to a moderator

Previous Topic:	99% Hit-Ratio but poor performance
Next Topic:	How to use optimizer hint in a recursivly called procedure

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sat Nov 23 08:16:52 CST 2024