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
For the Love of Data 