Install and use Statspack

The performance collection using Statspack is achieved through snapshot, that is, Statspack takes a snapshot of the system performance data at a certain time point, and saves the snapshot in perfst.

The performance collection using Statspack is achieved through snapshot, that is, Statspack takes a snapshot of the system performance data at a certain time point, and saves the snapshot in perfst.

Perfcreate

Use the sys user to connect to the database and run the spcreate. SQL script. First, enter the new user perfstat password. The password we entered is perfcreate.

Choose the Default tablespace for the PERFSTAT user

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

Below is the list of online tablespaces in this database which can

Store user data. Specifying the SYSTEM tablespace for the user's

Default tablespace will result in the installation FAILING,

Using SYSTEM for performance data is not supported.

Choose the PERFSTAT users's default tablespace. This is the tablespace

In which the STATSPACK tables and indexes will be created.

TABLESPACE_NAME CONTENTS STATSPACK DEFAULT TABLESPACE

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

CW PERMANENT

CW_MD PERMANENT

CW_PART PERMANENT

DBAPI PERMANENT

PETDOG PERMANENT

SEVEN PERMANENT

STRMADMIN PERMANENT

Sysaux permanent *

USERS PERMANENT

Pressing Will result in STATSPACK's recommended default

Tablespace (identified by *) being used.

Enter the value of default_tablespace: users

Next, Oracle lists the tablespaces that can be used in the database. We need to specify the default tablespace for perfstat, that is, the tablespace for storing Statspack information. Here we select users tablespace.

Using tablespace USERS as PERFSTAT default tablespace.

Choose the Temporary tablespace for the PERFSTAT user
-----------------------------------------------------
Below is the list of online tablespaces in this database which can
Store temporary data (e.g. for sort workareas). Specifying the SYSTEM
Tablespace for the user's temporary tablespace will result in
Installation FAILING, as using SYSTEM for workareas is not supported.

Choose the PERFSTAT user's Temporary tablespace.

TABLESPACE_NAME CONTENTS DB DEFAULT TEMP TABLESPACE
-----------------------------------------------------------------
Temp temporary *

Pressing Will result in the database's default Temporary
Tablespace (identified by *) being used.

Enter the value of temporary_tablespace: temp

Next, enter the default temporary tablespace. No doubt, select temp tablespace.

The operation for creating a perfstat user object is as follows:

If this script is automatically called from spcreate (which is

The supported method), all STATSPACK segments will be created in

The PERFSTAT user's default tablespace.

Using users tablespace to store Statspack objects

... Creating STATS $ SNAPSHOT_ID Sequence

The sequence has been created.

You have created a synonym.

... Creating STATS $... tables

The table has been created.

You have created a synonym.

The table has been created.

One row has been created.

One row has been created.

......

You have created a synonym.

You have created a synonym.

NOTE:

Spctl complete. Please check spctl. lis for any errors.

......

Creating Package STATSPACK...

The package has been created.

No error.

Creating Package Body STATSPACK...

The package body has been created.

No error.

NOTE:

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

So far, the Statspack installation is complete. Next, use Statspack for performance collection and performance analysis.