1，收集统计数据，可以以手动方式做快照：

SQL> connect perfstat/perfstat

    SQL> variable snap number;
    SQL> begin
         2 :snap:=statspack.snap;
          3 end;
         4 /

PL/SQL 过程已成功完成。

SQL> print snap;

SNAP
----------
1

或者设置时间间隔，由Oracle数据库自动做快照，例如运行如下spauto.sql脚本后，统计数据每隔60分钟被收集一次：

SQL> @spauto
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'), 'tr
nc(SYSDATE+1/24,''HH'')', TRUE, :instno);
4    commit;
5 end;
6 /

PL/SQL 过程已成功完成。

SQL>
SQL>
SQL> prompt

SQL> prompt Job number for automated statistics collection for this instanc

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
----------
41

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.or

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 10
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
---------- -------------- ----------------
41 15-3月 -11 17:00:00

已选择 1 行。

SQL>
SQL> spool off;
SQL>

2,快照采集后，可以生成性能报告-有两种可用的报告：实例报告、SQL报告，一般选择快照时间间隔为15分钟。

spreport.sql调用sprepins.sql,首先默认是你连接实例的DBID和实例编号。关于sprepins和spreport的区别查看文档spdoc.txt中的'Running the instance report when there are multiple instances' 部分；可以在sprepins.sql文件中加入

define num_days=n只列出n天内采集的快照

SQL报告(sprepsql.sql and sprsqins.sql)是关于指定SQL语句的报告。通常针对实例检查报告中的高负载SQL语句运行SQL报告。SQL报告提供了一个SQL语句（通过Hash值）的详细统计数据。
sprepsql.sql调用sprsqins.sql,首先默认是你连接实例的DBID和实例编号。关于sprepsql和sprsqins的区别查看文档spdoc.txt中的'Running the SQL report when there are multiple instances' 部分

a，生成实例报告：

SQL> @%ORACLE_HOME%\rdbms\admin\spreport

Current Instance
~~~~~~~~~~~~~~~~

DB Id DB Name Inst Num Instance
----------- ------------ -------- ------------
1259990420 ORCL 1 orcl

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

DB Id Inst Num DB Name Instance Host
----------- -------- ------------ ------------ ------------
1259990420 1 ORCL orcl HANHUBO

Using 1259990420 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 without
specifying a number lists all completed snapshots.

Listing all Completed Snapshots

Snap
Instance DB Name Snap Id Snap Started Level Comment
------------ ------------ --------- ----------------- ----- -------

orcl         ORCL                 1 15 3月 2011 16:5     5
                                    3
                                  2 15 3月 2011 17:0     5
                                    0
                                  3 15 3月 2011 17:0     5
                                    2
                                  4 15 3月 2011 17:0     5
                                    6
                                  5 15 3月 2011 17:1     5
                                    4
                                  6 15 3月 2011 17:3     5
                                    0
                                  7 15 3月 2011 18:0     5
                                    0

                                 11 16 3月 2011 08:5     5
                                    8
                                 12 16 3月 2011 09:0     5
                                    0
                                 13 16 3月 2011 10:0     5
                                    0
                                 14 16 3月 2011 11:0     5
                                    0
                                 15 16 3月 2011 12:0     5
                                    0
                                 16 16 3月 2011 13:2     5
                                    0
                                 17 16 3月 2011 14:0     5
                                    0
                                 18 16 3月 2011 15:0     5
                                    0
                                 21 16 3月 2011 16:0     5
                                    0
                                 22 16 3月 2011 17:0     5
                                    0

31 17 3月 2011 13:4 5
3

Specify the Begin and End Snapshot Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

...

生成report文件

b，生成Sql报告：

SQL> @%ORACLE_HOME%\rdbms\admin\sprepsql

Current Instance
~~~~~~~~~~~~~~~~

DB Id DB Name Inst Num Instance
----------- ------------ -------- ------------
1259990420 ORCL 1 orcl

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

DB Id Inst Num DB Name Instance Host
----------- -------- ------------ ------------ ------------
1259990420 1 ORCL orcl HANHUBO

Using 1259990420 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 without
specifying a number lists all completed snapshots.

Listing all Completed Snapshots

Snap Snap
Instance DB Name Id Snap Started Level Comment
------------ ------------ --------- ----------------- ----- --------------------

orcl         ORCL                 1 15 3月 2011 16:5     5
                                    3
                                  2 15 3月 2011 17:0     5
                                    0
                                  3 15 3月 2011 17:0     5
                                    2
                                  4 15 3月 2011 17:0     5
                                    6
                                  5 15 3月 2011 17:1     5
                                    4
                                  6 15 3月 2011 17:3     5
                                    0
                                  7 15 3月 2011 18:0     5
                                    0

                                 11 16 3月 2011 08:5     5
                                    8
                                 12 16 3月 2011 09:0     5
                                    0
                                 13 16 3月 2011 10:0     5
                                    0
                                 14 16 3月 2011 11:0     5
                                    0
                                 15 16 3月 2011 12:0     5
                                    0
                                 16 16 3月 2011 13:2     5
                                    0
                                 17 16 3月 2011 14:0     5
                                    0
                                 18 16 3月 2011 15:0     5
                                    0
                                 21 16 3月 2011 16:0     5
                                    0
                                 22 16 3月 2011 17:0     5
                                    0

                                 31 17 3月 2011 13:4     5
                                    3
                                 32 17 3月 2011 13:5     5
                                    4

Specify the Begin and End Snapshot Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
输入 begin_snap 的值: 31
Begin Snapshot Id specified: 31

输入 end_snap 的值: 32
End Snapshot Id specified: 32

Specify the old (i.e. pre-10g) Hash Value
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
输入 hash_value 的值: 3302967177
Hash Value specified is: 3302967177

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

输入 report_name 的值:

Using the report name sp_31_32_3302967177

STATSPACK SQL report for Old Hash Value: 3302967177 Module: SQL*Plus

DB Name DB Id Instance Inst Num Release RAC Host
------------ ----------- ------------ -------- ----------- --- ----------------
ORCL 1259990420 orcl 1 10.2.0.1.0 NO HANHUBO

Start Id Start Time End Id End Time Duration(mins)
--------- ------------------- --------- ------------------- --------------
31 17-3月 -11 13:43:52 32 17-3月 -11 13:54:46 10.90

SQL Statistics
~~~~~~~~~~~~~~
-> CPU and Elapsed Time are in seconds (s) for Statement Total and in
   milliseconds (ms) for Per Execute
                                                       % Snap
                     Statement Total      Per Execute   Total
                     --------------- --------------- ------
        Buffer Gets:           4,043          4,043.0   37.94
         Disk Reads:             161            161.0   43.63
     Rows processed:               1              1.0
     CPU Time(s/ms):               1            530.4
Elapsed Time(s/ms):               3          2,945.3
              Sorts:               0               .0
        Parse Calls:               0               .0
      Invalidations:               0
      Version count:               1
    Sharable Mem(K):              16
         Executions:               1

SQL Text
~~~~~~~~
begin :snap:=statspack.snap; end;

Plans in shared pool between Begin and End Snap Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Shows the Execution Plans found in the shared pool between the begin and end
snapshots specified. The values for Rows, Bytes and Cost shown below are those
which existed at the time the first-ever snapshot captured this plan - these
values often change over time, and so may not be indicative of current values
-> Rows indicates Cardinality, PHV is Plan Hash Value
-> ordered by Plan Hash Value

--------------------------------------------------------------------------------

--------------------------------------------------------------------------------

End of Report

ITPub博客

使用Statspack

最新文章