Statspack 101: Installing Oracle's Statspack Utility

Introduction
Oracle's Statspack is a free utility that allows you to monitor and report on
performance problems with your Oracle database. In this tutorial we will install
Statspack on an Oracle 9i database.
Creating a Statspack Tablespace
The first step in installing statspack is creating a tablespace for the statspack
objects and data to reside. For our example we will call the tablespace "PERFSTAT"
and make it 500mb in size.

SQL> CREATE TABLESPACE PERFSTAT

2 DATAFILE '/path/to/my/tablespaces/perfstat.dbf' SIZE 500M AUTOEXTEND OFF

3 EXTENT MANAGEMENT LOCAL AUTOALLOCATE

4 LOGGING

5 ONLINE

6 SEGMENT SPACE MANAGEMENT AUTO

7 /

Tablespace created.

Installing Statspack

Next, we're actually going to install Statspack in the database. First you want
to change to the ORACLE_HOME/rdbms/admin/ directory. This directory contains
all the files needed for installing statspack.
$> cd $ORACLE_HOME/rdbms/admin/
Now that we are in the ORACLE_HOME/rdbms/admin/ directory we are going to run
the "spcreate.sql" script from SQLPLUS. This script will create the
statspack user, the statspack tables, and the statspack packages.

$> sqlplus "/ as sysdba"
SQL*Plus: Release 9.2.0.4.0 - Production on Mon Nov 28 18:49:51 2005
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.


Connected to:

Oracle9i Enterprise Edition Release 9.2.0.4.0 - 64bit Production

With the Partitioning, OLAP and Oracle Data Mining options

JServer Release 9.2.0.4.0 - Production
SQL> @spcreate;

... Installing Required Packages
Package created.


Grant succeeded.


View created.


Package body created.


Package created.


Synonym created.


Grant succeeded.


View created.


Synonym created...


...Synonym created.
... Creating PERFSTAT user ...
Choose the PERFSTAT user's password.
Not specifying a password will result in the installation FAILING
Specify PERFSTAT password

Enter value for perfstat_password: perfstatpass

perfstatpass
PL/SQL procedure successfully completed.


User created....


....Grant succeeded.


Below are the list of online tablespaces in this database.

Decide which tablespace you wish to create the STATSPACK tables

and indexes. This will also be the PERFSTAT user's default tablespace.
Specifying the SYSTEM tablespace will result in the installation FAILING, as using SYSTEM for performance data is not supported.

TABLESPACE_NAME CONTENTS

------------------------------ ---------

DRSYS PERMANEN

PERFSTAT PERMANENT

TEMP TEMPORARY

TOOLS PERMANENT

UNDOTBS UNDO

USERS PERMANENT

XDB PERMANENT
7 rows selected.


Specify PERFSTAT user's default tablespace

Enter value for default_tablespace: perfstat

Using perfstat for the default tablespace
PL/SQL procedure successfully completed.


User altered.


User altered.


Choose the PERFSTAT user's temporary tablespace.
Specifying the SYSTEM tablespace will result in the installation

FAILING, as using SYSTEM for the temporary tablespace is not recommended.
Specify PERFSTAT user's temporary tablespace.

Enter value for temporary_tablespace: temp

Using temp for the temporary tablespace


PL/SQL procedure successfully completed.


User altered.....
Creating Package STATSPACK...
Package created.
No errors.

Creating Package Body STATSPACK...
Package body created.
No errors.
NOTE:

SPCPKG complete. Please check spcpkg.lis for any errors.

Review any messages and make sure you didn't receive any errors.


Execute a Snapshot and Generate a Statspack Report

Now that you have statspack installed, let's take two snapshots and then generate
a Statspack Report. Assuming you are still in the ORACLE_HOME/rdbms/admin/ directory
and logged in as perfstat, run this command twice like so:

SQL> execute statspack.snap
PL/SQL procedure successfully completed.
SQL> execute statspack.snap
PL/SQL procedure successfully completed.
We now have two snapshots to compare in order to create a Statspack Report.
Run the "spreport.sql" script in SQLPLUS to create a Statspack Report:
SQL> @spreport
Current Instance

~~~~~~~~~~~~~~~~
DB Id DB Name Inst Num Instance

----------- ------------ -------- ------------

4184632401 ORCL 1 ORCL

Instances in this Statspack schema

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
DB Id Inst Num DB Name Instance Host

----------- -------- ------------ ------------ ------------

4184632401 1 ORCL ORCL localhost
Using 4184632401 for database Id

Using 1 for instance number
Completed Snapshots
Snap Snap

Instance DB Name Id Snap Started Level Comment

------------ ------------ ----- ----------------- ----- ----------------------

ORCL ORCL 1 28 Nov 2005 20:00 5

2 28 Nov 2005 20:04 5

Specify the Begin and End Snapshot Ids

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Enter value for begin_snap: 1

Begin Snapshot Id specified: 1
Enter value for end_snap: 2

End Snapshot Id specified: 2
Specify the Report Name

~~~~~~~~~~~~~~~~~~~~~~~

The default report file name is sp_1_2. To use this name,

press to continue, otherwise enter an alternative.

Enter value for report_name: myStatspackReport.txt
-- a bunch of the report flies by the screen here,

-- but don't worry we saved a copy to look at afterwards.
End of Report
SQL>

Here I saved my Statspack Report under the name "myStatspackReport.txt".
You can exit SQLPLUS and open the file in a text editor to view the report.


Scheduling the Statspack Report
Now that you have statspack up and running, lets schedule it to run at an automatic
interval. Again, assuming you are in the ORACLE_HOME/rdbms/admin/ directory
and logged in as perfstat, run the "spauto.sql" script:

SQL> @spauto
PL/SQL procedure successfully completed.


Job number for automated statistics collection for this instance

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Note that this job number is needed when modifying or removing

the job:
JOBNO

----------

1


Job queue process

~~~~~~~~~~~~~~~~~

Below is the current setting of the job_queue_processes init.ora

parameter - the value for this parameter must be greater

than 0 to use automatic statistics gathering:
NAME TYPE

------------------------------------ --------------------------------

VALUE

------------------------------

job_queue_processes integer

2


Next scheduled run

~~~~~~~~~~~~~~~~~~

The next scheduled run for this job is:
JOB NEXT_DATE NEXT_SEC

---------- --------------- ------------------------

1 28-NOV-05 21:00:00

The "spauto.sql" script, by default, schedules the statspack job
to take a snapshot every hour. If you want a statspack snapshot to be taken
more often than that then make a copy of the spauto.sql file and edit it. You
want to change the section that says:

variable jobno number;

variable instno number;

begin

select instance_number into :instno from v$instance;

dbms_job.submit(:jobno, 'statspack.snap;',

trunc(sysdate+1/24,'HH'), 'trunc(SYSDATE+1/24,''HH'')', TRUE, :instno);

commit;

end;

/

If you want the statspack to run every 10 minutes then change it to say this:

variable jobno number;

variable instno number;

begin

select instance_number into :instno from v$instance;

dbms_job.submit(:jobno, 'statspack.snap;',

trunc(sysdate+1/144,'MI'), 'trunc(SYSDATE+1/144,''MI'')', TRUE, :instno);

commit;

end;

/

Issue your copy of the spauto.sql script and it will schedule a snapshot to
be taken every 10 minutes.


Check the job_queue_processes parameter
Make sure that you have the job_queue_processes parameter set higher than zero
or you will not be able to use the auto snapshot feature to take statspack snapshots
at a given interval.

SQL> set linesize 150;

SQL> show parameter job_queue_processes
NAME TYPE VALUE

------------------------------------ -------------------------------- -----

job_queue_processes integer 0

Here I don't have job_queue_processes set above zero so I issue a ALTER SYSTEM
command to increase it to 2:

SQL> ALTER SYSTEM SET job_queue_processes = 2 SCOPE=BOTH;
System altered.
SQL> SHOW PARAMETER job_queue_processes
NAME TYPE VALUE

------------------------------------ -------------------------------- -----

job_queue_processes integer 2

How to Uninstall Statspack
To uninstall Statspack from an Oracle database running Oracle 8.1.7 and above,
you just need to run the ORACLE_HOME/rdbms/admin/spdrop.sql script. This will
get rid of the user, tables, and procedures.
To uninstall Statspack from an Oracle database running Oracle 8.1.6, you just
need to run the ORACLE_HOME/rdbms/admin/spdrp.sql
In a future article I hope to cover how to interpret your StatsPack Reports.
share on: facebook