Oracle 9i Statspack Configuration Tool

Oracle self-diagnostic tools play a major role in daily performance diagnosis. Since Oracle 8, Oracle has been continuously promoting the development of comprehensive self-diagnostic kits. From the earliest Statspack to the current AWR and ADDM, Oracle is moving towards self-diagnosis and intelligent development.
 
As a toolkit launched earlier in Oracle, Statspack was first introduced in Oracle 8 and has been widely used in 9i. From the development process, Statspack laid the basic working principle and Indicator Method of the existing AWR. AWR is a perfect development based on Statspack.
 
In some old systems, 9i is still the main environment system of the database, and the Statspack diagnosis function is required. This article describes how to install and use the Statspack tool in 9i environment and how to extract execution plans.
 
-------------------------------------- Split line --------------------------------------

Related reading:

Detailed analysis of new Oracle 10g Statspack features

The treasure hidden in Oracle: Statspack

Use Statspack for Oracle Performance Analysis

Oracle Statspack Installation Guide

Install and use Statspack

-------------------------------------- Split line --------------------------------------

1. Install and uninstall Statspack

By default, Statspack is not installed as the default option. Due to the existence of AWR in the 10G version, we generally do not need to install an outdated Statspack package. We will introduce how to install the 9i environment.
 
SQL> select * from v $ version;

BANNER

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

Oracle9i Enterprise Edition Release 9.2.0.1.0-Production

PL/SQL Release 9.2.0.1.0-Production

CORE 9.2.0.1.0 Production

TNS for 32-bit Windows: Version 9.2.0.1.0-Production

NLSRTL Version 9.2.0.1.0-Production

To put it simply, the statspack toolkit is a small program consisting of a PL/SQL stored procedure package and a series of basic Snapshot information tables. The stored Snapshot running data requires additional storage space. Therefore, we recommend that you create additional tablespace storage for data retention.
 
SQL> create tablespace stptbl datafile 'C: \ ORACLE \ ORADATA \ ORA9I \ stptbl01.dbf' size 500 m autoextend on
 
2 extent management local uniform size 1 m

3 segment space management auto;

Tablespace created

Note: Unlike AWR's Automatic Storage Management, the snapshot of Statspack does not have an automatic deletion mechanism. That is to say, if we do not care, the image data collected manually will be stored in the database all the time. Therefore, if you often use Statspack to collect performance data, you must not ignore the space issue.
 
In 9i, we need to call the server script to complete the creation. Note: We try to execute the script on the server to prevent problems caused by different versions.

SQL> show user

USER is "SYS"

 

SQL> @? \ Rdbms \ admin \ spcreate

... Installing Required Packages

 

You have created a synonym.

 

... Creating PERFSTAT user...

Choose the PERFSTAT user's password.

Not specifying a password will result in the installation FAILING

 

Specify PERFSTAT password

Enter the perfstat_password value: perfstat

Perfstat

 

The PL/SQL process is successfully completed.

User Created

Authorization successful.

 

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

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

CWMLITE

PERMANENT

 

12 rows have been selected.

 

Specify PERFSTAT user's default tablespace

Enter the value of default_tablespace: STPTBL

Using STPTBL for the default tablespace

 

The PL/SQL process is successfully completed.

 

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 the value of temporary_tablespace: TEMP

 

You have created a synonym.

 

NOTE:

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

 

The spcreate script is an interactive process. During the execution, we need to confirm three parameter items: Create the user perfstat password, data table space, and temporary tablespace.

Note that a data table space is used as the statspack storage instead of in the system.

Uninstalling Statspack is relatively simple. You can just run the script spdrop in the same directory. This article will not go into detail.

 

2. Use Statspack

 

Essentially, the same Statspack and AWR are both performance monitoring tools based on Sampling. In earlier versions, Oracle has released a series of dynamic views to reflect the "real-time" Performance of databases and systems. The so-called Snapshot is at a certain point in time. The Oracle program keeps the real-time data and records it in the database table as a snapshot point. Both Statspack and AWR reports are based on the difference of multiple snapshots.
 
 

The most obvious difference between Statspack and AWR is that Statspack does not automatically perform the Snapshot action by default, while AWR provides the sampling function every hour by default.

 

Therefore, AWR is easy to analyze the fault period, while Statspack must be faulty or re-executed. By default, it is easy to execute a statspack image.

 

 

SQL> conn perfstat/perfstat @ ora9i

Connected to Oracle9i Enterprise Edition Release 9.2.0.1.0

Connected as perfstat

 

SQL> exec statspack. SNAP;

PL/SQL procedure successfully completed

 

Call the snap method of the statspack package to directly collect snapshots. The default collection policy is used. You can view the saved information in the basic table stats $ snapshot.

 

 

SQL> select snap_id, dbid, to_char (snap_time, 'yyyy-mm-dd hh24: mi: ss') snap_time, snap_level from stats $ snapshot;
 
 

SNAP_ID DBID SNAP_TIME SNAP_LEVEL

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

1 2629872507 10:51:30 5

 

Note: The default Oracle configuration is used here. We have a lot of options for the snapshot method image. These contents will be discussed later. The target is to create a snapshot.

If you observe the data tables under the perfstat user, we will find many data tables prefixed with stats $. The data stored in these tables is the performance data led by snapshot.

Like AWR, generating a statspack report requires at least two snapshots, And the shutdown and startup actions are not allowed in the middle of the snapshot.

 

 

SQL> exec statspack. SNAP;

PL/SQL procedure successfully completed

 

SQL> select snap_id, dbid, to_char (snap_time, 'yyyy-mm-dd hh24: mi: ss') snap_time, snap_level from stats $ snapshot;
 
 

SNAP_ID DBID SNAP_TIME SNAP_LEVEL

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

1 2629872507 10:51:30 5

2 2629872507 11:05:00 5

When two reports are created, we can generate the report. Note: The Statspack and AWR reports are slightly different during generation. Statspack is used to call client scripts, while the AWR core program is in the server package. Therefore, we strongly recommend that you use the server side to generate reports.
 
SQL> conn perfstat/perfstat @ ora9i

Connected.

SQL>

SQL> @? /Rdbms/admin/spreport // generate the script

 

Current Instance

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

DB Id DB Name Inst Num Instance

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

2629872507 ORA9I 1 ora9i

 

Instances in this Statspack schema

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

 

DB Id Inst Num DB Name Instance Host

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

2629872507 1 ORA9I ora9i ACCA-8535F03

015

Using 2629872507 for database Id

Using 1 for instance number

 

Specify the number of days of snapshots to choose from

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

Entering the number of days (n) will result in the most recent

(N) days of snapshots being listed. Pressing <return>

Specifying a number lists all completed snapshots.

Snap

Instance DB Name Snap Id Snap Started Level Comment

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

 

Ora9i ORA9I 1 07 August 14 5

1

February 20

5

 

Specify the Begin and End Snapshot Ids

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

Input begin_snap value: 1

Begin Snapshot Id specified: 1

 

Input end_snap value: 2

End Snapshot Id specified: 2

 

Specify the Report Name

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

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

Press <return> to continue, otherwise enter an alternative.

 

Enter the value of report_name:

 

Using the report name sp_1_2

The content of the script is omitted. The same as when an AWR report is generated, the statspack report requires three data types for interaction: the end-to-end snapshot number and report generation name. Unlike the html and text Reports of AWR, statspack supports text reports.
 
The final report segment is as follows:

STATSPACK report

 

DB Name DB Id Instance Inst Num Release Cluster Host

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

ORA9I 2629872507 ora9i 1 9.2.0.1.0 NO ACCA-8535F03

015

 

Snap Id Snap Time Sessions Curs/Sess Comment

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

Begin Snap: 1 month-14 10:51:30 10 4.5

End Snap: 2-2 months-14 11:05:00 10 6.4

Elapsed: 13.50 (mins)

Report generation, including the Top n Series diagnostics that we are familiar.

After learning about the basic functions of statspack, we need to explore it. Compared with AWR, Statspack has a high flexibility in sampling control. Different settings can have different diagnostic levels. In the next article, we will discuss in detail.

For more details, please continue to read the highlights on the next page:

1 2 Next Page