ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 使用Statspack

使用Statspack

原创 Linux操作系统 作者:landf 时间:2011-03-15 16:30:56 0 删除 编辑
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
--------------------------------------------------------------------------------
| Operation                      | PHV/Object Name     |  Rows | Bytes|   Cost |
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------

End of Report

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/14359/viewspace-689524/,如需转载,请注明出处,否则将追究法律责任。

上一篇: 创建Statspack
请登录后发表评论 登录
全部评论

注册时间:2008-02-14

  • 博文量
    68
  • 访问量
    498339