Oracle STATSPACK installation steps

http://www.dba-oracle.com/t_statspack_installation_steps.htm

http://www.dba-oracle.com/t_statspack_install_scripts.htm

Step 1: Create the perfstat Tablespace

Next, we create a tablespace called perfstat with at least 180 megabytes of space in the datafile:

>sqlplus /

SQL*Plus: Release 8.1.6.0.0 - Production on Tue Dec 12 14:08:11 2000

(c) Copyright 1999 Oracle Corporation. All rights reserved.


Connected to:
Oracle8i Enterprise Edition Release 8.1.6.1.0 - 64bit Production
With the Partitioning option
JServer Release 8.1.6.1.0 - 64bit Production

SQL> create tablespace perfstat 
  2  datafile '/u03/oradata/prodb1/perfstat.dbf'   
size 500m;

Step 2: Run the create Scripts

>cd $ORACLE_HOME/rdbms/admin

>ls -al sp*

-rw-r--r--    1 oracle   oinstall     1771 May 10  2001 spauto.sql
-rw-r--r--    1 oracle   oinstall    82227 May 10  2001 spcpkg.sql
-rw-r--r--    1 oracle   oinstall      877 May 10  2001 spcreate.sql
-rw-r--r--    1 oracle   oinstall    42294 May 10  2001 spctab.sql
-rw-r--r--    1 oracle   oinstall     7949 May 10  2001 spcusr.sql
-rw-r--r--    1 oracle   oinstall    69074 May 10  2001 spdoc.txt
-rw-r--r--    1 oracle   oinstall      758 May 10  2001 spdrop.sql
-rw-r--r--    1 oracle   oinstall     4342 May 10  2001 spdtab.sql
-rw-r--r--    1 oracle   oinstall     1363 May 10  2001 spdusr.sql
-rw-r--r--    1 oracle   oinstall     7760 May 10  2001 sppurge.sql
-rw-r--r--    1 oracle   oinstall   113753 May 10  2001 sprepins.sql
-rw-r--r--    1 oracle   oinstall     1284 May 10  2001 spreport.sql
-rw-r--r--    1 oracle   oinstall    26556 May 10  2001 sprepsql.sql
-rw-r--r--    1 oracle   oinstall     2726 May 10  2001 sptrunc.sql
-rw-r--r--    1 oracle   oinstall      588 May 10  2001 spuexp.par
-rw-r--r--    1 oracle   oinstall    30462 May 10  2001 spup816.sql
-rw-r--r--    1 oracle   oinstall    23309 May 10  2001 spup817.sql

·        statscre.sql This is the first install script run after you create the tablespace. It calls several subscripts:

·        Specify PERFSTAT user's default tablespace: perfstat

·        Specify PERFSTAT user's temporary tablespace: temp

·        Enter tablespace where STATSPACK objects will be created: perfstat

Install Prerequisites

Note that you must have performed the following before attempting to install STATSPACK:

1.      Run catdbsyn.sql when connected as SYS.

2.      Run dbmspool.sql when connected as SYS.

3.      Allocate a tablespace called perfstat with at least 180 megabytes of storage.

Step 3: Test the STATSPACK Install

To ensure that everything is installed correctly, we can demand two snapshots and then request an elapsed-time report. To execute a STATSPACK snapshot, we enter the statspack.snap procedure. If we do this twice, we will have two snapshots, and we can run the statsrep.sql report to ensure that everything is working properly. Here is the test to ensure that the install works properly. If you get a meaningful report after entering statsrep, then the install was successful. Also, note that the statsrep.sql script has an EXIT statement, so it will return you to the UNIX prompt when it has completed:

SQL> execute statspack.snap
PL/SQL procedure successfully completed.
SQL> execute statspack.snap
PL/SQL procedure successfully completed.
SQL> @spreport
. . .

Step 4: Schedule Automatic STATSPACK Data Collections

Now that we have verified that STATSPACK is installed and working, we can schedule automatic data collection. By using the statsauto.sql script we can automatically schedule an hourly data collection for STATSPACK. The statsauto.sql script contains the following directive:

SQL> execute dbms_job.submit(:jobno, 'statspack.snap;',
trunc(sysdate+1/24,'HH'), 'trunc(SYSDATE+1/24,''HH'')', TRUE, :instno);

The important thing to note in this call to dbms_job.submit is the execution interval. The SYSDATE+1/24 is the interval that is stored in the dba_jobs view to produce hourly snapshots. You can change this as follows for different sample times. There are 1,440 minutes in a day, and you can use this figure to adjust the execution times.

Table 1 gives you the divisors for the snapshot intervals.

Minutes per Day	Minutes between Snapshots	Required Divisor
1,440	60	24
1,440	30	48
1,440	10	144
1,440	5	288

Table 1: Determining the Snapshot Interval

Hence, if we want a snapshot every ten minutes we would issue the following command:

SQL> execute dbms_job.submit(:jobno, 'statspack.snap;',
trunc(sysdate+1/144,'MI'), 'trunc(SYSDATE+1/144,''MI'')', TRUE, :instno);

In the real world, you may have times where you want to sample the database over short time intervals. For example, if you have noticed that a performance problem happens every day between 4:00 p.m. and 5:00 p.m., you can request more frequent snapshots during this period.

For normal use, you probably want to accept the hourly default and execute a snapshot every hour. Below is the standard output from running the statsauto.sql script:

SQL> connect perfstat/perfstat;
Connected.
SQL> @statsauto
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 1

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

       JOB NEXT_DATE NEXT_SEC
---------- --------- --------
         1 12-MAY-02 16:00:00

We can now see that a STATSPACK snapshot will automatically be executed every hour. We see that this is scheduled as job number 1, and we can use this job number to cancel this collection at any time using the dbms_job.remove procedure:

SQL> execute dbms_job.remove(1);