Home » SQL & PL/SQL » SQL & PL/SQL » Execution time is faster on 2nd and subsequent runs ? (ORACLE 9i)
Execution time is faster on 2nd and subsequent runs ? [message #327713] Tue, 17 June 2008 06:20 Go to next message
NewLife
Messages: 170
Registered: April 2008
Senior Member
Hi,

Is it true that once you run a query , it executes faster the 2nd time ?

If YES, then how much is the variance ?

I am pondering about this because I executed a package in QA environment, and it took 45 minutes to execute, BUT when i ran the same in DEV environment, it takes long, (the package is still executing), so is there any reasoning to say that it may run faster the 2nd and subsequent times ?

Note---DEV and QA databases look alike
Re: Execution time is faster on 2nd and subsequent runs ? [message #327715 is a reply to message #327713] Tue, 17 June 2008 06:21 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Is it true that once you run a query , it executes faster the 2nd time ?

Sometimes yes, sometimes no.

Regards
Michel
Re: Execution time is faster on 2nd and subsequent runs ? [message #327716 is a reply to message #327715] Tue, 17 June 2008 06:22 Go to previous messageGo to next message
NewLife
Messages: 170
Registered: April 2008
Senior Member
Can it vary to about a time difference of 2 to 3 hours ?

If Yes, then why is it ? Surprised
Re: Execution time is faster on 2nd and subsequent runs ? [message #327719 is a reply to message #327716] Tue, 17 June 2008 06:29 Go to previous messageGo to next message
NewLife
Messages: 170
Registered: April 2008
Senior Member
jagannathkiran wrote on Tue, 17 June 2008 16:52
Can it vary to about a time difference of 2 to 3 hours ?

If Yes, then why is it ? Surprised


I mean for executing a package
Re: Execution time is faster on 2nd and subsequent runs ? [message #327721 is a reply to message #327716] Tue, 17 June 2008 06:32 Go to previous messageGo to next message
rajatratewal
Messages: 507
Registered: March 2008
Location: INDIA
Senior Member
Quote:

jagannathkiran wrote on Tue, 17 June 2008 06:20

Note---DEV and QA databases look alike



What do you mean by this??

Are the Database Parameter Settings Are Same on both DB.

Regards,
Rajat Ratewal
Re: Execution time is faster on 2nd and subsequent runs ? [message #327724 is a reply to message #327721] Tue, 17 June 2008 06:36 Go to previous messageGo to next message
NewLife
Messages: 170
Registered: April 2008
Senior Member
rajatratewal wrote on Tue, 17 June 2008 17:02
Quote:

jagannathkiran wrote on Tue, 17 June 2008 06:20

Note---DEV and QA databases look alike



What do you mean by this??

Are the Database Parameter Settings Are Same on both DB.




I am not sure about that, but they were both refreshed from production about a month ago
Re: Execution time is faster on 2nd and subsequent runs ? [message #327728 is a reply to message #327724] Tue, 17 June 2008 06:48 Go to previous messageGo to next message
rajatratewal
Messages: 507
Registered: March 2008
Location: INDIA
Senior Member
jagannathkiran i asked this question because i think that you have just refreshed the schema not Database.

And your Database Parameter Settings are different.

Like if say TEMP tablespace size is not same in both Databases.

And by the way which package is taking 45 minutes to complete.

Have you tuned it properly.

Regards,
Rajat Ratewal

[Updated on: Tue, 17 June 2008 07:17] by Moderator

Report message to a moderator

Re: Execution time is faster on 2nd and subsequent runs ? [message #327732 is a reply to message #327713] Tue, 17 June 2008 07:00 Go to previous messageGo to next message
NewLife
Messages: 170
Registered: April 2008
Senior Member
Hi Rajat,

I do not have that much knowledge to figure out if the DB parameter settings are different !!

If that is the case, then probably i have to wait and see on my 2nd run.

Quote:
And by the way which package is taking 45 minutes to complete.

Have you tuned it properly.



Since I am an amateur in PL/SQL , i have tried my best to tune the package to my capability, but if you are intersted , i can share it with you Smile

[Updated on: Tue, 17 June 2008 07:18] by Moderator

Report message to a moderator

Re: Execution time is faster on 2nd and subsequent runs ? [message #327739 is a reply to message #327732] Tue, 17 June 2008 07:18 Go to previous messageGo to next message
rajatratewal
Messages: 507
Registered: March 2008
Location: INDIA
Senior Member
jagannathkiran wrote on Tue, 17 June 2008 07:00
Quote:
I do not have that much knowledge to figure out if the DB parameter settings are different !!


Then ASK your DBA for this??


Regards,
Rajat Ratewal
Re: Execution time is faster on 2nd and subsequent runs ? [message #327741 is a reply to message #327739] Tue, 17 June 2008 07:22 Go to previous messageGo to next message
NewLife
Messages: 170
Registered: April 2008
Senior Member
Well, He has left for the day, I guess I can figure that out tomorrow and get back to you.

Thanks,
Jaggy
Re: Execution time is faster on 2nd and subsequent runs ? [message #328074 is a reply to message #327713] Wed, 18 June 2008 18:09 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8636
Registered: November 2002
Location: California, USA
Senior Member
Any queries that are run are stored in the SGA until it gets full and the older ones are bumped out. If the same queries are re-run while they are still in the SGA, then they do not have to be re-parsed and can therefore save some time by skipping that step and run faster. There me also be other items that are cached and such that can be re-used and save time. In general, yes, things run quicker the second and subsequent times.

Re: Execution time is faster on 2nd and subsequent runs ? [message #328092 is a reply to message #328074] Wed, 18 June 2008 22:58 Go to previous messageGo to next message
rajatratewal
Messages: 507
Registered: March 2008
Location: INDIA
Senior Member
Hi Barbara,
jagannathkiran has created a new topic <How to find out if a
session is hung?> for the same problem.

Needed your expert comments on the problem.

Regards,
Rajat Ratewal

Re: Execution time is faster on 2nd and subsequent runs ? [message #328096 is a reply to message #328092] Wed, 18 June 2008 23:07 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
rajatratewal wrote on Wed, 18 June 2008 20:58
Hi Barbara,
jagannathkiran has created a new topic <How to find out if a
session is hung?> for the same problem.

Needed your expert comments on the problem.

Regards,
Rajat Ratewal



Do you know the SID of the session that is considered "hung"?
Re: Execution time is faster on 2nd and subsequent runs ? [message #328105 is a reply to message #328096] Wed, 18 June 2008 23:32 Go to previous messageGo to next message
NewLife
Messages: 170
Registered: April 2008
Senior Member
Actually I have killed that session for now, but i am going to run the same package in 10 minutes, so once i start running that package i shall tell you the new session ID, PLEASE WAIT

PLEASE refer this topic http://www.orafaq.com/forum/t/120524/122576/

[Updated on: Wed, 18 June 2008 23:38]

Report message to a moderator

Re: Execution time is faster on 2nd and subsequent runs ? [message #328107 is a reply to message #327713] Wed, 18 June 2008 23:37 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
select * from v$sess_io where sid = <sid_hung_session>;
invoke above SQL repeatedly.
If the SQL is doing work, the values will change over time.
If the SQL is really, really "hung" the values will never change.

[Updated on: Wed, 18 June 2008 23:38] by Moderator

Report message to a moderator

Re: Execution time is faster on 2nd and subsequent runs ? [message #328111 is a reply to message #328107] Wed, 18 June 2008 23:46 Go to previous messageGo to next message
NewLife
Messages: 170
Registered: April 2008
Senior Member
Ok, thanks for the suggestion, I shall check for that in around an hour, because i am stuck up in some other misc work now, I shall revert ASAP

Hmmm i feel the undo file is missing in this database, the DBA has told me to log off this database for a while, so once the Databse is UP, i shall revert to you all Smile

[Updated on: Thu, 19 June 2008 00:42]

Report message to a moderator

Re: Execution time is faster on 2nd and subsequent runs ? [message #328201 is a reply to message #328107] Thu, 19 June 2008 06:17 Go to previous messageGo to next message
NewLife
Messages: 170
Registered: April 2008
Senior Member
select * from v$sess_io where sid = 16;


1. The "CONSISTENTS_GETS" columns value is increasing every time i run the above command, so does it mean that it is still fetching data ?

2. Why is it taking so long ?

3. Is there any problem with the database ?

Re: Execution time is faster on 2nd and subsequent runs ? [message #328208 is a reply to message #328201] Thu, 19 June 2008 06:37 Go to previous messageGo to next message
rajatratewal
Messages: 507
Registered: March 2008
Location: INDIA
Senior Member
Because you are picking large volume of data.i told you yesterday??

Regards,
Rajat Ratewal
Re: Execution time is faster on 2nd and subsequent runs ? [message #328214 is a reply to message #328208] Thu, 19 June 2008 06:50 Go to previous messageGo to next message
NewLife
Messages: 170
Registered: April 2008
Senior Member
But then There is in fact more data in QA environment and it executes to completion, here there are fewer records....it SHOULD ATLEAST complete executing right ? Sad

[Updated on: Thu, 19 June 2008 07:02]

Report message to a moderator

Re: Execution time is faster on 2nd and subsequent runs ? [message #328218 is a reply to message #328214] Thu, 19 June 2008 07:00 Go to previous messageGo to next message
rajatratewal
Messages: 507
Registered: March 2008
Location: INDIA
Senior Member
Boss try to tune your sql first.

Why you are not concentrating on the problem.

We don't have your test database.Only god knows what you have configured their.

We can only suggest.
What can be wrong??
What you should try??


Regards,
Rajat Ratewal

Re: Execution time is faster on 2nd and subsequent runs ? [message #328224 is a reply to message #328218] Thu, 19 June 2008 07:06 Go to previous messageGo to next message
NewLife
Messages: 170
Registered: April 2008
Senior Member
Yeah, you are correct, I feel there is some probelm with my TEST environment,because i got some strange errors this morning in my test env all off a sudden like this for ex :

*** SCRIPT START :  Session:#(#1)   6/19/2008 10:35:50 AM *** 
Processing ...
update [Removed]
update av_activity set memo_sent = '18'
*
ORA-01116: error in opening database file 22
ORA-01110: data file 22: '/san/vol1/oradata/#/ts_undo_001.dbf'
ORA-27041: unable to open file
SVR4 Error: 2: No such file or directory
Additional information: 3
*** Script stopped due to error ***
*** SCRIPT END :  Session:#(1)   6/19/2008 10:35:50 AM *** 




[Sensitive information replaced by # by MC at OP's request]

[Updated on: Thu, 08 November 2012 03:17] by Moderator

Report message to a moderator

Re: Execution time is faster on 2nd and subsequent runs ? [message #328349 is a reply to message #327713] Thu, 19 June 2008 17:06 Go to previous messageGo to next message
rdebruyn
Messages: 17
Registered: June 2008
Location: Ottawa
Junior Member
First, the difference in running SQL for the first time is mostly about parsing the SQL. This can add a small amount of time like 400ms the first time and 300ms after that.

Your in a different ballgame than that. Your comparing the results of one sandbox against the other. The sandboxes would have to be exacly the same with the same activity to have any real expectation of consisitency.

Is the test database doing a backup while you're testing? Does someone have a record lock that's hanging your session? Do the tables have similar amounts of data? Do all indexes exist the same in both environments? Have the tables been analyzed?

Try adding
raise too_many_rows;
at an early stage in the code and run it. See how fast it gets to the point of raising the exception. Move the statement further and further into the code until you find a point that stalls. Try and identify the statement stalling and check it out.
Re: Execution time is faster on 2nd and subsequent runs ? [message #328379 is a reply to message #328349] Fri, 20 June 2008 00:19 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
rdebruyn wrote on Fri, 20 June 2008 00:06
First, the difference in running SQL for the first time is mostly about parsing the SQL. This can add a small amount of time like 400ms the first time and 300ms after that.

That's not entirely true. The first run can take considerably longer than consecutive runs, if the first run gets datablocks from disk, where consecutive runs get them from the shared pool.
This is especially true on development and testing systems, where there is not a lot of action, data-wise so the person executing the queries is the only one filling up the shared pool.
Re: Execution time is faster on 2nd and subsequent runs ? [message #328385 is a reply to message #328379] Fri, 20 June 2008 00:41 Go to previous messageGo to next message
rdebruyn
Messages: 17
Registered: June 2008
Location: Ottawa
Junior Member
There can be more difference, but how much are you suggesting. Hours? I don't think so!
Re: Execution time is faster on 2nd and subsequent runs ? [message #328392 is a reply to message #328385] Fri, 20 June 2008 00:58 Go to previous messageGo to next message
NewLife
Messages: 170
Registered: April 2008
Senior Member
I am sorry to divert from the track, but here is another issue..

PLEASE SEE attachment.

I ran the package yesterday, and the attach shows that it is till running .

1. The Connection got lost around midnight, when i had locked my computer and gone home, so there is now ay that this session is active.

2. But when i try to do a simple
select * from ADM.MEMO_LOG
within that "supposed to be inactive" session, a message pops up saying that "Current Session is busy executing a task", PLEASE SEE ATTACHMENT

3. How do i identify the problem ?
./fa/10500/0/


[mod-edit: image inserted into message body by bb]
[mod-edit: image modified to remove sensitive information by mc]

[Updated on: Thu, 08 November 2012 02:10] by Moderator

Report message to a moderator

Re: Execution time is faster on 2nd and subsequent runs ? [message #328393 is a reply to message #328385] Fri, 20 June 2008 00:59 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Not hours, but considerably more than 300-400 ms
It all depends on the amount of datablocks that need to be read and the available memory to store these.
Normally, the difference would not exceed minutes, so I don't say that this is what the original poster is running into. Just wanted to add some nuance to your statement.
Re: Execution time is faster on 2nd and subsequent runs ? [message #328398 is a reply to message #328392] Fri, 20 June 2008 01:24 Go to previous message
rdebruyn
Messages: 17
Registered: June 2008
Location: Ottawa
Junior Member
jagannathkiran wrote on Fri, 20 June 2008 01:58

I ran the package yesterday, and the attach shows that it is till running .

1. The Connection got lost around midnight, when i had locked my computer and gone home, so there is now ay that this session is active.




Just because your client closes or loses a connection, doesn't mean the database stops what that session is doing. Unless you killed the seesion on the database server, it could very well still be running.

I can start an update that will take 20 minutes in SQLPlus and then close SQLPlus after 2 minutes. The database is still working on the update for 18 more minutes.

PS I didn't open the image. I'll look at it tommorow at work.

[Updated on: Fri, 20 June 2008 01:26]

Report message to a moderator

Previous Topic: varray, snapshot
Next Topic: Leave Calculation (merged)
Goto Forum:
  


Current Time: Sun Dec 11 04:23:00 CST 2016

Total time taken to generate the page: 0.13781 seconds