Home » RDBMS Server » Performance Tuning » Query is taking huge time to display the record (Oracle e-business Suite 12.1.3.)
Query is taking huge time to display the record [message #604380] Mon, 30 December 2013 00:10 Go to next message
ajit.jha31
Messages: 3
Registered: December 2013
Junior Member
I have a custom OAF page in Oracle Apps.Over there we are trying to get value based on item no.
Whatever query is running behind that OAF page is attached along with execution plan.Trace file,TKPROF file is too big to load in this forum.That's why just loaded the particular query and their execution plan.

We are using Union All to combine the result-set of 43 different select statement.
This query used as Cursor in a Procedure.This procedure is displaying the data on the base of item id in OAF page.

Currently it is taking around 3-4 min to display the data.
Please help me out to tune this query...
Re: Query is taking huge time to display the record [message #604381 is a reply to message #604380] Mon, 30 December 2013 00:30 Go to previous messageGo to next message
ajit.jha31
Messages: 3
Registered: December 2013
Junior Member
Query is too larg that's why I am not able to post over here

[Updated on: Mon, 30 December 2013 00:38]

Report message to a moderator

Re: Query is taking huge time to display the record [message #604382 is a reply to message #604381] Mon, 30 December 2013 00:48 Go to previous messageGo to next message
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
Senior Member
The elapsed time is 48.15 seconds for the query and it returns 7 rows. You are saying it takes 3-4 minutes to display, so what else is happening?

The following index is associated with which table? Post the total count of rows of that table.
INDEX RANGE SCAN XXADAT_MTL_MATERIAL_TXN_N1


Post the result of the following :
select count(*) from OE_ORDER_LINES_ALL;
select count(*) from OE_ORDER_HEADERS_ALL;


Could you please explain why are you doing this?
AND OOLA1.LINE_ID != OOLA.LINE_ID
AND OOLA1.HEADER_ID = OOHA.HEADER_ID

[Updated on: Sat, 08 March 2014 13:50] by Moderator

Report message to a moderator

Re: Query is taking huge time to display the record [message #604386 is a reply to message #604382] Mon, 30 December 2013 01:30 Go to previous messageGo to next message
ajit.jha31
Messages: 3
Registered: December 2013
Junior Member
XXADAT_MTL_MATERIAL_TXN_N1 Index is on mtl_material_transactions table.
select count(*) from mtl_material_transactions; --No Of Record: 11007780

select count(*) from OE_ORDER_LINES_ALL; -- No Of Record:608265
select count(*) from OE_ORDER_HEADERS_ALL; -- No Of Record:229122

Re: Query is taking huge time to display the record [message #604394 is a reply to message #604380] Mon, 30 December 2013 02:04 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
Welcome to the forum. Please read our OraFAQ Forum Guide and please read How to use [code] tags and make your code easier to read

You cannot expect anyone to read such a huge mess of unformatted code, and I am surprised that your local coding standards permit it. On this occasion, I have formatted it for you. Please do this yourself in future.

Query is here


[EDITED by LF: applied [spoiler] tags]

[Updated on: Mon, 30 December 2013 05:59] by Moderator

Report message to a moderator

Re: Query is taking huge time to display the record [message #604421 is a reply to message #604394] Mon, 30 December 2013 08:13 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
Since NOBODY can optimize SQL just by looking at it, we need a few more details.
http://www.orafaq.com/forum/m/433888/#msg_433888
Please refer to URL above & be sure to provide the details requested:
1) DDL for all tables & indexes
2) EXPLAIN PLAN
3) output from SQL_TRACE & tkprof
Re: Query is taking huge time to display the record [message #604544 is a reply to message #604380] Wed, 01 January 2014 00:49 Go to previous messageGo to next message
michael_bialik
Messages: 621
Registered: July 2006
Senior Member
1. IMHO - you have an I/O problem accessing OE_ORDER_LINES_ALL table for the first time (time=18239891 and 1503 physical reads):

  2829       2829       2829           TABLE ACCESS BY INDEX ROWID OE_ORDER_LINES_ALL (cr=6637 pr=1503 pw=0 time=18239891 us cost=10 size=205 card=5)
  2880       2880       2880            INDEX RANGE SCAN OE_ORDER_LINES_N3 (cr=14 pr=0 pw=0 time=10314 us cost=3 size=0 card=10)(object id 119540)


From that moment all accesses to that table are executed in memory and don't use physical IOs.

Can you try following index:

CREATE INDEX ... ON OE_ORDER_LINES_ALL ( INVENTORY_ITEM_ID, LINE_CATEGORY_ID, FLOW_STATUS_CODE, HEADER_ID, SHIP_FROM_ORG_ID, ORDERED_QUANTITY ) COMPUTE STATISTICS ...

?

2. Use query subfactoring (WITH) to retrive the necessary data only once.


HTH.
Re: Query is taking huge time to display the record [message #604834 is a reply to message #604380] Sat, 04 January 2014 06:59 Go to previous messageGo to next message
Kevin Meade
Messages: 2103
Registered: December 1999
Location: Connecticut USA
Senior Member
For those seeking it, here is a formatted version of the query.
You can use this website for formatting queries. Works most of the time.

Query is here



[EDITED by LF: applied [spoiler] tags]

[Updated on: Sat, 04 January 2014 11:59] by Moderator

Report message to a moderator

Re: Query is taking huge time to display the record [message #604835 is a reply to message #604834] Sat, 04 January 2014 07:20 Go to previous message
Kevin Meade
Messages: 2103
Registered: December 1999
Location: Connecticut USA
Senior Member
In order to solve a problem like this, isolate components to determine what is really happening and where time is going. For example, given the query above, you could do this from SQLPLUS.

set timing on

create table temp1
nologging
as
select ...
/

drop table temp1
/

create table temp1
nologging
as
select ...
/

The timed result will give you a wall clock on how long it takes to execute. You may find a large percentage of time may be in parsing so we execute the query twice in a row.

Notice how executing this query twice does two things:

1. it shows you how long the query itself takes (more or less) without dealing with network I/O etc.
2. it shows you how long the parsing takes and what the disk caching might mean to the query.

If you find that the query is taking too long (only you know what too long is) then you can begin to address why. Use query decomposition and reconstruction for a case like this to isolate each individual sub-query to see how long it takes to execute. You may get lucky and find one that is a problem. Do this by decomposing the big query into a set of individual sub-queries and test each sub-query. If that does not yield an answer then incrementally add one sub-query at a time and test each new reconstructed version to see how long it takes, until you have made your way back to the original query. By doing this you then know how much time is being spent in each sub-query and how long parsing takes for each incremental addition. KEEP EXCELLENT NOTES. You don't want to do all this work and then lose data you should have captured. An Excel sheet for the timings with a note is a good idea.

Yes this will take a while, a day probably, but this is tuning. In the end as was noted, you may find any of the following:

1. it is not the query at all as most of the time from your app is expended elsewhere besides the query
2. there may be a specific sub-query that is taking too long
3. parsing may be the culprit due to the large query size and many object references
4. something else

Point is, you will know more tomorrow than you know today. Do not assume the query is the problem. Figure out if the query is the problem or if it is somewhere else. Once you have determined if the query is actually the problem, then you can look at how to tune it. Tuning this SQL is at this moment premature.

Kevin
Previous Topic: Normally why count (*) taking so much of time when compare to select *
Next Topic: Please help me merge query is taking more time
Goto Forum:
  


Current Time: Thu Mar 28 10:43:23 CDT 2024