Statspack安装配置使用说明

eygle (eygle@itpub.net)

在数据库管理中，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/00-Mar/index.html?o20tun.htmlhttp://www.Oracle.com/oramag/Oracle/00-Mar/index.html?statspack-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 Oracleora81rdbmsadmin

D:Oracleora81RDBMSADMIN>sqlplus internal

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

(c) Copyright 2000 Oracle Corporation. All rights reserved.请输入口令:连接到:
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:OracleORADATAEYGLESYSTEM01.DBF
D:OracleORADATAEYGLERBS01.DBF
D:OracleORADATAEYGLEUSERS01.DBF
D:OracleORADATAEYGLETEMP01.DBF
D:OracleORADATAEYGLETOOLS01.DBF
D:OracleORADATAEYGLEINDX01.DBF
D:OracleORADATAEYGLEDR01.DBF
D:OracleORADATAEYGLEGGQIANG01.DBF
D:OracleORADATAEYGLEEQSP01.DBF
D:OracleORA81DATABASEMP5_DATA.DBF
D:OracleORA81DATABASEMP5_INDX.DBF
D:OracleORA81DATABASEMP5_TEMP.DBF
D:OracleORA81DATABASEMP5_IDNX.DBF
D:OracleORADATAEYGLEHRM01.DBF
D:OracleORADATAEYGLETIB.DBF
D:OracleORADATAEYGLEEQSP02.DBF
D:OracleORADATAEYGLERMAN_TS.DBF
D:OracleORADATAEYGLEEQSP03.DBF
D:OracleORADATAEYGLEEAPP01.DBF
D:OracleORADATAEYGLEEQSP04.DBF
D:OracleORADATAEYGLEAM01.DBF
D:OracleORADATAEYGLESYSTEM02.DBF
D:OracleORADATAEYGLEFNC01.DBF
D:OracleORADATAEYGLEHH_AM01.ORA已选择24行。

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

SQL> CREATE tablespace perfstat
2 datafile 'd:Oracleoradataeygleperfstat.dbf'
3 size 500M;表空间已创建。

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

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

D:Oracleora81RDBMSADMIN的目录

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:Oracleora81RDBMSADMIN的目录

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
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Note that this job number is needed when modifying or removing
the job:
JOBNO
----------
22

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:
SQL> show parameter job_queue_processes

NAME TYPE VALUE
------------------------------------ ------- ------------------------------
job_queue_processes integer 12

Next scheduled run
~~~~~~~~~~~~~~~~~~
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
---------- ---------- ----------------
22 04-12月-02 15:00:00

SQL>

五.生成分析报告调用spreport.sql可以生成分析报告：

SQL> @spreport


DB Id DB Name Inst Num Instance
----------- ------------ -------- ------------
1277924236 EYGLE 1 eygle


Completed Snapshots

Snap Snap
Instance DB Name Id Snap Started Level Comment
------------ ------------ ----- ----------------- ----- ----------------------
eygle EYGLE 1 04 12月2002 14:4 5
8
2 04 12月2002 15:0 5
0
3 04 12月2002 15:1 5
0
4 04 12月2002 15:2 5
0
………………..

87 05 12月2002 02:2 5
3
88 05 12月2002 02:3 5
3
89 05 12月2002 02:4 5
3
90 05 12月2002 02:5 5
3
91 05 12月2002 03:0 5
3
92 05 12月2002 03:1 5
3
93 05 12月2002 03:2 5
3
94 05 12月2002 03:3 5
3
95 05 12月2002 03:4 5
3
96 05 12月2002 03:5 5
3
97 05 12月2002 04:0 5
3
98 05 12月2002 04:1 5
3
eygle EYGLE 99 05 12月2002 04:2 5
3
100 05 12月2002 04:3 5
3


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


Specify the Report Name
~~~~~~~~~~~~~~~~~~~~~~~
The default report file name is sp_1_100. To use this name,
press <return> to continue, otherwise enter an alternative.输入report_name的值: rep1205.txt


Using the report name rep1205.txt这样就生成了一个报告，可是如果中间停过机，那么你可能收到以下错误信息：

ERROR: Snapshots chosen span an instance shutdown: RESULTS ARE INVALID
STATSPACK report for

DB Name DB Id Instance Inst Num Release OPS Host
------------ ----------- ------------ -------- ----------- --- ------------
EYGLE 1277924236 eygle 1 8.1.7.0.0 NO AM-SERVER
:ela := ;
*
ERROR位于第4行:
ORA-06550:第4行,第17列:
PLS-00103:出现符号";"在需要下列之一时：
(-+modnotnull<an identifier>
<a double-quoted delimited-identifier><a bind variable>avg
countcurrentexistsmaxminpriorsqlstddevsumvarianceexecute
foralltimetimestampintervaldate
<a string literal with character set specification>
<a number><a single-quoted SQL string>符号"null"被替换为";"后继续。
ORA-06550:第6行,第16列:
PLS-00103:出现符号";"在需要下列之一时：
(-+modnotnull<an identifier>
<a double-quoted delimited-identifier><a bind variable>avg
countcurrentexistsmaxminpriorsqlstddevsumvarianceexecute
foralltimetimestampintervaldate
<a stri