oracle statspack学习（一）
虽然 oracle statspac是在oracle 8 中出现的，statspack是一个强有力的oracle性能分析收集信息工具，指导现在依然有研究价值。
在创建statspace之前需要了解两个参数，如下：
JOB_QUEUE_PROCESSES:
JOB_QUEUE_PROCESSES specifies the maximum number of job slaves per instance that can be created for the execution of DBMS_JOB jobs and Oracle Scheduler (DBMS_SCHEDULER) jobs. DBMS_JOB and Oracle Scheduler share the same job coordinator and job slaves, and they are both controlled by the JOB_QUEUE_PROCESSES parameter.
If the value of JOB_QUEUE_PROCESSES is set to 0, then DBMS_JOB jobs and Oracle Scheduler jobs will not run on the instance.
timed_statistics
•true
The statistics are collected and stored in trace files or displayed in the V$SESSTATS and V$SYSSTATS dynamic performance views.
•false
The value of all time-related statistics is set to zero. This setting lets Oracle avoid the overhead of requesting the time from the operating system.
Starting with release 11.1.0.7.0, the value of the TIMED_STATISTICS parameter cannot be set to false if the value of STATISTICS_LEVEL is set to TYPICAL or ALL
1）创建statspack
创建步骤，
1、创建单独的表空间
2、运行创建脚本spcreate.sql
如下：
SQL> select file_name from dba_data_files;
FILE_NAME
--------------------------------------------------------------------------------
/opt/app/oracle/oradata/RHYS/users01.dbf
/opt/app/oracle/oradata/RHYS/undotbs01.dbf
/opt/app/oracle/oradata/RHYS/sysaux01.dbf
/opt/app/oracle/oradata/RHYS/system01.dbf
/opt/app/oracle/oradata/RHYS/example01.dbf
SQL> create tablespace statspack datafile '/opt/app/oracle/oradata/RHYS/statspack.dbf' size 500M autoextend off;
Tablespace created.
[oracle@oracle-one admin]$ ls -ltr sp*
-rw-r--r--. 1 oracle oinstall 588 Mar 15 2000 spuexp.par
-rw-r--r--. 1 oracle oinstall 1771 Mar 15 2000 spauto.sql
-rw-r--r--. 1 oracle oinstall 758 Jun 19 2000 spdrop.sql
-rw-r--r--. 1 oracle oinstall 1284 Apr 23 2001 spreport.sql
-rw-r--r--. 1 oracle oinstall 861 May 17 2002 spcreate.sql
-rw-r--r--. 1 oracle oinstall 1268 Oct 11 2002 sprepsql.sql
-rw-r--r--. 1 oracle oinstall 5193 Mar 31 2005 sprepcon.sql
-rw-r--r--. 1 oracle oinstall 23049 May 31 2005 spup10.sql
-rw-r--r--. 1 oracle oinstall 4924 Apr 10 2006 sppurge.sql
-rw-r--r--. 1 oracle oinstall 33823 Jun 27 2006 sprsqins.sql
-rw-r--r--. 1 oracle oinstall 20341 Jun 28 2007 spup102.sql
-rw-r--r--. 1 oracle oinstall 5071 Jun 28 2007 sptrunc.sql
-rw-r--r--. 1 oracle oinstall 228808 Oct 13 2008 spawrrac.sql
-rw-r--r--. 1 oracle oinstall 1825 Feb 11 2009 spdusr.sql
-rw-r--r--. 1 oracle oinstall 8374 May 14 2009 spup1101.sql
-rw-r--r--. 1 oracle oinstall 85202 Feb 23 2010 spawrio.sql
-rw-r--r--. 1 oracle oinstall 19808 Apr 20 2010 spup90.sql
-rw-r--r--. 1 oracle oinstall 24011 Apr 20 2010 spup817.sql
-rw-r--r--. 1 oracle oinstall 31334 Apr 20 2010 spup816.sql
-rw-r--r--. 1 oracle oinstall 4954 Aug 3 2010 spadvrpt.sql
-rw-r--r--. 1 oracle oinstall 4172 Aug 13 2010 spup11201.sql
-rw-r--r--. 1 oracle oinstall 158146 Aug 13 2010 spdoc.txt
-rw-r--r--. 1 oracle oinstall 42039 Aug 13 2010 spup92.sql
-rw-r--r--. 1 oracle oinstall 284125 Mar 6 2012 sprepins.sql
-rw-r--r--. 1 oracle oinstall 8229 Mar 6 2012 spdtab.sql
-rw-r--r--. 1 oracle oinstall 16396 Mar 6 2012 spcusr.sql
-rw-r--r--. 1 oracle oinstall 94461 Mar 6 2012 spctab.sql
-rw-r--r--. 1 oracle oinstall 212780 Mar 6 2012 spcpkg.sql
[oracle@oracle-one admin]$ more spcreate.sql
Rem
Rem $Header: spcreate.sql 16-apr-2002.11:22:55 vbarrier Exp $
Rem
Rem spcreate.sql
Rem
Rem Copyright (c) 1999, 2002, Oracle Corporation. All rights reserved.
Rem
Rem NAME
Rem spcreate.sql - Statistics Create
Rem
Rem DESCRIPTION
Rem SQL*PLUS command file which creates the STATSPACK user,
Rem tables and package for the performance diagnostic tool STATSPACK
Rem
Rem NOTES
Rem Note the script connects INTERNAL and so must be run from
Rem an account which is able to connect internal.
Rem
Rem MODIFIED (MM/DD/YY)
Rem cdialeri 02/16/00 - 1191805
Rem cdialeri 12/06/99 - 1103031
Rem cdialeri 08/13/99 - Created
Rem
--
-- Create PERFSTAT user and required privileges
@@spcusr
--
-- Build the tables and synonyms
connect perfstat/&&perfstat_password
@@spctab
-- Create the statistics Package
@@spcpkg
可以看到在创建statspack的时候是引用了两个脚本，一个为创建对象的spctab.sql另一个是创建包的spcpkg.sql
[oracle@oracle-one admin]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Sat Sep 14 19:38:34 2013
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> @?/rdbms/admin/spcreate
Choose the PERFSTAT user's password
-----------------------------------
Not specifying a password will result in the installation FAILING((((((((这是创建用户perfstat，并指定密码为：amy））））
Enter value for perfstat_password: amy
amy
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
