Home » RDBMS Server » Performance Tuning » query Dead Slow (11g, 11.2.0.3.0)
query Dead Slow [message #658228] Wed, 07 December 2016 06:33 Go to next message
annu-agi
Messages: 228
Registered: July 2005
Location: Karachi
Senior Member

dear experts

I have a sub query

(SELECT NVL(SUM(DECODE(l.LINE_CATEGORY_CODE,'ORDER',l.ORDERED_QUANTITY))-SUM(NVL(DECODE(l.LINE_CATEGORY_CODE,'RETURN',l.ORDERED_QUANTITY),0)),0)
FROM oe_order_headers_all h,
oe_order_lines_all l
WHERE h.HEADER_ID = l.HEADER_ID
AND h.ORG_ID      = l.ORG_ID
AND h.org_id      =
(SELECT d.OPERATING_UNIT
FROM org_organization_definitions d
WHERE d.ORGANIZATION_code = :p_Inv_Org_Id
)
AND l.INVENTORY_ITEM_ID = msib.INVENTORY_ITEM_ID
AND h.ORDERED_DATE     <= :p_TransDate
) Com_Sale,

retrive data based on main query where msib.INVENTORY_ITEM_ID joined from main query. Query result is fine but responces time is dead slow.

I am finding a causes why this is dead slow, Please suggest solutions and suggest best possible index combinations.

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
PL/SQL Release 11.2.0.3.0 - Production
"CORE	11.2.0.3.0	Production"
TNS for Linux: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production


regards

Anwer Ali
Re: query Dead Slow [message #658235 is a reply to message #658228] Wed, 07 December 2016 06:48 Go to previous messageGo to next message
Michel Cadot
Messages: 65844
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

For any performances question, please read http://www.orafaq.com/forum/mv/msg/84315/433888/102589/#msg_433888 and post the required information.

Re: query Dead Slow [message #658249 is a reply to message #658228] Wed, 07 December 2016 09:06 Go to previous message
John Watson
Messages: 7619
Registered: January 2010
Location: Global Village
Senior Member
What do you mean by saying that this is a sub-query? Is this just part of a larger SQL? If so, you need to show the whole thing, and of course the execution plan. You can't tune just one sub-query, Oracle may have merged it into the rest.
Previous Topic: ORA-01555 error during expdp
Next Topic: Oracle Advance Queue consumes messages very slowly
Goto Forum:
  


Current Time: Fri Oct 19 01:47:09 CDT 2018