Databases Reference
In-Depth Information
How to do it...
Follow these steps to make use of the Statspack tool:
1.
Connect to the database with a user with the
SYSDBA
privilege and run the
spcreate.sql
script from the
$ORACLE_HOME/rdbms/adminr
directory. This
script will ask for a password to assign to the
PERFSTAT
user.
2.
We will then be asked for the tablespace to use. Select the previously created
tablespace by entering its name (
STATSPACK
). When the script asks for the
temporary tablespace just press
Enter
to use the default temporary tablespace
defined in the system.
3.
The script will create the user
PERFSTAT
, identified by the password provided, and all
the objects needed to run the tool.
After the tool is created, we can collect statistics by executing the following
procedure:
EXEC STATSPACK.snap;
With this simple command, we have created a snapshot in the Statspack table.
4.
When we have at least two snapshots, we can create a report using a pair of them.
To do so, we will execute the
spreport.sql
script.
The script will show us the completed snapshots and we will be asked for the ID of
the two which we want to compare.
5.
The script will ask for the name to give to the report—the default will be
sp_id1_id2
,
where
id1
and
id2
are the beginning and ending snapshots chosen in the
previous step.
At the end of the process, we will find our Statspack report.
How it works...
The
spcreate.sql
script internally launches the
spcusr.sql
,
spctab.sql
, and
spcpkg.sql
scripts. For every script, after the execution, we will find a corresponding file
with the extension changed to
.lis
with the spool of the actions performed. In case anything
goes wrong, we can launch the
spdrop.sql
script to rollback the actions performed by
spcreate.sql
.
A snapshot of Statspack contains information from the dynamic performance views. As
these views are emptied at database start-up, it makes no sense to elaborate Statspack
performance reports with the use of snapshots taken before and after a database shutdown.
