Statspack Introduction

Oracle Statspack 从 Oracle8.1.6 开始被引入 Oracle,并马上成为 DBA 和 Oracle 专家用来诊断数据库性能的强有力的工具。通过 Statspack 我们可以很容易的确定 Oracle 数据库的瓶颈所在，记录数据库性能状态，也可以使远程技术支持人员迅速了解你的数据库运行状况。因此了解和使用 Statspack 对于 DBA 来说至关重要。

Oracle 10g之前对数据库做性能检测使用statspack工具，自10g 提供了一个新的工具：(AWR:Automatic Workload Repository)。Oracle 建议用户用这个取代 Statspack。AWR 实质上是一个 Oracle 的内置工具，它采集与性能相关的统计数据，并从那些统计数据中导出性能量度，以跟踪潜在的问题。

在数据库中 Statspack 的脚本位于$ORACLE_HOME/RDBMS/ADMIN 目录下。

基本使用

1.安装statspack.

在$ORACLE_HOME/rdbms/admin/目录下运行：

SQL> @spcreate.sql

若创建失败则在同一目录下运行：

SQL> @spdrop.sql

2.测试

SQL>execute statspack.snap
  PL/SQL procedure successfully completed.
SQL>execute statspack.snap
  PL/SQL procedure successfully completed.
SQL>@spreport.sql
 
SQL>exec statspack.snap; 

进行信息收集统计，每次运行都将产生一个快照号，获得快照号，必须要有两个以上的快照，才能生成报表

3.查选快照信息

SQL>select SNAP_ID, SNAP_TIME from STATS$SNAPSHOT;

4.获取statspack 报告

SQL>@spreport.sql

按照提示，输入需要查看的开始快照号与结束快照号即可。

5.其他相关脚本

spauto.sql：利用dbms_job提交一个作业，自动的进行STATPACK的信息收集统计
sppurge.sql ：清除一段范围内的统计信息，需要提供开始快照与结束快照号
sptrunc.sql ：清除(truncate)所有统计信息

6.查看Statspack 生成源代码

在oracle 9i里面，我们可以通过查看statspack 生成脚本来帮助我们理解report，但是10g的AWR是通过dbms_workload_repository包来实现AWR的。包把代码都封装了起来，我们无法查看。

statspack的生成脚本位置：$ORACLE_HOME/rdbms/admin/sprepins.sql 代码很长，不过看懂了，能帮助我们理解statspack中各个数据的意义。

检查系统参数

为了能够顺利安装和运行 Statspack 你可能需要设置以下系统参数：

1.`job_queue_processes`

为了能够建立自动任务，执行数据收集，该参数需要大于 0。你可以在初试化参数文件中修改该参数(使该参数在重起后以然有效)。

该参数可以在系统级动态修改。

SQL> show parameter job_queue_processes;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
job_queue_processes                  integer     2
SQL> alter system set job_queue_processes=6;

System altered.

SQL> show parameter job_queue_processes;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
job_queue_processes                  integer     6

在 Oracle9i 当中，可以指定范围，如 both,这样该修改在当前及之后保持有效(仅当你使用 spfile时，如果在 9i 中仍然使用 pfile，那么更改方法同 8i 相同):

SQL> alter system set job_queue_processes = 6 scope=both;   
System altered.

2.`timed_statistics`

收集操作系统的计时信息，这些信息可被用来显示时间等统计信息、优化数据库和 SQL 语句。要防止因从操作系统请求时间而引起的开销，请将该值设置为 False。

使用 statspack 收集统计信息时建议将该值设置为 True，否则收集的统计信息大约只能起到10%的作用，将 timed_statistics 设置为 True 所带来的性能影响与好处相比是微不足道的。

该参数使收集的时间信息存储在在 V$SESSTATS 和 V$SYSSTATS 等动态性能视图中。

timed_statistics 参数可以在实例级进行更改

SQL> alter system set timed_statistics = true; 
System altered 
SQL> show parameter timed_statistics;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
timed_statistics                     boolean     TRUE

如果你担心一致启用 timed_statistics 对于性能的影响，你可以在使用 statspack 之前在 system 更改，采样过后把该参数动态修改成 false。

检查Statspack

因测试环境中之前做过statspack监控，并未删除statspack对象，因此这里仅确认下是否安装正确。要看安装、卸载等，请查阅参考文档。

1.statspack检查

statspack脚本均在$ORACLE_HOME/rdbms/admin下。首先切换到该目录，下面执行脚本时会比较方便。

切换到该路径

$ pwd
/u2/TEST/testora/testdb/9.2.0/rdbms/admin

sp脚本

$ ls sp*
sp__.lst          spcpkg.sql        spctab.sql        spdoc.txt         spdusr.sql        spreport.sql      sprepsql.sql      spup816.sql
spauto.sql        spcreate.sql      spcusr.lis        spdrop.sql        sppurge.sql       spreport0902.txt  sptrunc.sql       spup817.sql
spcpkg.lis        spctab.lis        spcusr.sql        spdtab.sql        sprepins.sql      spreport0907.txt  spuexp.par        spup90.sql

2.测试sp脚本

SQL> execute statspack.snap

PL/SQL procedure successfully completed.

SQL> execute statspack.snap

PL/SQL procedure successfully completed.
SQL> @spreport.sql
……	

3.检查statspach表空间

SQL> SELECT tablespace_name,file_name, round(dbf.BYTES / (1024 * 1024),0) "Total_space(M)" FROM dba_data_files dbf where dbf.TABLESPACE_NAME = 'STATSPACK';  
		TABLESPACE_NAME   FILE_NAME                     Total_space(M)
-------------------- ------------------------------------------------------------ --------------
STATSPACK  /u2/TEST/testora/testdata/statspack_01.dbf               500.00

规划自动任务

Statspack 正确安装以后，我们就可以设置定时任务，开始收集数据了。可以使用 spatuo.sql 来定义自动任务。

先来看看 spauto.sql 的关键内容：

variable jobno number;
variable instno number;
begin
  select instance_number into :instno from v$instance;
  dbms_job.submit(:jobno, 'statspack.snap;',
  trunc(sysdate+1/24,'HH'), 'trunc(SYSDATE+1/24,'HH')', 
TRUE, :instno);
  commit;
end;

这个 job 任务定义了收集数据的时间间隔：

一天有 24 个小时，1440 分钟，那么：
	1/24   HH           每小时一次
	1/48   MI           每半小时一次
	1/144  MI           每十分钟一次
	1/288  MI           每五分钟一次

SQL> @spauto

PL/SQL procedure successfully completed.


Job number for automated statistics collection for this instance
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Note that this job number is needed when modifying or removing
the job:

     JOBNO
----------
     87890


Job queue process
~~~~~~~~~~~~~~~~~
Below is the current setting of the job_queue_processes init.ora
parameter - the value for this parameter must be greater
than 0 to use automatic statistics gathering:

NAME_COL_PLUS_SHOW_PARAM                                         TYPE
---------------------------------------------------------------- -----------
VALUE_COL_PLUS_SHOW_PARAM
------------------------------------------------------------------------------
job_queue_processes                                              integer
6


Next scheduled run
~~~~~~~~~~~~~~~~~~
The next scheduled run for this job is:

       JOB NEXT_DATE       NEXT_SEC
---------- --------------- ------------------------
     87890 28-JUN-12       10:00:00

关于采样间隔，我们通常建议以 1 小时为时间间隔，对于有特殊需要的环境，可以设置更短的，如半小时作为采样间隔，但是不推荐更短。因为 statspack 的执行本身需要消耗资源，对于繁忙的生产系统，太短的采样对系统的性能会产生较大的影响（甚至会使 statspack 的执行出现在采样数据中）。

生成分析报告

运行spreport脚本，输入起始和结束的快照ID，生成分析报告。

SQL> @spreport
Current Instance
~~~~~~~~~~~~~~~~
   DB Id    DB Name      Inst Num Instance
---------- ------------ -------- ------------
   33115540 ERP                 1 ERP 
Instances in this Statspack schema
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
   DB Id    Inst Num DB Name      Instance     Host
----------- -------- ------------ ------------ ------------
   33115540        1 ERP          ERP          erp
   56114082        1 PROD         PROD         erpprod

Using   33115540 for database Id
Using          1 for instance number
Completed Snapshots
                               Snap                    Snap
Instance     DB Name             Id   Snap Started    Level Comment
------------ ------------ --------- ----------------- ----- --------------------
ERP          ERP                753 28 Jun 2012 10:30     5
                                754 28 Jun 2012 11:30     5
                                755 28 Jun 2012 12:30     5
                                756 28 Jun 2012 13:30     5
                                757 28 Jun 2012 14:30     5
                                758 28 Jun 2012 15:30     5 
Specify the Begin and End Snapshot Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Enter value for begin_snap: 753
Begin Snapshot Id specified: 753
Enter value for end_snap: 758
End   Snapshot Id specified: 758
Specify the Report Name
~~~~~~~~~~~~~~~~~~~~~~~
The default report file name is sp_753_758.  To use this name,
press <return> to continue, otherwise enter an alternative.
Enter value for report_name: sp_753_758.txt
Using the report name sp_753_758.txt SNATSPACK report for DB Name         DB Id    Instance     Inst Num Release     Cluster Host
----------- ----------- ------------ -------- ----------- ------- ------------
ERP             33115540 ERP                 1 9.2.0.6.0   NO      erp
              Snap Id     Snap Time      Sessions Curs/Sess Comment
            --------- ------------------ -------- --------- -------------------
Begin Snap:       753 28-Jun-12 10:30:16      153      91.9
  End Snap:       758 28-Jun-12 15:30:17      160      90.5
   Elapsed:              300.02 (mins) ……

一个 statspack 的报告不能跨越一次停机，但是之前或之后的连续区间，收集的信息依然有效。你可以选择之前或之后的采样声称 report。

移除定时任务

运行dbms_job.remove(‘job_id’)，移除定时任务。

SQL> select job,log_user,priv_user,last_date,next_date,interval from user_jobs;
    JOB LOG_USER  PRIV_USER LAST_DATE NEXT_DATE INTERVAL
------------ --------------- -------------- ----------------  -------------- --------------------------
87890 	SYS   SYS 	    28-JUN-12 28-JUN-12  trunc(SYSDATE+1/24,'HH') 

SQL> execute dbms_job.remove('87890');
PL/SQL procedure successfully completed.

删除历史数据

删除stats$snapshot数据表中的数据，其他表中的数据会相应的级联删除

SQL> select max(snap_id) from stats$snapshot;
MAX(SNAP_ID)
------------
        758
SQL> delete from stats$snapshot where snap_id < = 758;

如果采样了大量的数据，直接delete是非常会慢的，可以考虑使用sptrunc脚本，清空stats历史数据。

SQL> @sptrunc.sql 
Warning
~~~~~~~
Running sptrunc.sql removes ALL data from Statspack tables.  You may
wish to export the data before continuing. 
About to Truncate Statspack Tables ……

参考

statspack使用指南

blog comments powered by Disqus

Published

14 March 2013

Github

Rendered

Raw

Statspack Introduction

基本使用

1.安装statspack.

2.测试

3.查选快照信息

4.获取statspack 报告

5.其他相关脚本

6.查看Statspack 生成源代码

检查系统参数

1.`job_queue_processes`

2.`timed_statistics`

检查Statspack

1.statspack检查

2.测试sp脚本

3.检查statspach表空间

规划自动任务

生成分析报告

移除定时任务

删除历史数据

延伸阅读

参考

Published

Categories

Tags

Github

Statspack Introduction

基本使用

1.安装statspack.

2.测试

3.查选快照信息

4.获取statspack 报告

5.其他相关脚本

6.查看Statspack 生成源代码

检查系统参数

1.job_queue_processes

2.timed_statistics

检查Statspack

1.statspack检查

2.测试sp脚本

3.检查statspach表空间

规划自动任务

生成分析报告

移除定时任务

删除历史数据

延伸阅读

参考

Published

Categories

Tags

Github

1.`job_queue_processes`

2.`timed_statistics`