OraFAQ Forum: Performance Tuning » Sql statement taking very time

Home » RDBMS Server » Performance Tuning » Sql statement taking very time (Oracle E-Business Suite R12)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Sql statement taking very time [message #546861]

Fri, 09 March 2012 06:15

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.

Report message to a moderator

Re: Sql statement taking very time [message #546863 is a reply to message #546861]

Fri, 09 March 2012 06:28

Michel Cadot
Messages: 68731
Registered: March 2007
Location: Saint-Maur, France, https...

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

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?

Report message to a moderator

Re: Sql statement taking very time [message #546867 is a reply to message #546865]

Fri, 09 March 2012 06:37

Michel Cadot
Messages: 68731
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Quote:

Why are you using 1=1 condition?

Automatic query generation.

Regards
Michel

Report message to a moderator

Re: Sql statement taking very time [message #546873 is a reply to message #546867]

Fri, 09 March 2012 08:39

Bill B
Messages: 1971
Registered: December 2004

Senior Member

can you show the explain plan for the query?

Report message to a moderator

Re: Sql statement taking very time [message #547036 is a reply to message #546873]

Sun, 11 March 2012 23:05

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.

Report message to a moderator

Re: Sql statement taking very time [message #547037 is a reply to message #546865]

Sun, 11 March 2012 23:10

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.

Report message to a moderator

Re: Sql statement taking very time [message #547038 is a reply to message #547036]

Sun, 11 March 2012 23:11

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

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

cookiemonster
Messages: 13962
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.

Report message to a moderator

Re: Sql statement taking very time [message #547098 is a reply to message #547097]

Mon, 12 March 2012 04:58

Osafa
Messages: 14
Registered: July 2011
Location: Malaysia , KL

Junior Member

I took the explain from the production db.

Report message to a moderator

Re: Sql statement taking very time [message #547100 is a reply to message #547098]

Mon, 12 March 2012 05:05

cookiemonster
Messages: 13962
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.

Report message to a moderator

Re: Sql statement taking very time [message #547105 is a reply to message #547100]

Mon, 12 March 2012 05:35

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.

Report message to a moderator

Re: Sql statement taking very time [message #547106 is a reply to message #547100]

Mon, 12 March 2012 05:35

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.

Report message to a moderator

Re: Sql statement taking very time [message #547108 is a reply to message #547106]

Mon, 12 March 2012 05:37

cookiemonster
Messages: 13962
Registered: September 2008
Location: Rainy Manchester

Senior Member

How many rows do you insert?

Report message to a moderator

Re: Sql statement taking very time [message #547110 is a reply to message #547108]

Mon, 12 March 2012 05:38

Roachcoach
Messages: 1576
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?

Report message to a moderator

Re: Sql statement taking very time [message #547111 is a reply to message #547110]

Mon, 12 March 2012 05:46

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.

Report message to a moderator

Re: Sql statement taking very time [message #547112 is a reply to message #547108]

Mon, 12 March 2012 05:47

Osafa
Messages: 14
Registered: July 2011
Location: Malaysia , KL

Junior Member

Not sure of the number of rows being inserted.

Report message to a moderator

Previous Topic:	Disk i/o
Next Topic:	Oracle sequence performance

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sat Jan 25 10:51:07 CST 2025