Oracle Statspack Installation Guide: spcreate Setup
Oracle Database Statspack Installation Guide - Complete Setup and Configuration
Purpose
Statspack is a snapshot-based way to see what an Oracle database did between two points in time. AWR is the better-known alternative, and the license position differs. Oracle's Licensing Information guide lists the Automatic Workload Repository (AWR) as a feature of the Diagnostics Pack, and it says that using that pack requires its own license. That same guide does not mention Statspack at all. Whether Statspack suits a team without a Diagnostics Pack license is a question for that team's own license terms, not for this post.
Statspack is installed with one SQL*Plus script, spcreate. It creates a user named PERFSTAT, a set of tables owned by that user, and a package called STATSPACK. You then take snapshots, either by hand or on a schedule, and run a report between any two of them. This post walks through the install, the first snapshot, the automatic job, the interval syntax, and the clean-up scripts.
Code
1-- 1. Create a dedicated tablespace (example name, path and sizes)
2CREATE TABLESPACE perfstat
3 DATAFILE '/u01/app/oracle/oradata/PROD/PERFSTAT01.dbf'
4 SIZE 100M AUTOEXTEND ON MAXSIZE 5G;
5
6-- 2. Install Statspack as a SYSDBA user
7CONNECT / AS SYSDBA
8@?/rdbms/admin/spcreate
9
10-- 3. Take a first snapshot as PERFSTAT
11CONNECT perfstat
12EXECUTE STATSPACK.SNAP;
13
14-- 4. Schedule hourly snapshots with DBMS_JOB
15@?/rdbms/admin/spauto
16
17-- 5. Find the job number, then change the interval to 15 minutes
18SELECT job, what, interval, next_date
19FROM dba_jobs
20WHERE LOWER(what) LIKE '%statspack%';
21
22EXECUTE DBMS_JOB.INTERVAL(<job number>, 'SYSDATE+15/1440');
23
24-- 6. Report and purge, both as PERFSTAT
25@?/rdbms/admin/spreport
26@?/rdbms/admin/sppurge
The ? in a SQL*Plus path stands for ORACLE_HOME. The tablespace name, file path and sizes in step 1 are examples from this post. They are not Oracle defaults, so change them for your system.
Code Breakdown
Step 1: a dedicated tablespace
Oracle's Statspack documentation says that approximately 64MB is required to install Statspack. That is the install figure only. The tables grow as you keep snapshots, so the example above starts at 100M and lets the file extend. The 5G cap is a choice made for this example, not a rule from Oracle. Size it from how often you snapshot and how long you keep the data.
The documentation also says not to name the SYSTEM tablespace as the default or temporary tablespace of the PERFSTAT user. If you do, the installation aborts with an error. Oracle does not recommend SYSTEM for storing statistics data or for sorting.
Step 2: spcreate
To run the install script you must use SQL*Plus and connect as a user with the SYSDBA privilege. Oracle's documentation says spcreate runs three other scripts, and they are called automatically, so you do not run them by hand:
SPCUSR.SQLcreates thePERFSTATuser and grants it privileges.SPCTAB.SQLcreates the tables that hold the statistics.SPCPKG.SQLcreates theSTATSPACKpackage.
The Oracle 9i documentation says that during installation you are prompted for the default and temporary tablespaces of the PERFSTAT user. Answer with the dedicated tablespace from step 1 and your normal temporary tablespace. The same page says that after setup is complete you can change the password of the PERFSTAT user for security purposes, so do that as soon as the install ends. Prompts can differ between releases. Read what your own session shows, and read spdoc.txt in $ORACLE_HOME/rdbms/admin, which is the document shipped with your release.
Step 3: the first snapshot
STATSPACK.SNAP records a snapshot of the instance statistics into the PERFSTAT tables. Run it once as PERFSTAT to confirm the install worked, then check that it returned without error. Do not rely on a particular snapshot number. Each snapshot gets an ID, and the report scripts ask you for IDs, so list the snapshots you have before you run a report.
Step 4: spauto and the job queue
SPAUTO.SQL is the sample script that schedules a snapshot every hour, on the hour. It uses DBMS_JOB. Oracle's documentation says that to use DBMS_JOB for this, the JOB_QUEUE_PROCESSES initialization parameter must be greater than 0, or the job does not run by itself. Check the parameter before you decide the schedule is broken.
The snapshot level controls how much data is gathered. Oracle's page says the higher the level, the more data is gathered, and that the default level set by the installation is level 5.
Step 5: changing the interval
The Statspack page shows DBMS_JOB.INTERVAL being used with job number 1. Do not assume your job is number 1. The same page writes <job number> for the commands that run or remove the job, and it does not say where to find the number. A query on DBA_JOBS is one way to find it. The WHAT column holds the text of the job, so a filter on the word Statspack picks out the snapshot job.
Oracle's page gives this example: 'SYSDATE+(1/48)' results in the statistics being gathered each 1/48 hours, that is, every half hour. Take the arithmetic from the date rules instead: SYSDATE plus a number adds that many days, so 1/48 of a day is half an hour. A day has 1,440 minutes, so 15/1440 is 15 minutes, and 1/48 is 30 minutes.
The DBMS_JOB reference says the interval is a date function that is evaluated immediately before the job starts running. If the job completes successfully, the new date is placed in next_date. That means each run schedules the next one. The page also notes that DBMS_JOB has been superseded by DBMS_SCHEDULER, which matters if your site is moving away from the older package.
Step 6: report and purge
Run spreport.sql while connected as PERFSTAT. It asks for a begin and an end snapshot ID and for a report file name. Oracle's example offers a default name built from the two IDs, such as sp_1_2. Run sppurge.sql as PERFSTAT too. It deletes all snapshots between the lower and upper snapshot IDs you give it.
Key Points
- Install space: the Statspack documentation says approximately 64MB is required to install. Plan more for the snapshots you keep.
- Privilege: the install must run in SQL*Plus as a user with
SYSDBA. - Release: the documentation says Statspack is not supported with releases earlier than 8.1.6.
- Job number: use the number that belongs to your own job. Look it up in
DBA_JOBS. - Job queue:
JOB_QUEUE_PROCESSESmust be greater than 0 forDBMS_JOBto run the job by itself. - License: the 19c Licensing Information guide ties AWR to the Diagnostics Pack and says nothing about Statspack. Read your own contract.
Insights and Best Practices
How Statspack stores data
Oracle's documentation says permanent tables owned by PERFSTAT store the performance statistics. Instead of creating and dropping tables each time, data is inserted into the preexisting tables, which makes historical comparison easier. The same page lists several ways that Statspack differs from the older UTLBSTAT and UTLESTAT scripts. One example is that Statspack collects more data, including high-resource SQL.
Choosing an interval
A shorter interval gives a finer picture of a short problem, and it adds rows to the tables sooner. Hourly, the schedule spauto sets up, is a sensible start. Move to 15 or 30 minutes while you chase a specific problem, then put the interval back. A report covers the time between two snapshots, so a long gap blurs a short spike into the average.
Choose the snapshot pair with care
The report covers the time between the two snapshot IDs you give it. List the snapshots you have, with their times, and choose a begin and an end that bracket the period you want to study.
Plan the purge
Old snapshots pile up until you remove them. Decide a retention period that suits your site and run sppurge.sql on that rhythm. The script deletes a range of snapshot IDs, so check the IDs first. A wrong upper bound deletes data you wanted.
Protect the PERFSTAT account
The install creates a database user with access to performance data. Change its password after setup, as the documentation advises, and give the password only to the people who run reports.
When to Run This
- A new database needs a performance history and no Diagnostics Pack license is in place.
- A performance complaint arrives and there is no earlier data to compare against.
- Before and after a change such as a parameter edit or an application release, to compare two windows.
- When you must change the snapshot interval for a short investigation.
Troubleshooting Common Issues
The install aborts with a tablespace error. Check that the tablespace you named for the PERFSTAT user is not SYSTEM. Oracle's documentation says the installation aborts if it is.
The scheduled job never runs. Check JOB_QUEUE_PROCESSES. If it is 0, DBMS_JOB does not run jobs by itself. Then query the job:
1SELECT job, what, interval, next_date
2FROM dba_jobs
3WHERE LOWER(what) LIKE '%statspack%';
The interval change did nothing. You may have passed the wrong job number. Run the query above, then call DBMS_JOB.INTERVAL with the number it shows. The new interval takes effect when the next run completes, because the interval is evaluated just before the job starts.
The job runs but snapshots stop. Look for the job in DBA_JOBS and read its next_date column. To force a run, Oracle's page uses DBMS_JOB.RUN with the job number, and to remove the job it uses DBMS_JOB.REMOVE.
References
- Oracle9i Database Performance Guide and Reference: Using Statspack - The Oracle page for the install size, the SYSDBA requirement, the three scripts spcreate runs, spauto, DBMS_JOB.INTERVAL, sppurge and spreport.
- DBMS_JOB - Oracle Database PL/SQL Packages and Types Reference 10g Release 2 - Defines the interval parameter as a date function evaluated before the job starts, and notes that DBMS_SCHEDULER supersedes DBMS_JOB.
- Oracle Database Licensing Information User Manual 19c - Lists AWR and the DBA_HIST views as Diagnostics Pack features; read it for your own license position.
- Statspack - ORACLE-BASE - A community walkthrough of a Statspack install on Oracle 8i that points readers to spdoc.txt, the document shipped with the database.
Posts in this series
- Oracle Statspack Snapshot Levels: statspack.snap Guide
- Oracle Statspack sppurge: Snapshot Purge Script Guide
- spreport.sql: Generate Oracle Statspack Performance Reports
- Oracle Statspack: List Snapshots with stats$snapshot
- Oracle Statspack Installation Guide: spcreate Setup
- Oracle: Uninstall Statspack Using the spdrop Script
- Oracle Statspack: Automate Snapshots with spauto.sql