Execution time is faster on 2nd and subsequent runs ? [message #327713] |
Tue, 17 June 2008 06:20  |
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 #327724 is a reply to message #327721] |
Tue, 17 June 2008 06:36   |
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 #327732 is a reply to message #327713] |
Tue, 17 June 2008 07:00   |
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
[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 #328224 is a reply to message #328218] |
Thu, 19 June 2008 07:06   |
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   |
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   |
Frank
Messages: 7901 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 #328392 is a reply to message #328385] |
Fri, 20 June 2008 00:58   |
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 ?

[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   |
Frank
Messages: 7901 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  |
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
|
|
|