Oracle9i Statspack Installation and use of detailed

The following is tested under Window xp,oracle9.2.0.1, under Linux/unix the path "" is replaced by the "/"

Installation of 1.Statspack

Sqlplus/nolog
Conn/as SYSDBA

--Create a statspack table space, require more than 80M or use existing tablespace, but must have more than 80M of free space

Sql>create tablespace statspack datafile ' <statspack_tbs.dbf> '
Size 100M autoextend on
EXTENT MANAGEMENT Local uniform SIZE 128K
--Execute the installation script, under Oracle_home dbmsadmin
Sql>@? Dbmsadminspcreate.sql

--During the creation, you will be prompted to enter the password for the new user Perfstat, the default tablespace, the default temp table space, and I'll set this here to: Oracle Statspack TEMP

-After the creation, will automatically use the Perfstat user to connect, may check the sql>show user--if not perfstat the user, may execute one of the following

Sql>conn perfstat/oracle

2. Generate performance reports manually using Statspack
--Grab a snapshot, must be 2 times or more
Sql>exec Statspack.snap

--at least two times
Sql>exec Statspack.snap

--Generate a report that prompts you to enter the test and end snapshot ID of the generated report, and must be a continuous snapshot within the same cycle without disrupting the database

Sql>@? Dbmsadminspreport.sql

--Prompts you to enter the file name of the report, either by default or

3. Open the generated report with a text editor, specific analysis

4. If the installation process error, how to correct

You must first delete the related information created by Spcreate.sql with Spdrop.sql
Sql>conn/as SYSDBA
Sql>@? Dbmsadminspdrop.sql

5. Install Statspack.snap to automatic execution, grab a snapshot every hour
Sql>@? Dbmsadminspauto.sql
--Check if the task has this task, and you can see the job ID

Sql>select * from Dba_job;
--You can delete a task if you don't want to automatically grab the snapshot

--Write down the ID of the job and then execute
Sql>conn perfstat/oracle
Sql>exec dbms_remove (n);