Home » RDBMS Server » Performance Tuning » Performance issue in one sql (11i)
Performance issue in one sql [message #598844] Fri, 18 October 2013 03:31 Go to next message
Messages: 16
Registered: October 2013
Location: india
Junior Member
I am facing performance issue in one sql.Attached is the tkprof

SELECT OOL.inventory_item_id 
FROM   oe_order_lines OOL, 
       mtl_system_items_b MSIB 
WHERE  OOL.top_model_line_id = :B4 
       AND OOL.header_id = :B3 
       AND MSIB.item_type IN ( :B2, :B1 ) 
       AND OOL.inventory_item_id = MSIB.inventory_item_id 
       AND OOL.ship_from_org_id = MSIB.organization_id 

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.01       0.00          0          0          0           0
Execute  12166      0.72       0.79          0          0          0           0
Fetch    12178    153.73    5089.92     475806    4102075          0      499422
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total    24345    154.46    5090.71     475806    4102075          0      499422

*BlackSwan added {code} tags. Please do so yourself in the future.

[Updated on: Fri, 18 October 2013 07:52] by Moderator

Report message to a moderator

Re: Performance issue in one sql [message #598845 is a reply to message #598844] Fri, 18 October 2013 04:16 Go to previous messageGo to next message
John Watson
Messages: 8535
Registered: January 2010
Location: Global Village
Senior Member
This is your third request for assistance in an unusually short period. It is time you started to follow the forum rules. First, please read our OraFAQ Forum Guide and please read How to use [code] tags and make your code easier to read
Then, for a performance assistance request, please read this
and provide all the necessary information.
And, to repeat: format your code, and enclose it within tags.
Re: Performance issue in one sql [message #598864 is a reply to message #598845] Fri, 18 October 2013 08:37 Go to previous messageGo to next message
Messages: 26763
Registered: January 2009
Location: SoCal
Senior Member
SQL> select 499422/12166 rows_per_call, 5090.71/12166 secs_per_call from dual;

------------- -------------
   41.0506329    .418437449

each invocation averages less than 0.5 seconds which seems reasonable & acceptable.
It appears to me not to be a problem with the SQL itself; but the fact it is being called an excessive number of times.
Re: Performance issue in one sql [message #598960 is a reply to message #598844] Mon, 21 October 2013 00:21 Go to previous messageGo to next message
Messages: 621
Registered: July 2006
Senior Member
1. You didn't supply any additional data (index/table structures etc. especially index OE_ORDER_LINES_ALL_X14 ) so I'm guessing.
2. Can you try following:
CREATE INDEX ... ON OE_ORDER_LINES_ALL ( top_model_line_id, header_id, inventory_item_id, ship_from_org_id ) ...
3. According to TKPROF you posted - it's just first of many performance problems you have with your job, so try doing your job instead calling for somebody to do it for you.

Re: Performance issue in one sql [message #600389 is a reply to message #598960] Tue, 05 November 2013 23:35 Go to previous message
Kevin Meade
Messages: 2103
Registered: December 1999
Location: Connecticut USA
Senior Member
Just as a comment, the query looks wrong. If you join across those two columns but then only select one of them, I presume your output can contain dups. Dups is usually wrong in a result. The other alternative is your join is wrong. Can you comment on this?

Previous Topic: Understanding ASH
Next Topic: Which SQL is faster/better
Goto Forum:

Current Time: Fri Apr 23 03:06:43 CDT 2021