**IMPORTANT NOTE - This applies only to 10gR1 and older databases **
I recently reviewed a couple of statspack reports from a 9i database to determine the differences between a 'good' and 'bad' period. After identifying several key differences (more logical reads, more redo, more disk reads), I set about looking at the underlying activity in the form of the sql statements. In the past, I have used this data to find statements consuming large amount of resource and causing contention issues with other processes. I must now question the accuracy of that approach.
At UKOUG, I learned that in releases prior to 10.2, the statistical information for a given sql statement was not updated until the successful completion of the session's execution of the statement. While I found that interesting, I did not grasp the real significance in regards to Statspack until just now.
Let's say that you have a high resource consumption query that runs 5 hours (and you are taking hourly snapshots). All of the sql statistics (disk reads, buffer gets, time) will be reported for the snapshot immediately following the statement completion (unless it gets purged from the shared pool before that can happen...but that is another issue for another blog). Even though the statement was consuming resource during snaps 1, 2, 3 and 4, it is only reported for snap 5! If you are trying to diagnose issues that occur during the other snapshots, you will completely miss this query...and focus on queries that may have completed well before the snapshot and had little if any impact on the time period being examined!
This does not mean that all SQL metrics should be ignored (despite the title of this blog). Statements with multiple executions might be accurately reflected if they are of reasonably short duration (shorter than your report window). And the bottom line is that a query that runs for 5 hours to process less than 20 rows needs to be looked at more closely.
As with all tools, you need to understand the shortcomings. That does not make the tool useless, just not a wonder tool.
Thursday, March 22, 2007
Subscribe to:
Post Comments (Atom)
Followers
7 comments:
Daniel,
Good point. I find Statspack's SQL reports less useful in DW environments and batch processing environments exactly for that reason.
However, Statspack of an OLTP database does not suffer from that problem.
You title is indeed somewhat over-exaggerated. Generally, successful usage of Statspack often requires good prior knowledge of the application. Otherwise, it often leads to false assumptions and conclusions. This is just one of the examples.
Cheers,
Alex
Yes, the title is definitely over exaggerated (on purpose...one must have a catchy title to get people's attention).
Actually, the recent example is from an OLTP database, one with some very long running processes.
Dan
Daniel,
I find that less of a problem, when you are prepared for it. The trick I use is to not do a bad time vs. good time comparisions at all: I just plot some of the more interesting statistics and waits on a chart and look for (unexpected, extraordinary) spikes. Then I generate a report for that hour only - any long runner has dumped lots of stats in this snapshot, so it is sure to float to the top (especially in an OLTP system, where long runners are rare). (Some additional clues come from elapsed and/or CPU times greater than the snapshot duration for a single execution). Assuming, that is, that the long runner has not ben forced out of the shared pool after it completed.
The rest is routine - I run a sprepsql to get details, and I plot a history of its past executions to get a feeling whether it might be a recurring problem or just an accident, take a hard look at the plan, and so on...
Cheers,
Flado
Even grown men can sometimes be fooled by StatsPack reports. The general advice to run StatsPack for shorter intervals ("StatsPack snapshots 8 hours apart are meaningless" is a frequent comment) does not also include the hint that long running SQLs -- more importantly their CPU, Disk Reads and Buffer Gets statistics -- will NOT appear in StatsPack reports.
Furthermore, in a very busy system, SQLs that get aged out before the StatsPack Snapshot runs also would not get reflected in the Report.
Just check OTN and 10gR2 documnet, "Statistics displayed in V$SQL are normally updated at the end of query execution. However, for long running queries, they are updated every 5 seconds. This makes it easy to see the impact of long running SQL statements while they are still in progress."
Very good improvement
As I test,that "V$SQL enchancement" only work under statistics_level set to typical or all.
If statistics_level=basic,it's same as 9i etc.
@Vladimir:
Do you use special tools for this ? You run queries directly on Statspack ? It sounds exactly like how I do performance analysis. I use a 'tool' called Phat4Oracle.
Post a Comment