oracle statspack学习（一）

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.

TABLESPACE_NAME                CONTENTS  STATSPACK DEFAULT TABLESPACE
------------------------------ --------- ----------------------------
EXAMPLE                        PERMANENT
STATSPACK                      PERMANENT
SYSAUX                         PERMANENT *
USERS                          PERMANENT

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

Enter value for default_tablespace: statspack

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

... Granting privileges
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> !more spcpkg.lis
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> !
至此statspack创建完成
创建完成之后可以看到数据库中多了一个PERFSTAT 用户。

2）创建自动收集系统性能信息的job
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  /
使用spauto.sql脚本进行创建。
注意 此时为每一个小时运行一次，我们可以改成每半个小时运行一次为trunc（sysdate+1/48,'M')，每五分钟运行一次trunc（sysdate+/288,'MI')等等
SQL> conn perfstat/amy
Connected.
SQL> select job,priv_user,what from user_jobs;
SQL> set linesize 200
SQL> r
  1* select job,priv_user,what from user_jobs

       JOB PRIV_USER                      WHAT
---------- ------------------------------ --------------------------------------------------
        23 PERFSTAT                       statspack.snap;
3）手动执行生成快照。
我们可以使用如下方式，手动执行快照收集，而不在等待job运行
SQL> execute statspack.snap;

PL/SQL procedure successfully completed.

SQL> execute statspack.snap;

PL/SQL procedure successfully completed.
4）删除该job
注意：删除oracle的job只能是在该用户下才能删除本用户的job，及时具有dba权限也不能删除其他用户的job。
SQL> conn sys/root as sysdba
Connected.
SQL> select job,priv_user from dba_jobs;

       JOB PRIV_USER
---------- ------------------------------
      4001 APEX_030200
      4002 APEX_030200
         3 SYSMAN
        23 PERFSTAT

4 rows selected.
SQL> execute dbms_job.remove('23');
BEGIN dbms_job.remove('23'); END;

*
ERROR at line 1:
ORA-23421: job number 23 is not a job in the job queue
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 86
ORA-06512: at "SYS.DBMS_IJOB", line 781
ORA-06512: at "SYS.DBMS_JOB", line 180
ORA-06512: at line 1

SQL> conn perfstat/amy
Connected.
SQL> execute dbms_job.remove('23');

PL/SQL procedure successfully completed.

SQL> select job,priv_user from user_jobs;

no rows selected
5）生成快照信息；
我们可以使用spreport.sql来生成快照信息：
在这个过程中其实生成的信息都是在statspack表空间中的各个表的信息收集的到的。
SQL> @?/rdbms/admin/spreport

Instances in this Statspack schema
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

   DB Id    Inst Num DB Name      Instance     Host
----------- -------- ------------ ------------ ------------
 2738540715        1 RHYS         RHYS         oracle-one

Using 2738540715 for database Id
Using          1 for instance number

Specify the number of days of snapshots to choose from
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Entering the number of days (n) will result in the most recent
(n) days of snapshots being listed.  Pressing <return> without
specifying a number lists all completed snapshots.

 

Listing all Completed Snapshots

                                                       Snap
Instance     DB Name        Snap Id   Snap Started    Level Comment
------------ ------------ --------- ----------------- ----- --------------------
RHYS         RHYS                 1 14 Sep 2013 19:52     5
                                  2 14 Sep 2013 19:53     5

 

Specify the Begin and End Snapshot Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Enter value for begin_snap: 1
Begin Snapshot Id specified: 1

Enter value for end_snap: 2
End   Snapshot Id specified: 2

 

Specify the Report Name
~~~~~~~~~~~~~~~~~~~~~~~
The default report file name is sp_1_2.  To use this name,
press <return> to continue, otherwise enter an alternative.

Enter value for report_name:

Using the report name sp_1_2（（（（（指定名字））））

STATSPACK report for

Database    DB Id    Instance     Inst Num  Startup Time   Release     RAC
~~~~~~~~ ----------- ------------ -------- --------------- ----------- ---
          2738540715 RHYS                1 14-Sep-13 18:34 11.2.0.4.0  NO

Host Name             Platform                CPUs Cores Sockets   Memory (G)
~~~~ ---------------- ---------------------- ----- ----- ------- ------------
     oracle-one       Linux x86 64-bit           1     0       0          1.2

Snapshot       Snap Id     Snap Time      Sessions Curs/Sess Comment
~~~~~~~~    ---------- ------------------ -------- --------- ------------------
Begin Snap:          1 14-Sep-13 19:52:48       33       1.3
  End Snap:          2 14-Sep-13 19:53:06       33       1.3
   Elapsed:       0.30 (mins) Av Act Sess:       0.1
   DB time:       0.03 (mins)      DB CPU:       0.02 (mins)

Cache Sizes            Begin        End
~~~~~~~~~~~       ---------- ----------
    Buffer Cache:       128M              Std Block Size:         8K
     Shared Pool:       124M                  Log Buffer:     3,604K

到这里，这个工具你算是会用了，但是要让它真的给dba带来问题的解决途径，那又是另一个深入问题了。那是一门艺术。
6）报告对应的数据表信息：
对于有哪些表，我们一个是可以看statspack创建脚本中的spctab.sql
create table          STATS$DATABASE_INSTANCE
(dbid                 number       not null
,instance_number      number       not null
,startup_time         date         not null
,snap_id              number       not null
,parallel             varchar2(3)  not null
,version              varchar2(17) not null
,db_name              varchar2(9)  not null
,instance_name        varchar2(16) not null
,host_name            varchar2(64)
,platform_name        varchar2(101)
,constraint STATS$DATABASE_INSTANCE_PK primary key
    (dbid, instance_number, startup_time)
 using index tablespace &&tablespace_name
   storage (initial 1m next 1m pctincrease 0)
) tablespace &&tablespace_name
  storage (initial 1m next 1m pctincrease 0) pctfree 5 pctused 40;

create public synonym  STATS$DATABASE_INSTANCE  for STATS$DATABASE_INSTANCE;

/* ------------------------------------------------------------------------- */
"spctab.sql" 2258L, 94461C

这都是内部创建的表，我们也可以直接查看。
7）删除report数据。
oracle 提供了两种方法，一种是delete，另一种是truncate
分别对应脚本sqpurge.sql和sptrunc.sql
[oracle@oracle-one admin]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Sat Sep 14 20:47:57 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/sppurge

Database Instance currently connected to
========================================

                                Instance
   DB Id    DB Name    Inst Num Name
----------- ---------- -------- ----------
 2738540715 RHYS              1 RHYS

Snapshots for this database instance
。。。。。。。。。。。。。。。。。。。

SQL> @?/rdbms/admin/sptrunc

Warning
~~~~~~~
Running sptrunc.sql removes ALL data from Statspack tables.  You may
wish to export the data before continuing.

About to Truncate Statspack Tables
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
If would like to exit WITHOUT truncating the tables, enter any text at the
begin_or_exit prompt (e.g. 'exit'), otherwise if you would like to begin
the truncate operation, press <return>

Enter value for begin_or_exit:
Entered at the 'begin_or_exit' prompt

... Starting truncate operation

Table truncated.

Table truncated.

Table truncated.

Table truncated.

。。。。。。。。。。。。。。。。。

8）导出数据
statspack中的数据是存在于表中的，有时候需要对这些数据发送给support，那么statspack提供了导出数据的脚本为： spuexp.par
file=spuexp.dmp log=spuexp.log compress=y grants=y indexes=y rows=y constraints=y owner=PERFSTAT consistent=y
可以使用exp导出数据
eg：
[oracle@oracle-one admin]$ exp perfstat/amy parfile=./spuexp.par

Export: Release 11.2.0.4.0 - Production on Sat Sep 14 20:55:28 2013

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  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
Export done in US7ASCII character set and AL16UTF16 NCHAR character set
server uses WE8MSWIN1252 character set (possible charset conversion)
. exporting pre-schema procedural objects and actions
. exporting foreign function library names for user PERFSTAT
. exporting PUBLIC type synonyms
. exporting private type synonyms
. exporting object type definitions for user PERFSTAT
About to export PERFSTAT's objects ...
. exporting database links
. exporting sequence numbers
. exporting cluster definitions
. about to export PERFSTAT's tables via Conventional Path ...
. . exporting table          STATS$BUFFERED_QUEUES          0 rows exported
. . exporting table     STATS$BUFFERED_SUBSCRIBERS          0 rows exported
. . exporting table   STATS$BUFFER_POOL_STATISTICS          1 rows exported
. . exporting table          STATS$CR_BLOCK_SERVER          0 rows exported
. . exporting table     STATS$CURRENT_BLOCK_SERVER          0 rows exported
. . exporting table        STATS$DATABASE_INSTANCE          1 rows exported
. . exporting table          STATS$DB_CACHE_ADVICE         21 rows exported
. . exporting table                 STATS$DLM_MISC          0 rows exported
. . exporting table   STATS$DYNAMIC_REMASTER_STATS          0 rows exported
. . exporting table       STATS$ENQUEUE_STATISTICS         75 rows exported
. . exporting table          STATS$EVENT_HISTOGRAM        537 rows exported
. . exporting table               STATS$FILESTATXS          6 rows exported
. . exporting table           STATS$FILE_HISTOGRAM         35 rows exported
. . exporting table               STATS$IDLE_EVENT        135 rows exported
. . exporting table  STATS$INSTANCE_CACHE_TRANSFER          0 rows exported
. . exporting table        STATS$INSTANCE_RECOVERY          1 rows exported
. . exporting table       STATS$INTERCONNECT_PINGS          0 rows exported
. . exporting table          STATS$IOSTAT_FUNCTION         14 rows exported
. . exporting table     STATS$IOSTAT_FUNCTION_NAME         14 rows exported
. . exporting table         STATS$JAVA_POOL_ADVICE         10 rows exported
. . exporting table                    STATS$LATCH        582 rows exported
. . exporting table           STATS$LATCH_CHILDREN          0 rows exported
. . exporting table     STATS$LATCH_MISSES_SUMMARY         19 rows exported
. . exporting table             STATS$LATCH_PARENT          0 rows exported
. . exporting table        STATS$LEVEL_DESCRIPTION          5 rows exported
. . exporting table             STATS$LIBRARYCACHE         20 rows exported
. . exporting table     STATS$MEMORY_DYNAMIC_COMPS         16 rows exported
. . exporting table        STATS$MEMORY_RESIZE_OPS         56 rows exported
. . exporting table     STATS$MEMORY_TARGET_ADVICE          6 rows exported
. . exporting table              STATS$MUTEX_SLEEP          4 rows exported
. . exporting table                   STATS$OSSTAT         20 rows exported
. . exporting table               STATS$OSSTATNAME         20 rows exported
. . exporting table                STATS$PARAMETER        362 rows exported
. . exporting table                  STATS$PGASTAT         14 rows exported
. . exporting table        STATS$PGA_TARGET_ADVICE         14 rows exported
. . exporting table    STATS$PROCESS_MEMORY_ROLLUP         67 rows exported
. . exporting table           STATS$PROCESS_ROLLUP         21 rows exported
. . exporting table     STATS$PROPAGATION_RECEIVER          0 rows exported
. . exporting table       STATS$PROPAGATION_SENDER          0 rows exported
. . exporting table           STATS$RESOURCE_LIMIT          4 rows exported
. . exporting table                 STATS$ROLLSTAT         11 rows exported
. . exporting table         STATS$ROWCACHE_SUMMARY         57 rows exported
. . exporting table                 STATS$RULE_SET          1 rows exported
. . exporting table                 STATS$SEG_STAT          0 rows exported
. . exporting table             STATS$SEG_STAT_OBJ          0 rows exported
. . exporting table            STATS$SESSION_EVENT          0 rows exported
. . exporting table                  STATS$SESSTAT          0 rows exported
. . exporting table          STATS$SESS_TIME_MODEL          0 rows exported
. . exporting table                      STATS$SGA          4 rows exported
. . exporting table                  STATS$SGASTAT         27 rows exported
. . exporting table        STATS$SGA_TARGET_ADVICE          6 rows exported
. . exporting table       STATS$SHARED_POOL_ADVICE         21 rows exported
. . exporting table                 STATS$SNAPSHOT          1 rows exported
. . exporting table                  STATS$SQLTEXT        399 rows exported
. . exporting table                 STATS$SQL_PLAN          0 rows exported
. . exporting table           STATS$SQL_PLAN_USAGE          0 rows exported
EXP-00091: Exporting questionable statistics.
. . exporting table           STATS$SQL_STATISTICS          1 rows exported
. . exporting table              STATS$SQL_SUMMARY         71 rows exported
. . exporting table   STATS$SQL_WORKAREA_HISTOGRAM          8 rows exported
. . exporting table      STATS$STATSPACK_PARAMETER          1 rows exported
. . exporting table        STATS$STREAMS_APPLY_SUM          0 rows exported
. . exporting table          STATS$STREAMS_CAPTURE          0 rows exported
. . exporting table      STATS$STREAMS_POOL_ADVICE          0 rows exported
. . exporting table                  STATS$SYSSTAT        679 rows exported
. . exporting table             STATS$SYSTEM_EVENT         71 rows exported
. . exporting table           STATS$SYS_TIME_MODEL         19 rows exported
. . exporting table               STATS$TEMPSTATXS          1 rows exported
. . exporting table            STATS$TEMP_SQLSTATS
. . exporting table                   STATS$THREAD          1 rows exported
. . exporting table      STATS$TIME_MODEL_STATNAME         19 rows exported
. . exporting table                 STATS$UNDOSTAT         14 rows exported
. . exporting table                 STATS$WAITSTAT         18 rows exported
. exporting synonyms
. exporting views
. exporting stored procedures
. exporting operators
. exporting referential integrity constraints
. exporting triggers
. exporting indextypes
. exporting bitmap, functional and extensible indexes
. exporting posttables actions
. exporting materialized views
. exporting snapshot logs
. exporting job queues
. exporting refresh groups and children
. exporting dimensions
. exporting post-schema procedural objects and actions
. exporting statistics
Export terminated successfully with warnings.
[oracle@oracle-one admin]$
从这里，可以看到了很多关于report的表
9）对单独sql hast值进行生成sql报告
statspack提供了sprepsql.sql脚本
10）删除statspack
提供了一个删除脚本为：

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

Synonym dropped.

Sequence dropped.

Synonym dropped.

Table dropped.

Synonym dropped.

Table dropped.

Synonym dropped.

。。。。。。。。。。。。。。。