Posted Oct 22, 2008

Hidden treasures in Oracle's STATSPACK utility

By Steve Callan

Oracle 10gs Automatic Workload Repository, as part of Oracles Intelligence Infrastructure, works hand in hand with the Automatic Database Diagnostic Monitor to help you, the DBA, diagnose and remedy performance problems. Under the Intelligent Infrastructure, we see the following components:

Automatic Workload Repository

Automatic Maintenance Tasks

Server-Generated Alerts

Advisor Framework

The AWR is most closely aligned with what STATSPACK is in terms of being a repository for collecting statistics. Under automatic maintenance tasks, we have what Oracle does after dark: gather statistics. For server-generated alerts, you have the ability to set warning and threshold values for numerous items of interest. The Advisor Framework is where we find all of the advisors, such as the SQL Tuning Advisor, SQL Access Advisor.

So why re-visit STATSPACK when AWR, combined with ADDM, gets all the attention with respect to improving the manageability of a database? The main reasons why I feel it is noteworthy has to do time and money. Ill start with the money aspect.

Case I - Money

As mentioned in past articles, use of the repository and any of the packs (tuning and diagnostics, as in tuning pack and diagnostics pack) must be licensed. The packs serve as the main interface into the AWR, although information in the AWR can be accessed via supplied SQL scripts. If your license metric is based on 300 named users, then your licensing of a feature has a matching cost basis. In this example, you would essentially pay for 300 seats of the Tuning Pack, even though in reality, a small handful of users (e.g., DBAs) would be the actual users.

This cost, no doubt, can be expensive, but some of the pain is reduced through volume. The more you license, the better discount rate you get. The initial price you pay is the one that cuts the deepest because you have to pay for two elements: the basic license and the 22% maintenance fees. Going forward, you then (only) have the annual maintenance and support fee, which is also what pays for your CSI number so you can access My Oracle Support (the new name for MetaLink). If you have questions about the licensing model, I assure you this is an accurate description of what takes place. What it will actually cost you, however, is between you and your sales representative. If you want to use AWR-like features but cannot pony up the tens of thousands of dollars it takes to get compliant, what are your options (which presumably do not include using a third party tool such as Toad for Oracle, which now includes a warning message about using licensed features that Toad exposes on its interface)? The first level down which comes close to AWR is STATSPACK.

Case II  Time

Im going to stretch the meaning of time for this case. There is time related to your time on the job. What if you started a few years ago and cut your teeth on 10g, but are now looking for a new job and the versions used in the new place include not only 10g, but something prehistoric such as 9i? Not very likely youll run into 9i, you think? Quite a few E-Business Suite databases in the 11i family run on 9.2.0.5 or 6. STATSPACK is your only choice for something like AWR. Further, Oracle8i (in the 8.1.7 era) is still in use, albeit not as much as a few years ago, but its out there.

Time also refers to the evolution of enhanced manageability. Im not saying you need to be a UTLBSTAT/UTLESTAT wizard to have an appreciation of what the AWR provides, but starting with STATSPACK is certainly not unreasonable. If you can understand how to read and interpret ADDM reports (again, based off of information from the AWR), then learning how to do the same with AWR-based reports becomes that much easier.

Installing STATSPACK

Plenty of other articles on the Internet, plus Oracles documentation, cover how to install STATSPACK and take snapshots, and Im not going to repeat that here (except very briefly). After STATSPACK is installed, what a lot of places miss is now what? What does the PERFSTAT schema look like? What are the tables it uses? What about customizing what STATSPACK collects? How do you manage the repository? These are the relevant questions going forward.

A schema named PERFSTAT is the owner of STATSPACK. A common practice is to house the STATS$whatever tables in a separate tablespace, and the tablespace is cleverly named PERFSTAT in pre-10g versions (look for it in SYSAUX otherwise). In ancient versions, the schema is created and configured after running two scripts as the SYS user: catdbsyn.sql and dbmspool.sql, both found in the $ORACLE_HOME/rdbms/admin directory (catdbsyn is obsoleted by the DBA role, and dbmspool is run during the next step). The creation script proper is spcreate.sql. This script will interview you to obtain location information, and will also generate log files. You can also install in batch mode, but the interactive mode is the easiest to use.

STATSPACK has evolved across versions of the RDBMS. In Oracle 8.1.7, 28 tables are created. In 10.2.0.1, the PERFSTAT schema starts with 68 tables.

The PERFSTAT schema in 10g (and later) lives in the SYSAUX tablespace, and is one of the schemas which can vacate SYSAUX if you so chose. You can confirm this by viewing the V$SYSAUX_OCCUPANTS view based on the OCCUPANT_NAME of STATSPACK. Common to whichever version STATSPACK is being installed in, having the TIMED_STATISTICS parameter set to true will result in better data for performance analysis.

Configuring STATSPACK

The next step is to set (or change) the collection option, which consists of two components: the level and the threshold. The 10g and later documentation is sparse on this  its hard to find there, but one of the See Also notes directs you to Oracle9i documentation. Something even older can be found here.

Once snapshots are ready to be taken, the next step is to create some jobs. Three jobs will cover the automation we see in the AWR (how often a snapshot is taken and managing the retention). The jobs are: take a snapshot, purge snapshots, and perform customized reporting, such as reporting on segment growth.

To create a job which runs hourly:

BEGIN SYS.DBMS_JOB.REMOVE(3); COMMIT; END; / -- -- JOB3 (Job) -- DECLARE X NUMBER; BEGIN SYS.DBMS_JOB.SUBMIT ( job => X ,what => 'statspack.snap;' ,next_date => to_date('17/10/2008 17:00:00','dd/mm/yyyy hh24:mi:ss') ,interval => 'trunc(SYSDATE+1/24,''HH'')' ,no_parse => FALSE ,instance => 1 ); SYS.DBMS_OUTPUT.PUT_LINE('Job Number is: ' || to_char(x)); COMMIT; END; /

To create a job which purges the repository:

BEGIN SYS.DBMS_JOB.REMOVE(4); COMMIT; END; / -- -- JOB4 (Job) -- DECLARE X NUMBER; BEGIN SYS.DBMS_JOB.SUBMIT ( job => X ,what => 'sppurpkg.purge(45);' ,next_date => to_date('18/10/2008 13:29:23','dd/mm/yyyy hh24:mi:ss') ,interval => 'SYSDATE+1' ,no_parse => FALSE ); SYS.DBMS_OUTPUT.PUT_LINE('Job Number is: ' || to_char(x)); COMMIT; END; /

Finally, a job to monitor/collect segment growth information:

BEGIN SYS.DBMS_JOB.REMOVE(21); COMMIT; END; / -- -- JOB21 (Job) -- DECLARE X NUMBER; BEGIN SYS.DBMS_JOB.SUBMIT ( job => X ,what => 'sp_growth;' ,next_date => to_date('17/10/2008 18:31:49','dd/mm/yyyy hh24:mi:ss') ,interval => 'sysdate+1' ,no_parse => FALSE ); SYS.DBMS_OUTPUT.PUT_LINE('Job Number is: ' || to_char(x)); COMMIT; END; /

Three notes about the jobs are in order. The first is that job number is relative to your system; the ones shown here are examples where the job IDs are 3, 4 and 21. The second is the parameter used for the purge job; this example uses a 45-day purge threshold. The third has to do with the interval on two of the jobs. The intervals use sysdate+1, so the odd next_date times you see are a result of a creeping start time. You can set the interval to be more precise, such as on the hour at a specific hour.

The last job uses customized tables, views, and a procedure to monitor datafile and segment growth, but you are not limited in this regard. Whatever you can query out of the data dictionary and from within the PERFSTAT schema, not involving anything to do with the AWR is fair game. If you want to directly view the STATS$<some_area> tables, a tool such as Toad or SQL Developer can provide a quick snapshot of a snapshot, so to speak.

Of course, the main way to use this data is in comparison across time as opposed to a single point in time. Nonetheless, if you want to see if a certain metric has changed between snapshots, look at it directly instead of having to run a report.

In Summary

STATSPACK is still very much a viable option for monitoring and diagnosing performance. With todays latest and greatest version of Oracle and its enhanced manageability features, it seems as if AWR and ADDM have voted to send STATSPACK to Exile Island. Treasures and rewards can still be found in this place of exile, but its up to you to find them and make the most of whats available. We know that Oracle is continuing support STATSPACK as evidenced by the increased number of PERFSTAT tables related to system and session events. The number of tables in Release 11.1.0.6 has grown to 73 tables, almost triple than what it installed with in release 8.1.7.4.