Statspack Still Works in 11g

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:

  1. It is very, very similar to AWR
  2. It’s Open Source (sort of) – AWR is wrapped, but Statspack isn’t. You can modify it to suit your own taste. 
  3. It’s still free!

 
The worst things about Statspack:
 

  1. It has a few issues in 11g
  2. 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:

Top 5 Timed Events                                                    Avg %Total
~~~~~~~~~~~~~~~~~~                                                   wait   Call
Event                                            Waits    Time (s)   (ms)   Time
----------------------------------------- ------------ ----------- ------ ------
shared server idle wait                              5         150  30008   78.7
CPU time                                                        26          13.7
direct path read                                 5,168          12      2    6.4
Data file init write                                39           1     29     .6
os thread startup                                    5           0     79     .2
          -------------------------------------------------------------
 
...
 
SQL> insert into stats$idle_event values ('shared server idle wait');
 
...
 
Top 5 Timed Events                                                    Avg %Total
~~~~~~~~~~~~~~~~~~                                                   wait   Call
Event                                            Waits    Time (s)   (ms)   Time
----------------------------------------- ------------ ----------- ------ ------
CPU time                                                        26          64.4
direct path read                                 5,168          12      2   30.1
Data file init write                                39           1     29    2.8
os thread startup                                    5           0     79    1.0
control file sequential read                     3,986           0      0     .6
          -------------------------------------------------------------

 

 
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)

 
====AWR====
 
  Elapsed      CPU                  Elap per  % Total
  Time (s)   Time (s)  Executions   Exec (s)  DB Time    SQL Id
---------- ---------- ------------ ---------- ------- -------------
       884        575           20       44.2    75.2 ftgja8q5449mb
Module: sqlplus@homer (TNS V1-V3)
select avg(pk_col) from kso.skew where col1 > :"SYS_B_0"
 
 
====Statspack====
 
  Elapsed                Elap per            CPU                        Old
  Time (s)   Executions  Exec (s)  %Total   Time (s)  Physical Reads Hash Value
---------- ------------ ---------- ------ ---------- --------------- ----------
    883.84           20      44.19   75.3     575.35       3,217,304 1392465936
Module: sqlplus@homer (TNS V1-V3)
select avg(pk_col) from kso.skew where col1 > :"SYS_B_0"

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.

Please feel free to leave me a comment.

16 Comments

  1. I would rather say “IS MAINTAINED” and “HAS EVOLVED EVEN IN 11GR2″
    8i : 28 tables
    9i : 42 tables
    10g : 67 tables
    11GR2 : 72 tables

    patrick boulay
    Oracle Dba ( Zion-dba.com )

  2. osborne says:

    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).

    Kerry

  3. jpiwowar says:

    Hi Kerry,

    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. ;-)

    Regards,

    John P.

  4. osborne says:

    John,

    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.

    Thanks for the comment.

    Kerry

  5. […] Osborne saved my sanity with an 18-month old post, as I tried to figure out why a ‘shared server idle wait event’ was skewing my 11g Statspack […]

  6. Kubilay says:

    Thanks Osborne

    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.

    Very useful!

    Kubilay

  7. Anu says:

    Hi,
    I am upgrading from 9i to 11gR2 and am working on collecting snapshots with statspack. My question is if statspack is still there in 11gR2?

  8. osborne says:

    Yes it still works in 11.2.

    Kerry

    • Anu says:

      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.

  9. osborne says:

    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.

    Kerry

  10. Kaarlo says:

    Thanks for this useful blog. We’re considering using STATSPACK instead of AWR.
    In 11gR2 ‘shared server idle wait event’ is already included in STATS$IDLE_EVENT.

  11. […] extra-cost “diagnostic pack” license. But similar queries could be written from free statspack or S-ASH […]

  12. […] 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 […]

  13. […] there is some good news. Good old Statspack still ships with 11g and the source is exposed. So it is easy to change it to capture the predicates. So I did just […]

  14. Andriy Dmytrenko says:

    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;

  15. Swiss Steve says:

    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;

    and the problem goes away :)

Leave a Reply