Archive for the ‘Statspack’ Category

Enabling Statspack for 11gR2 RAC

With 11g standard edition you can no longer buy the tuning and diagnostic packs so no awr reports so back to the old statspack, this is the steps i used to install statspack on my 2 node 11gR2 RAC cluster.

1. Create the perfstat schema

SQL> @?/rdbms/admin/spcreate
Choose the PERFSTAT user's password
-----------------------------------
Not specifying a password will result in the installation FAILING

Enter value for perfstat_password: xxxxxx
xxxxxx

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
------------------------------ --------- ----------------------------
SYSAUX                         PERMANENT *
USERS                          PERMANENT

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

Enter value for default_tablespace: SYSAUX

Using tablespace SYSAUX 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  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

... Granting privileges

NOTE:
SPCUSR complete. Please check spcusr.lis for any errors.

SQL>
SQL> --
SQL> --  Build the tables and synonyms
SQL> connect perfstat/&&perfstat_password
Connected.
SQL> @@spctab
SQL> Rem
SQL> Rem $Header: rdbms/admin/spctab.sql /st_rdbms_11.2.0/1 2010/08/13 10:06:01 kchou Exp $
SQL> Rem
SQL> Rem spctab.sql
SQL> Rem
SQL> Rem Copyright (c) 1999, 2010, Oracle and/or its affiliates.
SQL> Rem All rights reserved.
SQL> Rem
SQL> Rem    NAME
SQL> Rem         spctab.sql
SQL> Rem
SQL> Rem    DESCRIPTION
SQL> Rem         SQL*PLUS command file to create tables to hold
SQL> Rem         start and end "snapshot" statistical information
SQL> Rem
SQL> Rem    NOTES
SQL> Rem         Should be run as STATSPACK user, PERFSTAT
SQL> Rem
SQL> Rem    MODIFIED   (MM/DD/YY)
SQL> Rem    kchou       08/11/10 - Bug#9800868 - Add Missing Idle Events for
SQL> Rem                           11.2.0.2for Statspack & Standby Statspack
SQL> Rem    kchou       08/11/10 - Bug#9800868 - Add missing idle events to 11.2.0.2
SQL> Rem    cgervasi    05/13/09 - add idle event: cell worker idle
SQL> Rem    cgervasi    04/02/09 - bug8395154: missing idle events
SQL> Rem    rhlee       02/22/08 -
> Rem    cdgreen     03/14/07 - 11 F2
SQL> Rem    shsong      06/14/07 - Add idle events
SQL> Rem    cdgreen     02/28/07 - 5908354
SQL> Rem    cdgreen     04/26/06 - 11 F1
SQL> Rem    cdgreen     06/26/06 - Increase column length
SQL> Rem    cdgreen     05/10/06 - 5215982
SQL> Rem    cdgreen     05/24/05 - 4246955
SQL> Rem    cdgreen     04/18/05 - 4228432
SQL> Rem    cdgreen     03/08/05 - 10gR2 misc
SQL> Rem    vbarrier    02/18/05 - 4081984
SQL> Rem    cdgreen     10/29/04 - 10gR2_sqlstats
SQL> Rem    cdgreen     07/16/04 - 10gR2
SQL> Rem    cdialeri    03/25/04 - 3516921
SQL> Rem    vbarrier    02/12/04 - 3412853
SQL> Rem    cdialeri    12/04/03 - 3290482
SQL> Rem    cdialeri    11/05/03 - 3202706
SQL> Rem    cdialeri    10/14/03 - 10g - streams - rvenkate
SQL> Rem    cdialeri    08/05/03 - 10g F3
SQL> Rem    cdialeri    02/27/03 - 10g F2: baseline, purge
SQL> Rem    vbarrier    02/25/03 - 10g RAC
SQL> Rem    cdialeri    11/15/02 - 10g F1
SQL> Rem    cdialeri    09/27/02 - sleep4
SQL> Rem    vbarrier    03/20/02 - 2143634
SQL> Rem    vbarrier    03/05/02 - Segment Statistics
SQL> Rem    cdialeri    02/07/02 - 2218573
SQL> Rem    cdialeri    01/30/02 - 2184717
SQL> Rem    cdialeri    01/11/02 - 9.2 - features 2
SQL> Rem    cdialeri    11/30/01 - 9.2 - features 1
SQL> Rem    cdialeri    04/22/01 - Undostat changes
SQL> Rem    cdialeri    03/02/01 - 9.0
SQL> Rem    cdialeri    09/12/00 - sp_1404195
SQL> Rem    cdialeri    04/07/00 - 1261813
SQL> Rem    cdialeri    03/20/00 - Support for purge
SQL> Rem    cdialeri    02/16/00 - 1191805
SQL> Rem    cdialeri    01/26/00 - 1169401
SQL> Rem    cdialeri    11/01/99 - Enhance, 1059172
SQL> Rem    cmlim       07/17/97 - Added STATS$SQLAREA to store top sql stmts
SQL> Rem    gwood       10/16/95 - Version to run as sys without using many views
SQL> Rem    cellis.uk   11/15/89 - Created
SQL> Rem
SQL>
SQL> set showmode off echo off;

If this script is automatically called from spcreate (which is
the supported method), all STATSPACK segments will be created in
the PERFSTAT user's default tablespace.

Using SYSAUX tablespace to store Statspack objects

... Creating STATS$SNAPSHOT_ID Sequence

Sequence created.

Synonym created.

... Creating STATS$... tables

Table created.

Synonym created.

Table created.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

Commit complete.

Synonym created.

Table created.

Synonym created.

Table created.

Synonym created.

Table created.

Synonym created.

Table created.

Synonym created.

Table created.

Synonym created.

Table created.

Synonym created.

Table created.

Synonym created.

Table created.

Synonym created.

Table created.

Synonym created.

Table created.

Synonym created.

Table created.

Synonym created.

Table created.

Synonym created.

Table created.

Synonym created.

Table created.

Synonym created.

Table created.

Synonym created.

Table created.

Synonym created.

Table created.

Synonym created.

Table created.

Synonym created.

Table created.

Synonym created.

Table created.

Synonym created.

Table created.

Synonym created.

Table created.

Synonym created.

Table created.

Synonym created.

Table created.

Synonym created.

Table created.

Synonym created.

Table created.

Synonym created.

Table created.

Synonym created.

Table created.

Synonym created.

Table created.

Synonym created.

Table created.

Index created.

Synonym created.

Table created.

Synonym created.

Table created.

Synonym created.

Table created.

Synonym created.

Table created.

Synonym created.

Table created.

Synonym created.

Table created.

Synonym created.

Table created.

Synonym created.

Table created.

Synonym created.

Table created.

Synonym created.

Table created.

Synonym created.

Table created.

Synonym created.

Table created.

Synonym created.

Table created.

Synonym created.

Table created.

Synonym created.

Table created.

Synonym created.

Table created.

Synonym created.

Table created.

Synonym created.

Table created.

Synonym created.

Table created.

Synonym created.

Table created.

Synonym created.

Table created.

Synonym created.

Table created.

Synonym created.

Table created.

Synonym created.

Table created.

Synonym created.

Table created.

Synonym created.

Table created.

Synonym created.

Table created.

Synonym created.

Table created.

Synonym created.

Table created.

Synonym created.

Table created.

Synonym created.

Table created.

Synonym created.

Table created.

Synonym created.

Table created.

Synonym created.

Table created.

Synonym created.

Table created.

Synonym created.

Table created.

Synonym created.

Table created.

Synonym created.

Table created.

Synonym created.

Table created.

Synonym created.

View created.

Synonym created.

Table created.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

Commit complete.

Synonym created.

Synonym created.

NOTE:
SPCTAB complete. Please check spctab.lis for any errors.

SQL> --  Create the statistics Package
SQL> @@spcpkg
SQL> Rem
SQL> Rem $Header: spcpkg.sql 28-jan-2008.21:06:26 arogers Exp $
SQL> Rem
SQL> Rem spcpkg.sql
SQL> Rem
SQL> Rem Copyright (c) 1999, 2008, Oracle. All rights reserved.
SQL> Rem
SQL> Rem    NAME
SQL> Rem         spcpkg.sql
SQL> Rem
SQL> Rem    DESCRIPTION
SQL> Rem         SQL*PLUS command file to create statistics package
SQL> Rem
SQL> Rem    NOTES
SQL> Rem         Must be run as the STATSPACK owner, PERFSTAT
SQL> Rem
SQL> Rem    MODIFIED   (MM/DD/YY)
SQL> Rem    arogers     01/23/08 - 6523482 - change VM_IN/OUT_BYTES id numbers
SQL> Rem    cdgreen     03/14/07 - 11 F2
SQL> Rem    shsong      06/14/07 - Fix BUFFER_GETS
SQL> Rem    cdgreen     04/05/07 - 5691086
SQL> Rem    cdgreen     03/02/07 - use _FG for v$system_event
SQL> Rem    cdgreen     03/02/07 - 5913378
SQL> Rem    cdgreen     05/16/06 - 11 F1
SQL> Rem    cdgreen     05/10/06 - 5215982
SQL> Rem    cdgreen     05/24/05 - 4246955
SQL> Rem    cdgreen     04/18/05 - 4228432
SQL> Rem    cdgreen     02/28/05 - 10gR2 misc
SQL> Rem    vbarrier    02/18/05 - 4081984
SQL> Rem    cdgreen     01/25/05 - 4143812
SQL> Rem    cdgreen     10/29/04 - 10gR2_sqlstats
SQL> Rem    cdgreen     10/25/04 - 3970898
SQL> Rem    cdgreen     07/16/04 - 10g R2
SQL> Rem    vbarrier    03/18/04 - 3517841
SQL> Rem    vbarrier    02/12/04 - 3412853
SQL> Rem    cdialeri    12/04/03 - 3290482
SQL> Rem    cdialeri    11/05/03 - 3202706
SQL> Rem    cdialeri    10/14/03 - 10g - streams - rvenkate
SQL> Rem    cdialeri    08/05/03 - 10g F3
SQL> Rem    cdialeri    07/31/03 - 2804307
SQL> Rem    vbarrier    02/25/03 - 10g RAC
SQL> Rem    cdialeri    01/28/03 - 10g F2: baseline, purge
SQL> Rem    cdialeri    11/15/02 - 10g F1
SQL> Rem    cdialeri    10/29/02 - 2648471
SQL> Rem    cdialeri    09/11/02 - 1995145
SQL> Rem    vbarrier    04/18/02 - 2271895
SQL> Rem    vbarrier    03/20/02 - 2184504
SQL> Rem    spommere    03/19/02 - 2274095
SQL> Rem    vbarrier    03/05/02 - Segment Statistics
SQL> Rem    spommere    02/14/02 - cleanup RAC stats that are no longer needed
SQL> Rem    spommere    02/08/02 - 2212357
SQL> Rem    cdialeri    02/07/02 - 2218573
SQL> Rem    cdialeri    01/30/02 - 2184717
SQL> Rem    cdialeri    01/09/02 - 9.2 - features 2
SQL> Rem    cdialeri    11/30/01 - 9.2 - features 1
SQL> Rem    hbergh      08/23/01 - 1940915: use substrb on sql_text
SQL> Rem    cdialeri    04/26/01 - 9.0
SQL> Rem    cdialeri    09/12/00 - sp_1404195
SQL> Rem    cdialeri    04/07/00 - 1261813
SQL> Rem    cdialeri    03/28/00 - sp_purge
SQL> Rem    cdialeri    02/16/00 - 1191805
SQL> Rem    cdialeri    11/01/99 - Enhance, 1059172
SQL> Rem    cgervasi    06/16/98 - Remove references to wrqs
SQL> Rem    cmlim       07/30/97 - Modified system events
SQL> Rem    gwood.uk    02/30/94 - Modified
SQL> Rem    densor.uk   03/31/93 - Modified
SQL> Rem    cellis.uk   11/15/89 - Created
SQL> Rem
SQL>
SQL> set echo off;
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.

2. Set-up the automatic job on instance 1, this will schedule the job to run on the hour. Run as the perfstat user

SQL> @/u01/app/oracle/product/11.2.0.3/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.02
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
----------
        53

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
---------- --------- --------------------------------
        53 03-OCT-13 16:00:00

1 row selected.

Elapsed: 00:00:00.00
SQL>
SQL> spool off;

3.Set up the job to run on the second node but to run on quarter past the hour to avoid any locks on the perfstat objects, run as the perfstat user

VARIABLE jobno NUMBER;
EXEC DBMS_JOB.SUBMIT (:jobno, 'statspack.snap;' , sysdate+0.25/24 , 'sysdate+1/24', TRUE, 2);
commit;

4.Some useful statspak information

Statspack – the basic source of information is $ORACLE_HOME/rdbms/admin/spdoc.txt
spreport.sql -> Generates a Statspack Instance report
sprepins.sql -> Generates a Statspack Instance report for the database and instance specified
sprepsql.sql -> Generates a Statspack SQL report for the SQL Hash Value specified
sprsqins.sql -> Generates a Statspack SQL report for the SQL Hash Value specified, for the database and instance specified

sppurge.sql -> Purges a limited range of Snapshot Id’s for a given database instance
sptrunc.sql -> Truncates all Performance data in Statspack tables
spuexp.par -> An export parameter file supplied for exporting the whole PERFSTAT user