Home » RDBMS Server » Performance Tuning » query tuning using explain plan (oracle , linux)
query tuning using explain plan [message #310724] Wed, 02 April 2008 03:56 Go to next message
sheldon1982
Messages: 59
Registered: February 2008
Location: india
Member
Hi EXpert,
one of my client is facing. his particular query is running very slow.
i have pasted the query and the execution plan

select order_number

from apps.oe_order_headers h,

apps.oe_order_lines l

where h.org_id = 2506

and h.order_source_id <>10

and h.order_category_code = 'ORDER'

and h.header_id = l.header_id

and l.invoice_interface_status_code = 'YES'

group by order_number


Operation Object Name Rows Bytes Cost Object Node In/Out PStart

SELECT STATEMENT Hint=CHOOSE 319 17313
SORT GROUP BY 319 12 K 17313
TABLE ACCESS BY INDEX ROWID OE_ORDER_LINES_ALL 1 14 18
NESTED LOOPS 325 13 K 17264
TABLE ACCESS FULL OE_ORDER_HEADERS_ALL 319 8 K 11522
INDEX RANGE SCAN OE_ORDER_LINES_N1 9 2

could you gurus please help me in tuning this query.

Thanks ,
Sheldon
Re: query tuning using explain plan [message #310734 is a reply to message #310724] Wed, 02 April 2008 04:27 Go to previous messageGo to next message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Once again, format your post.

Regards
Michel
Re: query tuning using explain plan [message #310742 is a reply to message #310734] Wed, 02 April 2008 04:40 Go to previous messageGo to next message
sheldon1982
Messages: 59
Registered: February 2008
Location: india
Member
is there any problem with this post?
Re: query tuning using explain plan [message #310745 is a reply to message #310742] Wed, 02 April 2008 04:46 Go to previous messageGo to next message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Yes format.

Regards
Michel
Re: query tuning using explain plan [message #310939 is a reply to message #310742] Wed, 02 April 2008 21:26 Go to previous message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
sheldon1982 wrote on Wed, 02 April 2008 19:40
is there any problem with this post?


Explain Plans are difficult to read if leading spaces are removed because the tree-structure is lost.

Please enclose all code and plans in [code] .. [/code] tags.

Ross Leishman
Previous Topic: insert took a lnog time.
Next Topic: How to decrease time to execute SUM function?
Goto Forum:
  


Current Time: Fri Dec 09 09:57:15 CST 2016

Total time taken to generate the page: 0.13967 seconds