All about Technology: 9i Statspack install steps and Creating Report Steps

Thursday, 14 July 2011

9i Statspack install steps and Creating Report Steps

9i Installing the Statspack

Installation of the Oracle Statspack tool is a relatively simple process. The following is a step-by-step guide to the process of installing Oracle Statspack on a UNIX system.
Navigate to the $ORACLE_HOME/rdbms/admin directory as follows:
# cd $ORACLE_HOME/rdbms/admin/
Start the Statspack install script, spcreate.sql, as follows:
# sqlplus "/ as sysdba" @spcreate.sql
Enter a password for the PERFSTAT user when prompted.

Enter the default tablespace (tools) for the PERFSTAT user when prompted.

Enter the temporary tablespace (temp) for the PERFSTAT user when prompted.

Exit sqlplus as follows:
SQL> exit
Collecting Snapshots

Once the Oracle Statspack tool is installed, snapshots must be collected to evaluate database performance. Snapshots are moment-in-time collections of all of the database statistics that the Oracle database continuously collects. Once two snapshots are collected, they can be compared to identify the activity that occurred during the interval between the two snapshots.
Snapshots can be collected a various levels, each increasing level collecting a greater amount of information about the database. As the levels go higher, each level is inclusive of the information collected at the levels below it.

Table A-1 Levels of Statistics

Level

Information Collected

0

General Performance Statistics

5

Addition Data: SQL Statements

6

Addition Data: SQL Plans and SQL Plan Usage

7

Addition Data: Segment Level Statistics

10

Addition Data: Parent and Child Latches

To collect statistics
Connect to the database as the PERFSTAT user as follows:
sqlplus perfstat/<password>
Create a snapshot with the statspack package as follows:
SQL> execute statspack.snap(i_snap_level=>7);
Exit SQLPLUS as follows:
SQL> exit
Generating Reports

Oracle Statspack comes with a comprehensive reporting script called spreport.sql. When this script is run, it outputs a list of available snapshots, asks the user for two snapshot IDs and a name for the report, and then outputs a text report of the results.
To run a Statspack report.
Navigate to the $ORACLE_HOME/rdbms/admin directory as follows:
# cd $ORACLE_HOME/rdbms/admin/
Run the standard Statspack report as follows:
# sqlplus perfstat/<password> @spreport
Enter a beginning snapshot ID.

Enter an ending snapshot ID.

Enter a name for the report or accept the default.

Exit SQLPLUS as follows:
SQL> exit
 
Zaman Ahmed Tatari
DATABASE ADMINISTRATOR
OCP 9i/10g(DBA/Developer Track) 
KASB BANK LIMITED
Cell #: 92-300-2542241