Home » RDBMS Server » Performance Tuning » How to read the execution plan
icon4.gif  How to read the execution plan [message #121806] Wed, 01 June 2005 02:54 Go to next message
shibu
Messages: 6
Registered: October 2000
Junior Member
Hi all,

Can anybody help me in reading the execution plan of the following query. The purpose of the query is to get the count of records where the price of the part is greater than 4 times of the minimum price.

SELECT source,NVL (location,subentity_cd) location,COUNT (*)
FROM
(SELECT MIN (us_po_unit_pr) * 4 min_price,part_nr
FROM receipts_fact alias2
WHERE alias2.source = :source
AND NVL(alias2.location,alias2.subentity_cd) = :location
AND trans_type_cd = 'Recpt'
AND us_po_unit_pr <> 0
AND us_po_unit_pr IS NOT NULL
AND alias2.reference_dt BETWEEN
ADD_MONTHS (TRUNC (SYSDATE,'MON'),-1)
AND TRUNC (SYSDATE, 'MON') - 1
GROUP BY part_nr) alias3, combined_receipts alias1
WHERE alias1.source = :source
AND NVL (alias1.location,alias1.subentity_cd) = :location
AND trans_type_cd = 'Recpt'
AND reference_dt BETWEEN ADD_MONTHS (TRUNC (SYSDATE,'MON'),-1 )
AND TRUNC (SYSDATE, 'MON') - 1
AND alias1.part_nr = alias3.part_nr
AND alias1.us_po_unit_pr > alias3.min_price
GROUP BY source,NVL (location, subentity_cd)

And the plan is as follows

ID PID OPERATION	OPTIONS	            OBJECT_NAME	    
----------------------------------------------------------------
0 |   |SELECT STATEMENT|		    |	           
1 | 0 |SORT	       |GROUP BY	    |	            
2 | 1 |VIEW	       |		    |                            
3 | 2 |FILTER	       |		    |
4 | 3 |SORT	       |GROUP BY	    |	            
5 | 4 |FILTER	       |  		    |
6 | 5 |TABLE ACCESS    |BY LOCAL INDEX ROWID|COMBINED_RECEIPTS
7 | 6 |NESTED LOOPS    |		    |
8 | 7 |TABLE ACCESS    |FULL	            |RECEIPTS_FACT
9 | 7 |INDEX	       |RANGE SCAN	    |CR_PNR_INDX	


CR_PNR_INDX is a non unique index on part_nr field in combined_receipts table.

My understanding is that the - the inline view will get executed first and the records from the combined_receipts table is checked against the values in the inline view. When I look at the execution plan I'm little bit confused.

Thanks in advance.
Shibu
  • Attachment: eplan.txt
    (Size: 0.57KB, Downloaded 1495 times)
Re: How to read the execution plan [message #121980 is a reply to message #121806] Thu, 02 June 2005 03:59 Go to previous messageGo to next message
nabeelkhan
Messages: 73
Registered: May 2005
Location: Kuwait
Member

As for "How to read the execution plan" -- I'm not sure at all what you are looking for. Do you mean "how do I interpret it"? If so please see:

http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96533/ex_plan.htm#16972

In fact, the entire document:

http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96533/toc.htm

which is the server tuning guide, will be invaluable to you in learning this.

It covers all of the access plans and such so you'll know what a SORT MERGE JOIN versus NESTED LOOPS means.

What optimizer_mode u are using?
Re: How to read the execution plan [message #122118 is a reply to message #121980] Fri, 03 June 2005 00:28 Go to previous messageGo to next message
shibu
Messages: 6
Registered: October 2000
Junior Member
Hi Nabeelkhan,

Thanks for your response.

I know how to interpret an execution plan but this one is quite confusing. I've explained my view on how the query will be executed but the Oracle gave me altogether a different plan.

Regards,
Shibu
Re: How to read the execution plan [message #122214 is a reply to message #121806] Fri, 03 June 2005 18:00 Go to previous messageGo to next message
joeancell
Messages: 19
Registered: June 2005
Junior Member
I download DataStudio today http://www.agileinfollc.com and play it a while, one feature I find it can display execution plan and also lists the step Oracle will do step by step, it should be able to solve your issue. It is pretty cool.

[Updated on: Fri, 03 June 2005 18:00]

Report message to a moderator

Re: How to read the execution plan [message #122292 is a reply to message #122118] Sun, 05 June 2005 02:55 Go to previous messageGo to next message
nabeelkhan
Messages: 73
Registered: May 2005
Location: Kuwait
Member

shibu wrote on Fri, 03 June 2005 08:28

Hi Nabeelkhan,

Thanks for your response.

I know how to interpret an execution plan but this one is quite confusing. I've explained my view on how the query will be executed but the Oracle gave me altogether a different plan.

Regards,
Shibu


Wat optimizer mode u are using?
Re: How to read the execution plan [message #122421 is a reply to message #122292] Mon, 06 June 2005 08:11 Go to previous messageGo to next message
shibu
Messages: 6
Registered: October 2000
Junior Member
The optimizer mode is Choose...
Re: How to read the execution plan [message #122433 is a reply to message #121806] Mon, 06 June 2005 09:06 Go to previous message
smartin
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida
Senior Member
It looks to me like your receipts_facts table, which is the only one in the inline view, is in fact getting read first. Steps 8 and 9, with a parent step of 7, are going first. Then step 6, the combined_receipts table, comes into the picture. What makes you think otherwise?
Previous Topic: Composite bitmap indexes
Next Topic: Stubborn Query
Goto Forum:
  


Current Time: Tue Mar 19 01:32:22 CDT 2024