转载:Statspack的安装使用说明

Statspack安装配置使用说明

在数据库管理中，Oracle提供的statspack是一个很强大的工具，通过Statspack，可以收集系统信息，诊断数据库故障，也方便第三方技术支持进行远程阅读和建议。
看到很多人还不太了解这个工具的使用，论坛上同样的问题很多，所以写了一下一个说明文档，主要涉及安装配置和数据收集。

关于参数说明和性能部分还在继续进行中。

先帖一部分写完的出来，希望通过这些文字可以使不了解的朋友学会怎样配置和使用Statspack!

下面是正文：

Oracle Statspack 从Oracle8.1.6开始被引入Oracle,并马上成为DBA和Oracle专家用来诊断数据库性能的强有力的工具。通过Statspack我们可以很容易的确定Oracle数据库的瓶颈所

在，记录数据库性能状态。因此了解和使用Statspack对于DBA来说至关重要。
在数据库中Statspack的脚本位于$ORACLE_HOME/RDBMS/ADMIN目录下，对于ORACLE8.1.6,是一组以stat开头的文件；对于ORACLE8.1.7,是一组以sp开头的文件。
在Oracle8.1.6中，Statspack第一次发布，但是你也可以在以下链接找到可用于Oracle80~Oracle8.1.5的版本。

http://www.oracle.com/oramag/oracle...tml?o20tun.html
http://www.oracle.com/oramag/oracle...pack-other.html

在816以前的版本使用Statspack，你需要使用statscbps.sql脚本建立一个v$buffer_pool_statistics视图，该脚本包含在以上链接下载的tar文件中。
访问该链接，你可能需要一个OTN帐号，申请该帐号是免费的。

在Statspack发布之前，我们通常能够使用诊断数据库的工具是两个脚本UTLBSTAT.SQL和UTLESTAT.SQL，BSTAT/ESTAT是一个非常简单的性能诊断工具。UTLBSTAT获得开始时很多

V$视图的快照，UTLESTAT通过先前的快照和当前视图生成一个报表。实际上通过这个报表我们很难读出或者解释系统性能方面的问题。Statspack改变了这一切，通过连续的数据收集，

Statspack能给我们提供至关重要的趋势分析。这是一个巨大的进步。

下面我们来讲一讲Statspack的安装，配置，使用，解读

一. 系统参数
为了能够顺利安装和运行Statspack你可能需要设置以下系统参数：
1. job_queue_processes
为了能够建立自动任务，执行数据收集，该参数需要大于0。你可以在初试化参数文件中修改该参数。

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>

二. 安装Statspack
安装Statspack需要用internal身份登陆，或者拥有SYSDBA(connect / as sysdba)权限的用户登陆。需要在本地安装或者通过telnet登陆到服务器。
在Oracle8.1.6版本中运行statscre.sql;在Oracle8.1.7版本中运行spcreate.sql。

首先登陆到数据库，最好转到$ORACLE_HOME/RDBMS/ADMIN目录，这样我们执行脚本就可以方便些。

D:>cd oracle\ora81\rdbms\admin

D:\oracle\ora81\RDBMS\ADMIN>sqlplus internal

SQL*Plus: Release 8.1.7.0.0 - Production on 星期二 12月 3 16:54:53 2002

请输入口令:

连接到:
Oracle8i Enterprise Edition Release 8.1.7.0.0 - Production
With the Partitioning option
JServer Release 8.1.7.0.0 - Production

SQL> select instance_name,host_name,version,startup_time from v$instance;

INSTANCE_NAME
----------------
HOST_NAME
----------------------------------------------------------------
VERSION STARTUP_TI
----------------- ----------
eygle
AM-SERVER
8.1.7.0.0 22-11月-02

SQL>

检查数据文件路径及磁盘空间，以决定创建数据文件的位置：

SQL> select file_name from dba_data_files;

FILE_NAME
--------------------------------------------------------------------------------
D:\ORACLE\ORADATA\EYGLE\SYSTEM01.DBF
D:\ORACLE\ORADATA\EYGLE\RBS01.DBF
D:\ORACLE\ORADATA\EYGLE\USERS01.DBF
D:\ORACLE\ORADATA\EYGLE\TEMP01.DBF
D:\ORACLE\ORADATA\EYGLE\TOOLS01.DBF
D:\ORACLE\ORADATA\EYGLE\INDX01.DBF
D:\ORACLE\ORADATA\EYGLE\DR01.DBF
D:\ORACLE\ORADATA\EYGLE\GGQIANG01.DBF
D:\ORACLE\ORADATA\EYGLE\EQSP01.DBF
D:\ORACLE\ORA81\DATABASE\MP5_DATA.DBF
D:\ORACLE\ORA81\DATABASE\MP5_INDX.DBF
D:\ORACLE\ORA81\DATABASE\MP5_TEMP.DBF
D:\ORACLE\ORA81\DATABASE\MP5_IDNX.DBF
D:\ORACLE\ORADATA\EYGLE\HRM01.DBF
D:\ORACLE\ORADATA\EYGLE\TIB.DBF
D:\ORACLE\ORADATA\EYGLE\EQSP02.DBF
D:\ORACLE\ORADATA\EYGLE\RMAN_TS.DBF
D:\ORACLE\ORADATA\EYGLE\EQSP03.DBF
D:\ORACLE\ORADATA\EYGLE\EAPP01.DBF
D:\ORACLE\ORADATA\EYGLE\EQSP04.DBF
D:\ORACLE\ORADATA\EYGLE\AM01.DBF
D:\ORACLE\ORADATA\EYGLE\SYSTEM02.DBF
D:\ORACLE\ORADATA\EYGLE\FNC01.DBF
D:\ORACLE\ORADATA\EYGLE\HH_AM01.ORA

已选择24行。

SQL>

创建存储数据的表空间，如果采样间隔较短，周期较长，打算长期使用，那么你可能需要一个大一点的表空间，如果每个半个小时采样一次，连续采样一周，数据量是很大的。本例创建一个

500M的测试表空间。

SQL> create tablespace perfstat
2 datafile 'd:\oracle\oradata\eygle\perfstat.dbf'
3 size 500M;

表空间已创建。

SQL>

检查是否存在安装所需要的脚本文件

SQL> host dir sp*
驱动器 D 中的卷没有标签。
卷的序列号是 5070-5982

D:\oracle\ora81\RDBMS\ADMIN 的目录

2000-02-18 14:34 1,841 spauto.sql
2000-06-15 15:21 64,492 spcpkg.sql
2000-02-18 14:34 916 spcreate.sql
2000-06-15 15:21 31,985 spctab.sql
2000-06-15 15:21 6,623 spcusr.sql
2000-06-15 15:53 47,820 spdoc.txt
2000-06-15 15:22 794 spdrop.sql
2000-06-15 15:21 3,732 spdtab.sql
2000-06-15 15:21 1,334 spdusr.sql
2000-06-15 15:22 7,002 sppurge.sql
2000-07-12 15:07 72,992 spreport.sql
2000-06-15 15:22 2,278 sptrunc.sql
2000-02-18 14:34 612 spuexp.par
2000-06-15 15:22 31,122 spup816.sql
14 个文件 273,543 字节
0 个目录 3,974,799,360 可用字节

接下来我们就可以开始安装Statspack了。这期间会提示你输入缺省表空间和临时表空间的位置,输入我们为perfstat用户创建的表空间和你的临时表空间。

SQL> @spcreate
.
.
.
.
Specify PERFSTAT user's default tablespace
输入 default_tablespace 的值: perfstat
Using perfstat for the default tablespace

用户已更改。

Specify PERFSTAT user's temporary tablespace
输入 temporary_tablespace 的值: temp

如果安装成功，你可以看到如下的输出信息：

SQL> host type spcpkg.lis
Creating Package STATSPACK...

程序包已创建。

没有错误。
Creating Package Body STATSPACK...

程序包主体已创建。

没有错误。

NOTE:
SPCPKG complete. Please check spcpkg.lis for any errors.

你可以查看.lis文件查看安装时的错误信息。

SQL> host dir *.lis
驱动器 D 中的卷没有标签。
卷的序列号是 5070-5982

D:\oracle\ora81\RDBMS\ADMIN 的目录

2002-12-03 17:25 204 spcpkg.lis
2002-12-03 17:25 2,276 spctab.lis
2002-12-03 17:25 3,965 spcusr.lis
2002-12-03 17:23 1,187 spdtab.lis
2002-12-03 17:24 351 spdusr.lis
5 个文件 7,983 字节
0 个目录 3,965,304,832 可用字节

SQL> host find “ORA-“ *.lis
SQL> host find "err" *.lis

---------- SPAUTO.LIS

---------- SPCPKG.LIS
SPCPKG complete. Please check spcpkg.lis for any errors.

---------- SPCTAB.LIS
SPCTAB complete. Please check spctab.lis for any errors.

---------- SPCUSR.LIS
SPCUSR complete. Please check spcusr.lis for any errors.

---------- SPDTAB.LIS

在UNIX上，你可以通过以下命令查看相应的错误信息

$ ls *.lis
spauto.lis spcpkg.lis spctab.lis spcusr.lis spdtab.lis spdusr.lis
$ grep ORA- *.lis
$ grep err *.lis
spcpkg.lis:SPCPKG complete. Please check spcpkg.lis for any errors.
spctab.lis:SPCTAB complete. Please check spctab.lis for any errors.
spcusr.lis:SPCUSR complete. Please check spcusr.lis for any errors.
spdtab.lis:SPDTAB complete. Please check spdtab.lis for any errors.
spdusr.lis:SPDUSR complete. Please check spdusr.lis for any errors.

在这一步，如果出现错误，那么你可以运行spdrop.sql脚本来删除这些对象。然后重新运行spcreate.sql来创建这些对象。运行 SQL*Plus, 以具有SYSDBA 权限的用户登陆：

SQL> @spdrop.sql
.
.
.
同义词已丢弃。off;

视图已丢掉。

同义词已丢弃。

视图已丢掉。

同义词已丢弃。

用户已丢弃

NOTE:
SPDUSR complete. Please check spdusr.lis for any errors.

SQL>

三. 测试安装好的Statspack
运行statspack.snap可以产生系统快照，运行两次，然后执行spreport.sql就可以生成一个基于两个时间点的报告。
如果一切正常，说明安装成功。

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;
BEGIN statspack.snap; END;

ERROR at line 1:
ORA-01401: inserted value too large for column
ORA-06512: at "PERFSTAT.STATSPACK", line 978
ORA-06512: at "PERFSTAT.STATSPACK", line 1612
ORA-06512: at "PERFSTAT.STATSPACK", line 71
ORA-06512: at line 1

这是Oracle的一个Bug，Bug号1940915。
该Bug自8.1.7.3后修正。
这个问题只会出现在多位的字符集,需要修改spcpkg.sql脚本，$ORACLE_HOME/rdbms/admin/spcpkg.sql，将"substr" 修改为 "substrb"，然后重新运行该脚本。
该脚本错误部分：

select l_snap_id
, p_dbid
, p_instance_number
, substr(sql_text,1,31)
．．．．．．．．．．．
substr 会将多位的字符, 当作一个byte.substrb 则会当作多个byte。在收集数据时， statpack 会将 top 10 的 sql 前 31 个字节存入数据表中,若在SQL的前31 个字有中文，就会出

现此错误。

四. 规划自动任务

Statspack正确安装以后，我们就可以设置定时任务，开始收集数据了。可以使用spatuo.sql来定义自动任务。
先来看看spauto.sql的关键内容：

dbms_job.submit(:jobno, 'statspack.snap;', trunc(sysdate+1/24,'HH'), 'trunc(SYSDATE+1/24,''HH'')', TRUE, :instno);

这个job任务定义了收集数据的时间间隔：
一天有24个小时，1440分钟，那么：
1/24 HH 每小时一次
1/48 MI 每半小时一次
1/144 MI 每十分钟一次
1/288 MI 每五分钟一次

我们可以修改spauto.sql来更改执行间隔，如：

dbms_job.submit(:jobno, 'statspack.snap;', trunc(sysdate+1/144,'MI'), 'trunc(SYSDATE+1/24,''HH'')', TRUE, :instno);

然后我们执行spauto，这样我们就建立了一个每30分钟执行一次的数据收集计划。你可以查看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/144,'MI'), 'trunc(SYSDATE+1/144,''MI'')', TRUE, :instno);
4 commit;
5 end;
6 /

PL/SQL 过程已成功完成。

SQL>
Job number for automated statistics collection for this instance

  1Note that this job number is needed when modifying or removing   
  2the job:   
  3JOBNO   
  4\----------   
  522   
  6  
  7Job queue process   
  8~~~~~~~~~~~~~~~~~   
  9Below is the current setting of the job_queue_processes init.ora   
 10parameter - the value for this parameter must be greater   
 11than 0 to use automatic statistics gathering:   
 12SQL&gt; show parameter job_queue_processes   
 13  
 14NAME TYPE value   
 15\------------------------------------ ------- ------------------------------   
 16job_queue_processes integer 12   
 17  
 18Next scheduled run   
 19~~~~~~~~~~~~~~~~~~   
 20The next scheduled run for this job is:   
 21SQL&gt; select job, next_date, next_sec   
 222 from user_jobs   
 233 where job = :jobno;   
 24  
 25JOB NEXT_DATE NEXT_SEC   
 26\---------- ---------- ----------------   
 2722 04-12月-02 15:00:00   
 28  
 29SQL&gt;   
 30  
 31  
 32  
 33五. 生成分析报告   
 34  
 35调用spreport.sql可以生成分析报告：   
 36  
 37SQL&gt; @spreport   
 38  
 39  
 40DB Id DB Name Inst Num Instance   
 41\----------- ------------ -------- ------------   
 421277924236 EYGLE 1 eygle   
 43  
 44  
 45Completed Snapshots   
 46  
 47Snap Snap   
 48Instance DB Name Id Snap Started Level Comment   
 49\------------ ------------ ----- ----------------- ----- ----------------------   
 50eygle EYGLE 1 04 12月 2002 14:4 5   
 518   
 522 04 12月 2002 15:0 5   
 530   
 543 04 12月 2002 15:1 5   
 550   
 564 04 12月 2002 15:2 5   
 570   
 58………………..   
 59  
 6087 05 12月 2002 02:2 5   
 613   
 6288 05 12月 2002 02:3 5   
 633   
 6489 05 12月 2002 02:4 5   
 653   
 6690 05 12月 2002 02:5 5   
 673   
 6891 05 12月 2002 03:0 5   
 693   
 7092 05 12月 2002 03:1 5   
 713   
 7293 05 12月 2002 03:2 5   
 733   
 7494 05 12月 2002 03:3 5   
 753   
 7695 05 12月 2002 03:4 5   
 773   
 7896 05 12月 2002 03:5 5   
 793   
 8097 05 12月 2002 04:0 5   
 813   
 8298 05 12月 2002 04:1 5   
 833   
 84eygle EYGLE 99 05 12月 2002 04:2 5   
 853   
 86100 05 12月 2002 04:3 5   
 873   
 88  
 89  
 90Specify the Begin and End Snapshot Ids   
 91~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~   
 92输入 begin_snap 的值: 1   
 93Begin Snapshot Id specified: 1   
 94  
 95输入 end_snap 的值: 100   
 96End Snapshot Id specified: 100   
 97  
 98  
 99Specify the Report Name   
100~~~~~~~~~~~~~~~~~~~~~~~   
101The default report file name is sp_1_100. To use this name,   
102press
103```
104<return> to continue, otherwise enter an alternative.   
105输入 report_name 的值: rep1205.txt   
106  
107  
108Using the report name rep1205.txt   
109  
110这样就生成了一个报告，可是如果中间停过机，那么你可能收到以下错误信息：   
111  
112ERROR: Snapshots chosen span an instance shutdown: RESULTS ARE INVALID   
113STATSPACK report for   
114  
115DB Name DB Id Instance Inst Num Release OPS Host   
116\------------ ----------- ------------ -------- ----------- --- ------------   
117EYGLE 1277924236 eygle 1 8.1.7.0.0 NO AM-SERVER   
118:ela := ;   
119*   
120ERROR 位于第 4 行:   
121ORA-06550: 第 4 行, 第 17 列:   
122PLS-00103: 出现符号 ";"在需要下列之一时：   
123(-+modnotnull<an identifier="">
124<a delimited-identifier="" double-quoted=""><a bind="" variable="">avg   
125countcurrentexistsmaxminpriorsqlstddevsumvarianceexecute   
126foralltimetimestampintervaldate   
127<a character="" literal="" set="" specification="" string="" with="">
128<a number=""><a single-quoted="" sql="" string="">   
129符号 "null" 被替换为 ";" 后继续。   
130ORA-06550: 第 6 行, 第 16 列:   
131PLS-00103: 出现符号 ";"在需要下列之一时：   
132(-+modnotnull<an identifier="">
133<a delimited-identifier="" double-quoted=""><a bind="" variable="">avg   
134countcurrentexistsmaxminpriorsqlstddevsumvarianceexecute   
135foralltimetimestampintervaldate   
136<a sql="" stri="" 六.="" 移除一个定时任务，可以如下操作:="" 移除定时任务=""> select job,log_user,priv_user,last_date,next_date,interval from user_jobs;   
137JOB LOG_USER LAST_DATE NEXT_DATE INTERVAL   
138\---------- ------------------------------ ------------------------------ ------------------------------ ----------- 22 PERFSTAT 2002-12-5:14:33:26 2002-12-5 14:43:00   
139  
140trunc(SYSDATE+1/144,'MI')   
141SQL&gt; execute dbms_job.remove('22')   
142  
143PL/SQL procedure successfully completed   
144  
145  
146  
147七. 删除历史数据   
148删除stats$snapshot数据表中的相应数据，其他表中的数据会相应的级连删除：   
149  
150SQL&gt; select max(snap_id) from stats$snapshot;   
151  
152MAX(SNAP_ID)   
153\------------   
154166   
155  
156SQL&gt; delete from stats$snapshot where snap_id &lt; = 166;   
157  
158143 rows deleted   
159  
160你可以更改snap_id的范围以保留你需要的数据。   
161在以上删除过程中，你可以看到所有相关的表都被锁定。   
162  
163SQL&gt; select a.object_id,a.oracle_username ,b.object_name   
164from v$locked_object a,dba_objects b   
165where a.object_id = b.object_id   
166/   
167  
168  
169OBJECT_ID ORACLE_USERNAME OBJECT_NAME   
170\------------------------------------- --------------------------------------------------------------------------------   
171156 PERFSTAT SNAP$   
17239700 PERFSTAT STATS$LIBRARYCACHE   
17339706 PERFSTAT STATS$ROLLSTAT   
17439712 PERFSTAT STATS$SGA   
17539754 PERFSTAT STATS$PARAMETER   
17639745 PERFSTAT STATS$SQL_STATISTICS   
17739739 PERFSTAT STATS$SQL_SUMMARY   
17839736 PERFSTAT STATS$ENQUEUESTAT   
17939733 PERFSTAT STATS$WAITSTAT   
18039730 PERFSTAT STATS$BG_EVENT_SUMMARY   
18139724 PERFSTAT STATS$SYSTEM_EVENT   
18239718 PERFSTAT STATS$SYSSTAT   
18339715 PERFSTAT STATS$SGASTAT   
18439709 PERFSTAT STATS$ROWCACHE_SUMMARY   
18539703 PERFSTAT STATS$BUFFER_POOL_STATISTICS   
18639697 PERFSTAT STATS$LATCH_MISSES_SUMMARY   
18739679 PERFSTAT STATS$SNAPSHOT   
18839682 PERFSTAT STATS$FILESTATXS   
18939688 PERFSTAT STATS$LATCH   
190174 PERFSTAT JOB$   
191  
19220 rows selected   
193  
194  
195  
196八. 调整STATSPACK的收集门限   
197  
198Statspack有两种类型的收集选项：   
199级别（level）：控制收集数据的类型   
200门限（threshold）：设置收集的数据的阈值。   
201  
2021．级别（level）   
203Statspack共有三种快照级别，默认值是5   
204a.level 0: 一般性能统计。包括等待事件、系统事件、系统统计、回滚段统计、行缓存、SGA、会话、锁、缓冲池统计等等。   
205b.level 5: 增加SQL语句。除了包括level0的所有内容，还包括SQL语句的收集，收集结果记录在stats$sql_summary中。   
206c.level 10: 增加子锁存统计。包括level5的所有内容。并且还会将附加的子锁存存入stats$lathc_children中。在使用这个级别时需要慎重，建议在Oracle support的指导下进行。   
207  
208可以通过statspack包修改缺省的级别设置   
209  
210SQL&gt;execute statspack.snap(i_snap_level=&gt;0,i_modify_parameter=&gt;’true’);   
211  
212通过这样的设置，以后的收集级别都将是0级。   
213如果你只是想本次改变收集级别，可以忽略i_modify_parameter参数。   
214  
215SQL&gt;execute statspack.snap(i_snap_level=&gt;10);   
216  
2172．快照门限   
218  
219快照门限只应用于stats$sql_summary表中获取的SQL语句。   
220因为每一个快照都会收集很多数据，每一行都代表获取快照时数据库中的一个SQL语句，所以stats$sql_summary很快就会成为Statspack中最大的表。   
221  
222门限存储在stats$statspack_parameter表中。让我们了结一下各种门限：   
223a. executions_th 这是SQL语句执行的数量(默认值是100)   
224b. disk_reads_tn 这是SQL语句执行的磁盘读入数量（默认值是1000）   
225c. parse_calls_th 这是SQL语句执行的解析调用的数量（默认值是1000）   
226d. buffer_gets_th 这是SQL语句执行的缓冲区获取的数量（默认值是10000）   
227  
228任何一个门限值超过以上参数就会产生一条记录。   
229  
230通过调用statspack.modify_statspack_parameter函数我们可以改变门限的默认值。   
231例如：   
232SQL&gt;execute statspack.modify_statspack_parameter(i_buffer_gets_th=&gt;100000,i_disk_reads_th=&gt;100000;   
233  
234  
235  
236一一. 在815上的安装配置   
237a. 数据库状况   
238  
239SQL&gt; select * from v$version;   
240  
241BANNER   
242\----------------------------------------------------------------   
243Oracle8i Enterprise Edition Release 8.1.5.0.0, 64 bit - Producti   
244PL/SQL Release 8.1.5.0.0 - Production   
245CORE Version 8.1.3.0.0 - Production   
246TNS for HPUX: Version 8.1.5.0.0 - Production   
247NLSRTL Version 3.4.0.0.0 - Production   
248  
249b. 运行statscbps.sql   
250  
251SQL&gt; @statscbps.sql   
252  
253View created.   
254  
255  
256Synonym created.   
257  
258  
259Grant succeeded.   
260  
261SQL&gt;   
262  
263其他步骤都是相同的。   
264  
265  
266关于结果分析，不是几句话就说得清楚的。   
267我后面也会整理一些常见的说明出来。   
268  
269这篇的目的是给出一个安装、配置的方法，第一帖中已经说明了。   
270  
271【发表回复】【查看CU论坛原帖】【添加到收藏夹】【关闭】</a></a></a></an></a></a></a></a></a></an></return>
272```