When you are configuring statspack perfstat user will be created. It will ask for a default tablespace and temporary tablespace to store its data.
It is recommended to create a separate tablespace for statspack.
SQL> create tablespace perfstat datafile 'D:\app\oradata\orcl\perfstat.dbf' size 1g autoextend on;
SQL>@?/rdbms/admin/catdbsyn.sql
NAME
catdbsyn.sql - catalog dba synonyms
DESCRIPTION
Creates private synonyms for DBA-only dictionary views.
NOTES
This file is made obsolete as DBA is now a role. All DBA_% catalog
views have a corresponding public synonym, and are accessible to
any user with SELECT ANY TABLE privilege.
SQL>@?/rdbms/admin/dbmspool.sql
NAME
dbmspool.sql - dbms_shared_pool utility package.
DESCRIPTION
This package allows you to display the sizes of objects in the
shared pool, and mark them for keeping or unkeeping in order to
reduce memory fragmentation.
SQL>@?/rdbms/admin/spcreate.sql
SQL> @?/rdbms/admin/spcreate.sql
SQL> Rem
SQL> Rem $Header: spcreate.sql 16-apr-2002.11:22:55 vbarrier Exp $
SQL> Rem
SQL> Rem spcreate.sql
SQL> Rem
SQL> Rem Copyright (c) 1999, 2002, Oracle Corporation. All rights reserved.
SQL> Rem
SQL> Rem NAME
SQL> Rem spcreate.sql - Statistics Create
SQL> Rem
SQL> Rem DESCRIPTION
SQL> Rem SQL*PLUS command file which creates the STATSPACK user,
SQL> Rem tables and package for the performance diagnostic tool STATSPACK
SQL> Rem
SQL> Rem NOTES
SQL> Rem Note the script connects INTERNAL and so must be run from
SQL> Rem an account which is able to connect internal.
SQL> Rem
SQL> Rem MODIFIED (MM/DD/YY)
SQL> Rem cdialeri 02/16/00 - 1191805
SQL> Rem cdialeri 12/06/99 - 1103031
SQL> Rem cdialeri 08/13/99 - Created
SQL> Rem
SQL>
SQL> --
SQL> -- Create PERFSTAT user and required privileges
SQL> @@spcusr
SQL> Rem
SQL> Rem $Header: rdbms/admin/spcusr.sql /st_rdbms_11.2.0/2 2012/03/06 15:07:48 shsong Exp $
SQL> Rem
SQL> Rem spcusr.sql
SQL> Rem
SQL> Rem Copyright (c) 1999, 2012, Oracle and/or its affiliates.
SQL> Rem All rights reserved.
SQL> Rem
SQL> Rem NAME
SQL> Rem spcusr.sql
SQL> Rem
SQL> Rem DESCRIPTION
SQL> Rem SQL*Plus command file to create user which will contain the
SQL> Rem STATSPACK database objects.
SQL> Rem
SQL> Rem NOTES
SQL> Rem Must be run from connected to SYS (or internal)
SQL> Rem
SQL> Rem MODIFIED (MM/DD/YY)
SQL> Rem shsong 01/30/09 - update STATS$V_$FILESTATXS etc
SQL> Rem shsong 07/11/08 - add stats$x$kccfe etc
SQL> Rem cdgreen 03/14/07 - 11 F2
SQL> Rem cdgreen 06/01/06 - 11 F1
SQL> Rem cdgreen 05/10/06 - 5215982
SQL> Rem cdgreen 08/22/05 - 4562627
SQL> Rem cdgreen 05/24/05 - 4246955
SQL> Rem cdgreen 04/18/05 - 4228432
SQL> Rem cdgreen 10/29/04 - 10gR2_sqlstats
SQL> Rem cdgreen 08/12/04 - 10g R2
SQL> Rem vbarrier 02/12/04 - 3412853
SQL> Rem cdialeri 12/04/03 - 3290482
SQL> Rem cdialeri 10/14/03 - 10g - streams - rvenkate
SQL> Rem cdialeri 08/05/03 - 10g F3
SQL> Rem vbarrier 02/25/03 - 10g RAC
SQL> Rem cdialeri 11/15/02 - 10g F1
SQL> Rem vbarrier 09/06/02 - SYSAUX and db default temp tbs
SQL> Rem vbarrier 04/01/02 - 2290728
SQL> Rem vbarrier 03/05/02 - Segment Statistics
SQL> Rem cdialeri 02/07/02 - 2218573
SQL> Rem cdialeri 11/30/01 - 9.2 - features 1
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 02/16/00 - 1191805
SQL> Rem cdialeri 01/26/00 - 1169401
SQL> Rem cdialeri 11/01/99 - 1059172
SQL> Rem cdialeri 08/13/99 - Created
SQL> Rem
SQL>
SQL> set echo off verify off showmode off feedback off;
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
------------------------------ --------- ----------------------------
INDX PERMANENT
PERFSTAT PERMANENT
SYSAUX PERMANENT *
USR 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
------------------------------ --------- --------------------------
TEMPORARY TEMPORARY *
Pressing <return> will result in the database's default Temporary
tablespace (identified by *) being used.
Enter value for temporary_tablespace:
Enter value for temporary_tablespace:
Using tablespace TEMPORARY 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/3 2012/03/06 15:07:48 shsong Exp $
SQL> Rem
SQL> Rem spctab.sql
SQL> Rem
SQL> Rem Copyright (c) 1999, 2012, 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 perfstat 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: rdbms/admin/spcpkg.sql /st_rdbms_11.2.0/2 2012/03/06 15:07:48 shsong Exp $
SQL> Rem
SQL> Rem spcpkg.sql
SQL> Rem
SQL> Rem Copyright (c) 1999, 2012, Oracle and/or its affiliates.
SQL> Rem 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.
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.
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
----------
102
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
---------- --------- --------
102 21-JUN-18 13:00:00
1 row selected.
SQL>
SQL> spool off;
SQL> exec statspack.snap
It is recommended to create a separate tablespace for statspack.
To configure spreport follow the below steps:
SQL> create tablespace perfstat datafile 'D:\app\oradata\orcl\perfstat.dbf' size 1g autoextend on;
SQL>@?/rdbms/admin/catdbsyn.sql
NAME
catdbsyn.sql - catalog dba synonyms
DESCRIPTION
Creates private synonyms for DBA-only dictionary views.
NOTES
This file is made obsolete as DBA is now a role. All DBA_% catalog
views have a corresponding public synonym, and are accessible to
any user with SELECT ANY TABLE privilege.
SQL>@?/rdbms/admin/dbmspool.sql
NAME
dbmspool.sql - dbms_shared_pool utility package.
DESCRIPTION
This package allows you to display the sizes of objects in the
shared pool, and mark them for keeping or unkeeping in order to
reduce memory fragmentation.
SQL>@?/rdbms/admin/spcreate.sql
SQL> @?/rdbms/admin/spcreate.sql
SQL> Rem
SQL> Rem $Header: spcreate.sql 16-apr-2002.11:22:55 vbarrier Exp $
SQL> Rem
SQL> Rem spcreate.sql
SQL> Rem
SQL> Rem Copyright (c) 1999, 2002, Oracle Corporation. All rights reserved.
SQL> Rem
SQL> Rem NAME
SQL> Rem spcreate.sql - Statistics Create
SQL> Rem
SQL> Rem DESCRIPTION
SQL> Rem SQL*PLUS command file which creates the STATSPACK user,
SQL> Rem tables and package for the performance diagnostic tool STATSPACK
SQL> Rem
SQL> Rem NOTES
SQL> Rem Note the script connects INTERNAL and so must be run from
SQL> Rem an account which is able to connect internal.
SQL> Rem
SQL> Rem MODIFIED (MM/DD/YY)
SQL> Rem cdialeri 02/16/00 - 1191805
SQL> Rem cdialeri 12/06/99 - 1103031
SQL> Rem cdialeri 08/13/99 - Created
SQL> Rem
SQL>
SQL> --
SQL> -- Create PERFSTAT user and required privileges
SQL> @@spcusr
SQL> Rem
SQL> Rem $Header: rdbms/admin/spcusr.sql /st_rdbms_11.2.0/2 2012/03/06 15:07:48 shsong Exp $
SQL> Rem
SQL> Rem spcusr.sql
SQL> Rem
SQL> Rem Copyright (c) 1999, 2012, Oracle and/or its affiliates.
SQL> Rem All rights reserved.
SQL> Rem
SQL> Rem NAME
SQL> Rem spcusr.sql
SQL> Rem
SQL> Rem DESCRIPTION
SQL> Rem SQL*Plus command file to create user which will contain the
SQL> Rem STATSPACK database objects.
SQL> Rem
SQL> Rem NOTES
SQL> Rem Must be run from connected to SYS (or internal)
SQL> Rem
SQL> Rem MODIFIED (MM/DD/YY)
SQL> Rem shsong 01/30/09 - update STATS$V_$FILESTATXS etc
SQL> Rem shsong 07/11/08 - add stats$x$kccfe etc
SQL> Rem cdgreen 03/14/07 - 11 F2
SQL> Rem cdgreen 06/01/06 - 11 F1
SQL> Rem cdgreen 05/10/06 - 5215982
SQL> Rem cdgreen 08/22/05 - 4562627
SQL> Rem cdgreen 05/24/05 - 4246955
SQL> Rem cdgreen 04/18/05 - 4228432
SQL> Rem cdgreen 10/29/04 - 10gR2_sqlstats
SQL> Rem cdgreen 08/12/04 - 10g R2
SQL> Rem vbarrier 02/12/04 - 3412853
SQL> Rem cdialeri 12/04/03 - 3290482
SQL> Rem cdialeri 10/14/03 - 10g - streams - rvenkate
SQL> Rem cdialeri 08/05/03 - 10g F3
SQL> Rem vbarrier 02/25/03 - 10g RAC
SQL> Rem cdialeri 11/15/02 - 10g F1
SQL> Rem vbarrier 09/06/02 - SYSAUX and db default temp tbs
SQL> Rem vbarrier 04/01/02 - 2290728
SQL> Rem vbarrier 03/05/02 - Segment Statistics
SQL> Rem cdialeri 02/07/02 - 2218573
SQL> Rem cdialeri 11/30/01 - 9.2 - features 1
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 02/16/00 - 1191805
SQL> Rem cdialeri 01/26/00 - 1169401
SQL> Rem cdialeri 11/01/99 - 1059172
SQL> Rem cdialeri 08/13/99 - Created
SQL> Rem
SQL>
SQL> set echo off verify off showmode off feedback off;
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
------------------------------ --------- ----------------------------
INDX PERMANENT
PERFSTAT PERMANENT
SYSAUX PERMANENT *
USR 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
------------------------------ --------- --------------------------
TEMPORARY TEMPORARY *
Pressing <return> will result in the database's default Temporary
tablespace (identified by *) being used.
Enter value for temporary_tablespace:
Enter value for temporary_tablespace:
Using tablespace TEMPORARY 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/3 2012/03/06 15:07:48 shsong Exp $
SQL> Rem
SQL> Rem spctab.sql
SQL> Rem
SQL> Rem Copyright (c) 1999, 2012, 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 perfstat 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: rdbms/admin/spcpkg.sql /st_rdbms_11.2.0/2 2012/03/06 15:07:48 shsong Exp $
SQL> Rem
SQL> Rem spcpkg.sql
SQL> Rem
SQL> Rem Copyright (c) 1999, 2012, Oracle and/or its affiliates.
SQL> Rem 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.
TO generate sp report every hour:
SQL> @?/rdbms/admin/spauto.sqlSQL> 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.
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
----------
102
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
---------- --------- --------
102 21-JUN-18 13:00:00
1 row selected.
SQL>
SQL> spool off;
To generate snap id manually.
SQL> exec statspack.snap
No comments:
Post a Comment