OraFAQ Forum: SQL & PL/SQL » Same Query taking significantly different time on different days

Home » SQL & PL/SQL » SQL & PL/SQL » Same Query taking significantly different time on different days (Oracle 11g)

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

Same Query taking significantly different time on different days [message #636061]

Wed, 15 April 2015 14:51

saurabh.sha
Messages: 15
Registered: March 2015
Location: india

Junior Member

All,

I have a query which runs overnight and taking significantly different time on different days, i did generate the comparison AWR report for 2 days 10th and 11th on 10th it took 1 hour and on 11th it took 2 hours, the data seem to be consistent.

i have attached AWR diff sheet, due to size restriction could attach all, also have attached in txt format and need to be saved in html format to view correctly.

Please can you advise if you see anything unusual on 2 days, i couldn't see anything obvious.

Thanks
Saurabh

Attachment: awr1.txt
(Size: 620.70KB, Downloaded 1181 times)

Report message to a moderator

Re: Same Query taking significantly different time on different days [message #636065 is a reply to message #636061]

Wed, 15 April 2015 14:54

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

consider to ALTER SESSION SET SQL_TRACE=TRUE
then process trace file using tkprof & post results back here

Report message to a moderator

Re: Same Query taking significantly different time on different days [message #636066 is a reply to message #636061]

Wed, 15 April 2015 15:07

John Watson
Messages: 8929
Registered: January 2010
Location: Global Village

Senior Member

Which query is it? There are several in that report. You need to generate the AWR SQL report for the query (run the awrsqrpt.sql script) for both the fast and the slow runs.

Report message to a moderator

Re: Same Query taking significantly different time on different days [message #636200 is a reply to message #636066]

Sun, 19 April 2015 13:09

Kevin Meade
Messages: 2103
Registered: December 1999
Location: Connecticut USA

Senior Member

In addition, you may want to check some basics.

1. look at the logical gets between the two runs for this one query (if possible).  Does the second do twice as many as the first?
2. check the query plans to see if they differ.  If they do then this is a clue to the performance change which needs researching.

If the query plans are the same and the amount of data is the same then your timings may simply be due to other workloads taking resources away from your query.

If in addition to the above, the logical gets are significantly larger even though you have processed the same amount of data, then maybe someone is updating a table while you are using it and you have been forced to do lots of "consistent gets" row reconstruction.

These are just some thoughts. Kevin

Report message to a moderator

Previous Topic:	how to decide when to go for local vs global partitioned index on a table
Next Topic:	DBMS_CRYPTO

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Apr 19 21:43:58 CDT 2024