Kerry Osborne's Oracle Blog

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.