Install STATSPACK

##Check if user is present##

Select * from dba_users where username='PERFSTAT';
SQL> Select * from dba_users where username='PERFSTAT';

no rows selected


###Create a tablespace###

SQL> select file_name from dba_data_files order by 1;

FILE_NAME
--------------------------------------------------------------------------------
E:\ORACLE\ORADATA\CAQ2000\CAQ2000_01.DBF
E:\ORACLE\ORADATA\CAQ2000\CAQ2000_02.DBF
E:\ORACLE\ORADATA\CAQ2000\CAQ2000_03.DBF
E:\ORACLE\ORADATA\CAQ2000\CAQ2000_04.DBF
E:\ORACLE\ORADATA\CAQ2000\SYSAUX01.DBF
E:\ORACLE\ORADATA\CAQ2000\SYSTEM01.DBF
E:\ORACLE\ORADATA\CAQ2000\UNDOTBS01.DBF
E:\ORACLE\ORADATA\CAQ2000\USERS01.DBF

8 rows selected.

SQL> create tablespace perfstat datafile 'E:\ORACLE\ORADATA\CAQ2000\PERFSTAT_01.dbf' size 1G autoextend on maxsize unlimited;

Tablespace created.

###Run the below script to install statspack##
SQL> @?/rdbms/admin/spcreate.sql

Choose the PERFSTAT user's password
-----------------------------------
Choose the PERFSTAT user's password
-----------------------------------
Not specifying a password will result in the installation FAILING

Enter value for perfstat_password: perfstat
perfstat


Choose the Default tablespace for the PERFSTAT user
---------------------------------------------------
Below is the list of online tablespaces in this database which can
store user data.  Specifying the SYSTEM tablespace for the user's
default tablespace will result in the installation FAILING, as
using SYSTEM for performance data is not supported.

Choose the PERFSTAT users's default tablespace.  This is the tablespace
in which the STATSPACK tables and indexes will be created.

TABLESPACE_NAME                CONTENTS  STATSPACK DEFAULT TABLESPACE
------------------------------ --------- ----------------------------
CAQ2000                        PERMANENT
PERFSTAT                       PERMANENT
SYSAUX                         PERMANENT *
USERS                          PERMANENT

Pressing <return> will result in STATSPACK's recommended default
tablespace (identified by *) being used.

Enter value for default_tablespace: PERFSTAT

Using tablespace PERFSTAT as PERFSTAT default tablespace.


Choose the Temporary tablespace for the PERFSTAT user
-----------------------------------------------------
Below is the list of online tablespaces in this database which can
store temporary data (e.g. for sort workareas).  Specifying the SYSTEM
tablespace for the user's temporary tablespace will result in the
installation FAILING, as using SYSTEM for workareas is not supported.

Choose the PERFSTAT user's Temporary tablespace.

TABLESPACE_NAME                CONTENTS  DB DEFAULT TEMP TABLESPACE
------------------------------ --------- --------------------------
TEMP                           TEMPORARY *

Pressing <return> will result in the database's default Temporary
tablespace (identified by *) being used.

Enter value for temporary_tablespace: TEMP

Using tablespace TEMP as PERFSTAT temporary tablespace.


... Creating PERFSTAT user


... Installing required packages


... Creating views

QL> Rem
QL>
QL> set echo off;
reating Package STATSPACK...

ackage created.

o errors.
reating Package Body STATSPACK...

ackage body created.

o errors.

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

###Schedule the job##

SQL> conn perfstat/perfstat/

Enter password:

Connected.

SQL>

SQL>

SQL>

SQL> show user

USER is "PERFSTAT"

SQL>

SQL>

SQL> @?/rdbms/admin/spauto.sql

SQL> Rem

SQL> Rem $Header: spauto.sql 16-feb-00.16:49:37 cdialeri Exp $

SQL> Rem

SQL> Rem spauto.sql

SQL> Rem

SQL> Rem  Copyright (c) Oracle Corporation 1999, 2000. All Rights Reserved.

SQL> Rem

SQL> Rem    NAME

SQL> Rem         spauto.sql

SQL> Rem

SQL> Rem    DESCRIPTION

SQL> Rem         SQL*PLUS command file to automate the collection of STATPACK

SQL> Rem         statistics.

SQL> Rem

SQL> Rem    NOTES

SQL> Rem         Should be run as the STATSPACK owner, PERFSTAT.

SQL> Rem         Requires job_queue_processes init.ora parameter to be

SQL> Rem         set to a number >0 before automatic statistics gathering

SQL> Rem         will run.

SQL> Rem

SQL> Rem    MODIFIED   (MM/DD/YY)

SQL> Rem    cdialeri    02/16/00 - 1191805

SQL> Rem    cdialeri    12/06/99 - 1059172, 1103031

SQL> Rem    cdialeri    08/13/99 - Created

SQL> Rem

SQL>

SQL>

SQL> spool spauto.lis

SQL>

SQL> --

SQL> --  Schedule a snapshot to be run on this instance every hour, on the hour

SQL>

SQL> variable jobno number;

SQL> variable instno number;

SQL> begin

  2    select instance_number into :instno from v$instance;

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

  4    commit;

  5  end;

  6  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.43

SQL>

SQL>

SQL> prompt

SQL> prompt     Job number for automated statistics collection for this instance

Job number for automated statistics collection for this instance

SQL> prompt     ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

SQL> prompt     Note that this job number is needed when modifying or removing

Note that this job number is needed when modifying or removing

SQL> prompt     the job:

the job:

SQL> print jobno

     JOBNO

----------

        21

SQL>

SQL> prompt

SQL> prompt     Job queue process

Job queue process

SQL> prompt     ~~~~~~~~~~~~~~~~~

~~~~~~~~~~~~~~~~~

SQL> prompt     Below is the current setting of the job_queue_processes init.ora

Below is the current setting of the job_queue_processes init.ora

SQL> prompt     parameter - the value for this parameter must be greater

parameter - the value for this parameter must be greater

SQL> prompt     than 0 to use automatic statistics gathering:

than 0 to use automatic statistics gathering:

SQL> show parameter job_queue_processes

NAME                                 TYPE                              VALUE

------------------------------------ --------------------------------- ------------------------------

job_queue_processes                  integer                           1000

SQL> prompt

SQL>

SQL> prompt

SQL> prompt     Next scheduled run

Next scheduled run

SQL> prompt     ~~~~~~~~~~~~~~~~~~

~~~~~~~~~~~~~~~~~~

SQL> prompt     The next scheduled run for this job is:

The next scheduled run for this job is:

SQL> select job, next_date, next_sec

  2    from user_jobs

  3   where job = :jobno;

       JOB NEXT_DATE       NEXT_SEC

---------- --------------- ------------------------

        21 02-JUN-17       13:00:00

1 row selected.

Elapsed: 00:00:00.01

SQL>

SQL> spool off;

SQL>

SQL> select name,snap_id,to_char(snap_time,'DD.MM.YYYY:HH24:MI:SS') "Date/Time" from stats$snapshot,v$database;

no rows selected

Elapsed: 00:00:00.03

SQL> EXECUTE statspack.snap;

PL/SQL procedure successfully completed.

Elapsed: 00:00:03.66

SQL> select name,snap_id,to_char(snap_time,'DD.MM.YYYY:HH24:MI:SS') "Date/Time" from stats$snapshot,v$database;

NAME          SNAP_ID Date/Time

---------- ---------- ---------------------------------------------------------

UNIORCL             1 02.06.2017:12:55:21

1 row selected.

Elapsed: 00:00:00.00

SQL>

SQL>

SQL> col WHAT for a60

SQL> col PRIV_USER for a12

SQL> col SCHEMA_USER for a12

SQL> col NEXT_DATE for a20

SQL> col INTERVAL for a25

SQL> SELECT JOB,PRIV_USER,SCHEMA_USER,NEXT_DATE,INTERVAL,FAILURES,BROKEN,WHAT FROM DBA_JOBS where job=21;

       JOB PRIV_USER    SCHEMA_USER  NEXT_DATE            INTERVAL                    FAILURES BRO WHAT

---------- ------------ ------------ -------------------- ------------------------- ---------- --- ------------------------------------------------------------

        21 PERFSTAT     PERFSTAT     02-JUN-17            trunc(SYSDATE+1/24,'HH')             N   statspack.snap;

##To change the snap interval to 30 mins###

SQL> execute dbms_job.interval(21,'SYSDATE+(1/48)');

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.03

SQL> SELECT JOB,PRIV_USER,SCHEMA_USER,NEXT_DATE,INTERVAL,FAILURES,BROKEN,WHAT FROM DBA_JOBS where job=21;

       JOB PRIV_USER    SCHEMA_USER  NEXT_DATE            INTERVAL                    FAILURES BRO WHAT

---------- ------------ ------------ -------------------- ------------------------- ---------- --- ------------------------------------------------------------

        21 PERFSTAT     PERFSTAT     02-JUN-17            SYSDATE+(1/48)                     0 N   statspack.snap;

1 row selected.

Elapsed: 00:00:00.01

SQL> alter session set nls_date_format='DD-MON-YYYY HH24:mi:ss';

Session altered.

Elapsed: 00:00:00.00

SQL> SELECT JOB,PRIV_USER,SCHEMA_USER,NEXT_DATE,INTERVAL,FAILURES,BROKEN,WHAT FROM DBA_JOBS where job=21;

       JOB PRIV_USER    SCHEMA_USER  NEXT_DATE            INTERVAL                    FAILURES BRO WHAT

---------- ------------ ------------ -------------------- ------------------------- ---------- --- ------------------------------------------------------------

        21 PERFSTAT     PERFSTAT     02-JUN-2017 14:00:00 SYSDATE+(1/48)                     0 N   statspack.snap;

1 row selected.

Elapsed: 00:00:00.00

SQL> select sysdate from dual;

SYSDATE

--------------------------

02-JUN-2017 13:02:46

1 row selected.

######To schedule the purge job####

QL> variable v_jobno number;

SQL> begin

  2    dbms_job.submit(:v_jobno,

  3                    'statspack.purge(i_num_days=>;30,i_extended_purge=>;TRUE);',

  4                    trunc(sysdate+1),

  5                    'trunc(sysdate+1)',

  6                    TRUE);

  7    commit;

  8  end;

  9  /

PL/SQL procedure successfully completed.

##Check the status####

SQL> SELECT JOB,PRIV_USER,SCHEMA_USER,NEXT_DATE,INTERVAL,FAILURES,BROKEN,WHAT FROM DBA_JOBS where PRIV_USER='PERFSTAT';

       JOB PRIV_USER    SCHEMA_USER  NEXT_DATE            INTERVAL                    FAILURES BRO WHAT

---------- ------------ ------------ -------------------- ------------------------- ---------- --- ------------------------------------------------------------

        21 PERFSTAT     PERFSTAT     02-JUN-2017 14:00:00 SYSDATE+(1/48)                     0 N   statspack.snap;

        22 PERFSTAT     PERFSTAT     03-JUN-2017 00:00:00 trunc(sysdate+1)                     N   statspack.purge(i_num_days=>;30,i_extended_purge=>;TRUE);