Oracle 10g came with a new version of Statspack – they called it AWR, but basically it’s the same old estat/bstat report. There are of course a few new things in it and the snapshot process is automatically configured to collect data once per hour when you create a 10g instance. Unfortunately, this “new” feature is part of the separately licensed Diagnostics Pack, despite the fact the there is no easy way to disable the data collection. I posted earlier on Oracle Management Packs and associated licensing here, by the way.

But Statspack is still available and still runs fine if you choose to install it. As a matter of fact, it still exists in 11gR1 and has in fact been updated to accommodate some of the data dictionary changes in 11g. It looks a little like an after thought though as there are a couple of problems with it. The script to set it up is still in the $ORACLE_HOME/rdbms/admin directory and it’s still called spcreate.sql. This script creates the PERFSTAT user and the tables and necessary code objects.

The Statspack report looks very similar to the AWR report. It has basically the same Header Section, the same Wait Events Section, the same SQL Statements Ordered By XXX Sections, the same Tablespace and File I/O Sections, the same Buffer Pool Advisory Section, and the same Non-Default INIT.ORA Parameters Section at the bottom. Hey, this is the same thing!

The best things about Statspack:

It is very, very similar to AWR

It’s Open Source (sort of) – AWR is wrapped, but Statspack isn’t. You can modify it to suit your own taste.

It’s still free!

The worst things about Statspack:

It has a few issues in 11g

Although it is still being maintained, it doesn’t appear to be a high priority

Things that really need fixing in 11.1.0.7:

First, there is a new idle event in 11.1.0.7 that somehow didn’t make it into STATS$IDLE_EVENT. That event is “shared server idle wait”. It may actually be in the initial release (11.1.0.6) but I don’t have one handy to check. At any rate, this event needs to be added or the Wait Event Profile will be dominated by this idle event like this:

Second, the Statspack report throws an errror when attempting to truncate STATS$TEMP_SQLSTATS. In addition to the error message in the report output, the SQL sections had duplicate entries as a result of the failure to clean out this temp table. The fix is pretty simple. Just edit the sprepins.sql file in the $ORACLE_HOME/rdbms/admin directory. Locate the truncate command and retype it (apparently there is a non-displayable character embedded in the table name).

truncate table STATS$TEMP_SQLSTATS
*
ERROR at line 1:
ORA-00942: table or view does not exist

Third, plan data is not included in standard AWR or Statspack reports, but the collection routines for AWR do collect that information. If you want Statspack to collect that sort of information you’ll need to change the level from the default (5) to something higher 6-10. This will allow you to do forensic type investigation similar to what we can do with ASH (also part of the Diagnostic Pack).

Finally, just an FYi, Statspack does not store the SQL_ID of the statements it reports on. It was written before SQL_ID showed up and so it used HASH_VALUE. In 10g, a new HASH_VALUE was introduced and the old version was stuck in a column called OLD_HASH_VALUE. So that’s what the newer versions of Statspack saves and reports while AWR uses the newer SQL_ID. (I’ll leave it as an exercise for the reader to figure out how to modify the snapshot process to store the SQL_ID so that it can be reported in the SQL Sections)

So if you are not licensed for the Diagnotics Pack, try Statspack again. I think you’ll find it works pretty well.

There’s are numerous web pages and posts on Statspack out there. Just google it. There is a good series of articles here on modifying statspack to suit your own needs. I haven’t tested any of his code, but the ideas are spot on.

Yes, it definitely has continued to “evolve” in 11gR2. There is very little difference between the standard AWR report and the standard Statspack report (although Statspack continues to use HASH_VALUE instead of SQL_ID to identify statements).

Just a quick note to thank you for this post. The “shared server idle wait” event in the Statspack reports on my recently-upgraded db were threatening to make me pull out what little hair I have left. ;-)

Glad to hear the post was useful. I have to admit that when I saw your comment I couldn’t remember having ever mentioned that wait event. One of the best things about blogging (as far as I’m concerned) is that it gives me a place to keep a record of “stuff” that I’ve worked on and that it is easily searchable.

This saved me time. I was going around and asking what can I use after the 11g upgrade to look at a performance issue, for free! Your post is re-assuring and pointing people to the right way, rather than blindly AWR, is nice to know there is a powerfull, free alternative.

Kerry,
Just wanted to let you know that I upgraded my databases from 9.2.0.6 to 11.2.0.1 last year and used perfstat to collect performance data. I spend a great deal of time on Perfstat and on upgrading it after the database was upgraded to 11gR2. The upgrade path was 9.2.0.6->9.2.08->11.2.0.1. I collected performance data at both 9.2.0.6 and 9.2.0.8 and also exported the perfstat schema before the upgrade to 9.2.0.8 and 11.2.0.1. I captured performance data with snap_level=>6 since I was most worried about execution plans changing(change from RBO to CBO). Anyway, after upgarding the statspack and importing it to 11.2.0.1 for comparison, I found that the statspack after importing, for some reason, was at level=>5 and thus lost all the execution plans. I was lucky this was test databases and then decided to use Stored Outlines to capture the execution plans and I was able to migrate these easily.

Can’t say I’ve actually tried moving statspack data from 9 to 11. That is a pretty odd behavior. You might want to open an SR to see if they know about that and there is a simple fix. I can’t imagine why it would move some but not all the statspack data. Thanks for the information.

[…] dividing them by the time that passed inbetween the snapshots (e.g., by using Statspack, which still works in 11g, btw). With ASH, Oracle implemented a mechanism of sampling system metrics every second. Those […]

It seems that the issue with truncation of STATS$TEMP_SQLSTATS not related to wrong typing but with that fact that report usually is being executed by other user than statspack owner. I’ve corrected it by appending the owner’s name:
truncate table PERFSTAT.STATS$TEMP_SQLSTATS;

Agreeing with Andriy:
Unless you run the spreport script as the user who owns the PERFSTAT tables, the error is in rdbms/admin/sprepins.sql
It is simply missing the schema name.
change the following line :
truncate table STATS$TEMP_SQLSTATS;
–>
truncate table perfstat.STATS$TEMP_SQLSTATS;