I am an Oracle Database Specialist in Singapore.
Please note that this site uses cookies.
15 May, 2007
SQL Statement Execution Times
Daniel Fink has posted a blog entry about how Execution Statistics for long running SQLs might not be visible in StatsPack reports if the SQLs end after the StatsPack Snapshots. The general advice is to NOT run StatsPack Snapshots at long intervals ("intervals of 8 hours are meaningless" we frequently hear). However, if there was a long running SQL that began before the first StatsPack Snapshot and ended after the last Snapshot, that SQL wouldn't be seen in the StatsPack Report at all. You would be left wondering "if the server was so busy [and 'sar' did show 'busy'ness] why don't I see any significant SQLs in the Report". You might well miss the SQLs accounting for most of the buffer gets, disk reads, CPU time.
4 comments:
Great tip. Thanks.
Sometime ago we had a SQL that was hanging while running it. The same query ran OK for a different set of data, while it was hanging for a specific set of data. To be precise for certain dates it was hanging. We knew the data for the problem period was more by around 10 fold. But the thing was it was not taking 10x or 20x as long, not even 100x. It was just hanging.
We could not figure out what it was doing. We checked the v$sql, wait events (wait events showed it was reading block after block but we did not know why it had to do that; some other times nothing useful was gotten from there), statspack.
When nothing worked, we tried 10046ing it. The same case as you are talking about the statspack happened there. Because the query also hung (for hours and hours, even after 16 hours, with Logical I/Os getting into 10s of millions) the 10046 trace file did NOT have any useful information. The last update to the trace file happened when we just started the query. 10046 looked like another hyped up thing.
Finally we had to rip the query out and split it into multiple queries and we started getting results.
Related question: Daniel mentions in that blog that it applies only to Pre 10.2 versions. Does it apply to statspack of <= 10.1? Does the *statspack* of 10.2 run good taking into account the currently running unfinished SQLs? Or are we talking about ADDM/AWR in 10.2?
I think that you cannot use ADDM unless you have a Enterprise version of Oracle.
When you say "wait events showed it was reading block after block" did you see which type of wait was it -- "db file sequential read" (generally single block reads) or "db file scattered read" (multiblock reads). The 10046 trace should be enabled in the session before the particular SQL is executed (ie begins). If you had run the 10046 trace at level 8, it would have shown you each wait event.
Hi Hemant
What is the difference between
disconnect session and kill session ?
Which one is more powerful?
This blog post is not about KILL SESSION and/or DISCONNECT SESSION.
Please do not expect free support.
Read the documentation at
http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/statements_2013.htm#i2053602
Post a Comment