STATSPACK is a performance diagnosis tool, available since Oracle8i. STATSPACK can be considered BSTAT/ESTAT's successor, incorporating many new features. STATSPACK is a diagnosis tool for instance-wide performance problems; it also supports application tuning activities by providing data which identifies high-load SQL statements. STATSPACK can be used both proactively to monitor the changing load on a system, and also reactively to investigate a performance problem. STATSPACK has two types of collection options, level and threshold. The level parameter controls the type of data collected from Oracle, while the threshold parameter acts as a filter for the collection of SQL statements into the stats$sql_summary table. 

The only limitation of STATSPACK is that some DBAs may not be confortable with the character output. If you are one of them, why go to enjoy "JOraStat", a java-based oracle performance tuning tool, which offers graphical output against STATSPACK statistics. And, IT"S FREE! 

Source:  http://sourceforge.net/projects/jorastat/
Or
Download from http://www.itpub.ca/bigupload/RIT_JOraStat.rar

Please follow the following threads to install it.  See JOraStat Manual as attached.

Introduction
Oracle's Statspack is a free utility that allows you to monitor and report on performance problems with your Oracle database. In this tutorial we will install Statspack on an Oracle 9i database.

Creating a Statspack Tablespace
The first step in installing statspack is creating a tablespace for the statspack objects and data to reside. For our example we will call the tablespace "PERFSTAT" and make it 500mb in size.


SQL> CREATE TABLESPACE PERFSTAT
 2      DATAFILE '/path/to/my/tablespaces/perfstat.dbf' SIZE 500M AUTOEXTEND OFF
 3      EXTENT MANAGEMENT LOCAL AUTOALLOCATE
 4      LOGGING
 5      ONLINE
 6      SEGMENT SPACE MANAGEMENT AUTO
 7  /

Tablespace created.


Installing Statspack
Next, we're actually going to install Statspack in the database. First you want to change to the ORACLE_HOME/rdbms/admin/ directory. This directory contains all the files needed for installing statspack.

$> cd $ORACLE_HOME/rdbms/admin/

Now that we are in the ORACLE_HOME/rdbms/admin/ directory we are going to run the "spcreate.sql" script from SQLPLUS. This script will create the statspack user, the statspack tables, and the statspack packages.

$> sqlplus "/ as sysdba"

SQL*Plus: Release 9.2.0.4.0 - Production on Mon Nov 28 18:49:51 2005

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.


Connected to:
Oracle9i Enterprise Edition Release 9.2.0.4.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.4.0 - Production

SQL> @spcreate;
... Installing Required Packages

Package created.


Grant succeeded.


View created.


Package body created.


Package created.


Synonym created.


Grant succeeded.


View created.


Synonym created...


...Synonym created.

... Creating PERFSTAT user ...

Choose the PERFSTAT user's password.

Not specifying a password will result in the installation FAILING

Specify PERFSTAT password
Enter value for perfstat_password: perfstatpass
perfstatpass

PL/SQL procedure successfully completed.


User created....


....Grant succeeded.


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
------------------------------ ---------
DRSYS                          PERMANEN
PERFSTAT                       PERMANENT
TEMP                           TEMPORARY
TOOLS                          PERMANENT
UNDOTBS                        UNDO
USERS                          PERMANENT
XDB                            PERMANENT

7 rows selected.


Specify PERFSTAT user's default   tablespace
Enter value for default_tablespace: perfstat
Using perfstat for the default tablespace

PL/SQL procedure successfully completed.


User altered.


User altered.


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 value for temporary_tablespace: temp
Using temp for the temporary tablespace


PL/SQL procedure successfully completed.


User altered.....

Creating Package STATSPACK...

Package created.

No errors.
Creating Package Body STATSPACK...

Package body created.

No errors.

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

Review any messages and make sure you didn't receive any errors.


Execute a Snapshot and Generate a Statspack Report
Now that you have statspack installed, let's take two snapshots and then generate a Statspack Report. Assuming you are still in the ORACLE_HOME/rdbms/admin/ directory and logged in as perfstat, run this command twice like so:

SQL> execute statspack.snap

PL/SQL procedure successfully completed.

SQL> execute statspack.snap

PL/SQL procedure successfully completed.

We now have two snapshots to compare in order to create a Statspack Report. Run the "spreport.sql" script in SQLPLUS to create a Statspack Report:

SQL> @spreport

Current Instance
~~~~~~~~~~~~~~~~

  DB Id    DB Name      Inst Num Instance
----------- ------------ -------- ------------
4184632401 ORCL              1   ORCL



Instances in this Statspack schema
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

  DB Id    Inst Num DB Name      Instance     Host
----------- -------- ------------ ------------ ------------
4184632401        1 ORCL         ORCL         localhost

Using 4184632401 for database Id
Using          1 for instance number

Completed Snapshots

                          Snap                    Snap
Instance     DB Name         Id   Snap Started    Level Comment
------------ ------------ ----- ----------------- ----- ----------------------
ORCL         ORCL             1 28 Nov 2005 20:00     5
                             2 28 Nov 2005 20:04     5



Specify the Begin and End Snapshot Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Enter value for begin_snap: 1
Begin Snapshot Id specified: 1

Enter value for end_snap: 2
End   Snapshot Id specified: 2

Specify the Report Name
~~~~~~~~~~~~~~~~~~~~~~~
The default report file name is sp_1_2.  To use this name,
press  to continue, otherwise enter an alternative.
Enter value for report_name: myStatspackReport.txt

-- a bunch of the report flies by the screen here,
-- but don't worry we saved a copy to look at afterwards.

End of Report

SQL>


Here I saved my Statspack Report under the name "myStatspackReport.txt". You can exit SQLPLUS and open the file in a text editor to view the report.


Scheduling the Statspack Report
Now that you have statspack up and running, lets schedule it to run at an automatic interval. Again, assuming you are in the ORACLE_HOME/rdbms/admin/ directory and logged in as perfstat, run the "spauto.sql" script:

SQL> @spauto

PL/SQL procedure successfully completed.


Job number for automated statistics collection for this instance
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Note that this job number is needed when modifying or removing
the job:

    JOBNO
----------
        1


Job queue process
~~~~~~~~~~~~~~~~~
Below is the current setting of the job_queue_processes init.ora
parameter - the value for this parameter must be greater
than 0 to use automatic statistics gathering:

NAME                                 TYPE
------------------------------------ --------------------------------
VALUE
------------------------------
job_queue_processes                  integer
2


Next scheduled run
~~~~~~~~~~~~~~~~~~
The next scheduled run for this job is:

      JOB NEXT_DATE       NEXT_SEC
---------- --------------- ------------------------
        1 28-NOV-05       21:00:00


The "spauto.sql" script, by default, schedules the statspack job to take a snapshot every hour. If you want a statspack snapshot to be taken more often than that then make a copy of the spauto.sql file and edit it. You want to change the section that says:
variable jobno number;
variable instno number;
begin
 select instance_number into :instno from v$instance;
 dbms_job.submit(:jobno, 'statspack.snap;',
 trunc(sysdate+1/24,'HH'), 'trunc(SYSDATE+1/24,''HH'')', TRUE, :instno);
 commit;
end;
/

If you want the statspack to run every 10 minutes then change it to say this:

variable jobno number;
variable instno number;
begin
 select instance_number into :instno from v$instance;
 dbms_job.submit(:jobno, 'statspack.snap;',
 trunc(sysdate+1/144,'MI'), 'trunc(SYSDATE+1/144,''MI'')', TRUE, :instno);
 commit;
end;
/

Issue your copy of the spauto.sql script and it will schedule a snapshot to be taken every 10 minutes.


Check the job_queue_processes parameter
Make sure that you have the job_queue_processes parameter set higher than zero or you will not be able to use the auto snapshot feature to take statspack snapshots at a given interval.

SQL> set linesize 150;
SQL> show parameter job_queue_processes

NAME                                 TYPE                             VALUE
------------------------------------ -------------------------------- -----
job_queue_processes                  integer                          0

Here I don't have job_queue_processes set above zero so I issue a ALTER SYSTEM command to increase it to 2:

SQL> ALTER SYSTEM SET job_queue_processes = 2 SCOPE=BOTH;

System altered.

SQL> SHOW PARAMETER job_queue_processes

NAME                                 TYPE                             VALUE
------------------------------------ -------------------------------- -----
job_queue_processes                  integer                          2


How to Uninstall Statspack
To uninstall Statspack from an Oracle database running Oracle 8.1.7 and above, you just need to run the ORACLE_HOME/rdbms/admin/spdrop.sql script. This will get rid of the user, tables, and procedures.

To uninstall Statspack from an Oracle database running Oracle 8.1.6, you just need to run the ORACLE_HOME/rdbms/admin/spdrp.sql

There are two steps to install JOraStat after the installation of STATSPACK. As demonstrated in page 16-17 of JOraStat Manual (JOraStat_manual_v1.pdf, which is downloadbale from the 1st thread of this topic.), the post-steps are incorrect or incomplete.  These two steps are:

• Setup STATSPACK parameters
  [url=http://www.oraclepoint.com]sYOjKedgUOraclePointD1mTBBXWE[/url]
  
  
  
• Automating STATSPACK snapshot
  
  [url=http://www.oraclepoint.com]wHjIapNCIOraclePointyYgtG58qF[/url]
  
  

 Against Oracle 9ir2, my testing shows that the following commands can perform requried objetives. 

• Setup STATSPACK parameters  (Set up Level 6 for STATSPACK)

                 exec statspack.snap(i_snap_level => 6, i_modify_parameter => 'true');
                 EXECUTE STATSPACK.SNAP(i_snap_level=>6); <-- this is used if you want a one shot.
                 EXECUTE STATSPACK.MODIFY_STATSPACK_PARAMETER(i_snap_level => 6);

• Automating STATSPACK snapshot

                         SQL> spauto.sql                 

The "spauto.sql" script, by default, schedules the statspack job to take a snapshot every hour. If you want a statspack snapshot to be taken more often than that then make a copy of the spauto.sql file and edit it. You want to change the section that says: