Home » RDBMS Server » Performance Tuning » Sql statement taking very time (Oracle E-Business Suite R12)
Sql statement taking very time [message #546861] Fri, 09 March 2012 06:15 Go to next message
Osafa
Messages: 14
Registered: July 2011
Location: Malaysia , KL
Junior Member
Hi Guru,

One of my concurrent which calls a packaged procedure is taking enormous amount of time. The same concurrent was in its earlier runs, until last month getting completed in less than an hour. however its now taking almost 24hrs. I checked the AWR report and found that one particular query was taking huge time - more than 6hrs. The query is as follows:

SELECT CURRENCY_CODE FROM XXGL_NOP_WORKINGS WHERE 1=1 AND LEDGER_ID = :B3 AND PERIOD_NAME = :B2 AND BATCH_ID = :B1 GROUP BY CURRENCY_CODE ORDER BY CURRENCY_CODE;

I have two composite indexes on the table:
a. BATCH_ID", "ACCOUNT_CODE
b. BATCH_ID", "CURRENCY_CODE", "GROUP_NAME"


Thanks,
Karim.



Re: Sql statement taking very time [message #546863 is a reply to message #546861] Fri, 09 March 2012 06:28 Go to previous messageGo to next message
Michel Cadot
Messages: 59748
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.

Regards
Michel

[Updated on: Fri, 09 March 2012 06:36]

Report message to a moderator

Re: Sql statement taking very time [message #546865 is a reply to message #546861] Fri, 09 March 2012 06:31 Go to previous messageGo to next message
muralikri
Messages: 638
Registered: August 2011
Location: chennai
Senior Member

How do we now above sql taking long time...
Why are you using 1=1 condition?
Re: Sql statement taking very time [message #546867 is a reply to message #546865] Fri, 09 March 2012 06:37 Go to previous messageGo to next message
Michel Cadot
Messages: 59748
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Why are you using 1=1 condition?


Automatic query generation.

Regards
Michel
Re: Sql statement taking very time [message #546873 is a reply to message #546867] Fri, 09 March 2012 08:39 Go to previous messageGo to next message
Bill B
Messages: 1130
Registered: December 2004
Senior Member
can you show the explain plan for the query?
Re: Sql statement taking very time [message #547036 is a reply to message #546873] Sun, 11 March 2012 23:05 Go to previous messageGo to next message
Osafa
Messages: 14
Registered: July 2011
Location: Malaysia , KL
Junior Member
hi bill,

here's the explain plan for the statement;;

<code>
SELECT CURRENCY_CODE

FROM XXGL_NOP_WORKINGS

WHERE 1 =1

AND LEDGER_ID = :B3

AND PERIOD_NAME = :B2

AND BATCH_ID = :B1

GROUP BY CURRENCY_CODE

ORDER BY CURRENCY_CODE;
</code>

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
Plan hash value: 3855094845

----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 60 | 6 (0)| 00:00:01 |
| 1 | SORT GROUP BY NOSORT | | 1 | 60 | 6 (0)| 00:00:01 |
|* 2 | TABLE ACCESS BY INDEX ROWID| XXGL_NOP_WORKINGS | 1 | 60 | 6 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | XXGL_NOP_WORKINGS_N02 | 1 | | 2 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------

Thanks,
Karim.
Re: Sql statement taking very time [message #547037 is a reply to message #546865] Sun, 11 March 2012 23:10 Go to previous messageGo to next message
Osafa
Messages: 14
Registered: July 2011
Location: Malaysia , KL
Junior Member
My user reported the issue as the concurrent was taking longer than it usually takes (between 1- 2 hrs). When we looked into the view-details in the online , it was almost 24hrs span when the concurrent was running. I therefore went for awr report -> top 10 queries and it show me the name of my package and the query below.

SELECT CURRENCY_CODE

FROM XXGL_NOP_WORKINGS

WHERE 1 =1

AND LEDGER_ID = :B3

AND PERIOD_NAME = :B2

AND BATCH_ID = :B1

GROUP BY CURRENCY_CODE

ORDER BY CURRENCY_CODE;

the explain plan for the same is as follows:
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
Plan hash value: 3855094845

----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 60 | 6 (0)| 00:00:01 |
| 1 | SORT GROUP BY NOSORT | | 1 | 60 | 6 (0)| 00:00:01 |
|* 2 | TABLE ACCESS BY INDEX ROWID| XXGL_NOP_WORKINGS | 1 | 60 | 6 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | XXGL_NOP_WORKINGS_N02 | 1 | | 2 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - filter("PERIOD_NAME"=:P_PERIOD AND "LEDGER_ID"=TO_NUMBER(:P_LEDGER_ID))
3 - access("BATCH_ID"=TO_NUMBER(:GLOBAL_BATCH_ID))

Note
-----
- dynamic sampling used for this statement

20 rows selected.


Thanks Karim.
Re: Sql statement taking very time [message #547038 is a reply to message #547036] Sun, 11 March 2012 23:11 Go to previous messageGo to next message
BlackSwan
Messages: 23029
Registered: January 2009
Senior Member
It would be helpful if you followed Posting Guidelines - http://www.orafaq.com/forum/t/88153/0/

SELECT currency_code 
FROM   xxgl_nop_workings 
WHERE  1 = 1 
       AND ledger_id = :B3 
       AND period_name = :B2 
       AND batch_id = :B1 
GROUP  BY currency_code 
ORDER  BY currency_code; 


Since NOBODY can optimize SQL just by looking at it, we need a few more details.
http://www.orafaq.com/forum/m/433888/136107/#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

[Updated on: Sun, 11 March 2012 23:12]

Report message to a moderator

Re: Sql statement taking very time [message #547097 is a reply to message #547038] Mon, 12 March 2012 04:53 Go to previous messageGo to next message
cookiemonster
Messages: 11177
Registered: September 2008
Location: Rainy Manchester
Senior Member
Did you take that explain plan from the production DB or dev/test one?
I suspect the later.
If so, it really needs to be taken from production.
Re: Sql statement taking very time [message #547098 is a reply to message #547097] Mon, 12 March 2012 04:58 Go to previous messageGo to next message
Osafa
Messages: 14
Registered: July 2011
Location: Malaysia , KL
Junior Member
I took the explain from the production db.
Re: Sql statement taking very time [message #547100 is a reply to message #547098] Mon, 12 March 2012 05:05 Go to previous messageGo to next message
cookiemonster
Messages: 11177
Registered: September 2008
Location: Rainy Manchester
Senior Member
In that case your DB statistics are horribly wrong.
Oracle thinks that table is pretty much empty and the query will take no time at all.
Re: Sql statement taking very time [message #547105 is a reply to message #547100] Mon, 12 March 2012 05:35 Go to previous messageGo to next message
Osafa
Messages: 14
Registered: July 2011
Location: Malaysia , KL
Junior Member
Yeah, the table is emptied once the procedure is complete, and I ran this explain after the concurrent is completed normally.In the same package the table is inserted with data from different tables and then this select statement is fired on the xxmbb_nop_workings table which is finally deleted.
Re: Sql statement taking very time [message #547106 is a reply to message #547100] Mon, 12 March 2012 05:35 Go to previous messageGo to next message
Osafa
Messages: 14
Registered: July 2011
Location: Malaysia , KL
Junior Member
Yeah, the table is emptied once the procedure is complete, and I ran this explain after the concurrent is completed normally.In the same package the table is inserted with data from different tables and then this select statement is fired on the xxmbb_nop_workings table which is finally deleted.
Re: Sql statement taking very time [message #547108 is a reply to message #547106] Mon, 12 March 2012 05:37 Go to previous messageGo to next message
cookiemonster
Messages: 11177
Registered: September 2008
Location: Rainy Manchester
Senior Member
How many rows do you insert?
Re: Sql statement taking very time [message #547110 is a reply to message #547108] Mon, 12 March 2012 05:38 Go to previous messageGo to next message
Roachcoach
Messages: 1224
Registered: May 2010
Location: UK
Senior Member
And is it a consistent number? Is there a possibility you had an uncommonly large volume one day?
Re: Sql statement taking very time [message #547111 is a reply to message #547110] Mon, 12 March 2012 05:46 Go to previous messageGo to next message
Osafa
Messages: 14
Registered: July 2011
Location: Malaysia , KL
Junior Member
This concurrent runs once a month, however i am not sure of the no. of rows that are inserted into this table, but definitely huge As when I tested the same in uat the file generated was of 4kb while that in prod is of 12mb. This problem has occured now only as it(this concurrent) has been there for almost a year without any issues. Its only in the last run that the user complained.
Re: Sql statement taking very time [message #547112 is a reply to message #547108] Mon, 12 March 2012 05:47 Go to previous message
Osafa
Messages: 14
Registered: July 2011
Location: Malaysia , KL
Junior Member
Not sure of the number of rows being inserted.
Previous Topic: Disk i/o
Next Topic: Oracle sequence performance
Goto Forum:
  


Current Time: Sun Nov 23 05:34:52 CST 2014

Total time taken to generate the page: 0.05192 seconds