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