Home » RDBMS Server » Performance Tuning » query Dead Slow (oRACLE EBS R12 )
query Dead Slow [message #659517] Fri, 20 January 2017 01:27 Go to next message
annu-agi
Messages: 218
Registered: July 2005
Location: Karachi
Senior Member

DEAR EXPERTS

Given below query response a very slow, although indexes already created and tabled optimized and gather schema is done . Given query is only running on 1 item and it takes 10 min minimum .. please suggest best of what we do to get early results.

select Mtln.Organization_Id, mtln.inventory_item_id, mtln.lot_number, round(sum(mtln.PRIMARY_QUANTITY)) 
from mtl_transaction_lot_numbers mtln
where 1=1
and Mtln.Inventory_Item_Id=695
and to_date(mtln.TRANSACTION_DATE) <= :P_EDT
--and mtln.transaction_id =(select transaction_id from mtl_material_transactions where transaction_id=Mtln.Transaction_Id and Subinventory_Code='Fresh') 
group by Mtln.Organization_Id, mtln.inventory_item_id, mtln.lot_number
having sum(mtln.PRIMARY_QUANTITY )<>0

regards

Anwer
Re: query Dead Slow [message #659518 is a reply to message #659517] Fri, 20 January 2017 01:33 Go to previous messageGo to next message
Michel Cadot
Messages: 65208
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Review your indexes.

Re: query Dead Slow [message #659519 is a reply to message #659517] Fri, 20 January 2017 01:37 Go to previous messageGo to next message
John Watson
Messages: 7150
Registered: January 2010
Location: Global Village
Senior Member
Review your use of data types and type casting, such as this,
and to_date(mtln.TRANSACTION_DATE) <= :P_EDT
Re: query Dead Slow [message #659522 is a reply to message #659517] Fri, 20 January 2017 05:09 Go to previous messageGo to next message
FDAVIDOV
Messages: 20
Registered: December 2014
Junior Member
Before trying drastic changes, please try the following:

You are converting "mtln.TRANSACTION_DATE" to a date to compare against ":P_EDT". Instead of doing that, convert P_EDT to whatever format you are using in the table for the field "TRANSACTION_DATE".

One more comment (though not related to performance at all): The use of aliases ("mtln" in your case) is required (and makes things much clearer) when you are selecting from more than one source (table/view). When you are selecting from a single table, it is not needed and makes the code less readable than it should.
Re: query Dead Slow [message #659523 is a reply to message #659522] Fri, 20 January 2017 05:17 Go to previous messageGo to next message
cookiemonster
Messages: 12930
Registered: September 2008
Location: Rainy Manchester
Senior Member
If transaction_date is a string then converting p_edt to the same format isn't going to work unless that format is YYYYMMDD ( + HH24:MI:SS if time is relevant). If it's any other format the < will give the wrong results.


Re: query Dead Slow [message #659529 is a reply to message #659523] Fri, 20 January 2017 05:55 Go to previous messageGo to next message
FDAVIDOV
Messages: 20
Registered: December 2014
Junior Member
Cookiemonster, of course you are right, but it would be fair to assume that the string is NOT simply 8 digits in the form "YYYYMMDD" but something more unambiguous, like "12-Feb-2017", which does not require a format specification. Moreover, I can't testing right now, but I doubt PL/SQL would cope with such conversion without a format specification.

In any case, my message to the OP is: do NOT case a column from the table to the format of the parameter, but do it the other way round (if possible of course).

Having said that, and based on the (poor) available information, I still think the OP should try my suggestion.
Re: query Dead Slow [message #659530 is a reply to message #659529] Fri, 20 January 2017 06:02 Go to previous messageGo to next message
John Watson
Messages: 7150
Registered: January 2010
Location: Global Village
Senior Member
Quote:
something more unambiguous, like "12-Feb-2017", which does not require a format specification.
that most certainly does require a format specification. In my case, for example, the conversion works but gives a date of five o'clock in the afternoon back in the days of Emperor Augustus:
orclz>
orclz> select to_date('12-Feb-2017') from dual;

TO_DATE('12-FEB-201
-------------------
0012-02-20:17:00:00

orclz>
orclz>
Re: query Dead Slow [message #659534 is a reply to message #659530] Fri, 20 January 2017 06:34 Go to previous messageGo to next message
cookiemonster
Messages: 12930
Registered: September 2008
Location: Rainy Manchester
Senior Member
As long as the format matches the default nls_date_format it won't matter what it is (as long as users don't go around changing it, which they might).

However, it might be the OP is using to_date to do what trunc does, I keep coming across people doing that.

@annu-agi - if transaction_date is actually a date and you're trying to get rid of the time part for comparison purposes, use trunc instead, that's why it exists.
Re: query Dead Slow [message #659539 is a reply to message #659529] Fri, 20 January 2017 10:35 Go to previous messageGo to next message
Michel Cadot
Messages: 65208
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
Having said that, and based on the (poor) available information
So why do you give "solution"?

Re: query Dead Slow [message #659540 is a reply to message #659539] Fri, 20 January 2017 10:37 Go to previous message
Michel Cadot
Messages: 65208
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

@OP

As already told you in your previous "query Dead Slow" topic:

Michel Cadot wrote on Wed, 07 December 2016 13:48

For any performances question, please read http://www.orafaq.com/forum/mv/msg/84315/433888/102589/#msg_433888 and post the required information.
Previous Topic: Avoiding the -ve execute to parse for MV refresh(highest parse calls total than execution totals)
Next Topic: Tuning update query - for more than 3 billion rows
Goto Forum:
  


Current Time: Fri Nov 24 17:59:33 CST 2017

Total time taken to generate the page: 0.03349 seconds