马上注册,结交更多数据大咖,获取更多知识干货,轻松玩转大数据
您需要 登录 才可以下载或查看,没有帐号?立即注册
x
在数据库管理中,Oracle提供的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 (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:\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 已选择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 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 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ 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 …………….. 87 05 12月 2002 02:2 5 88 05 12月 2002 02:3 5 3 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 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 avg countcurrentexistsmaxminpriorsqlstddevsumvarianceexecute foralltimetimestampintervaldate 符号 "null" 被替换为 ";" 后继续。 ORA-06550: 第 6 行, 第 16 列: PLS-00103: 出现符号 ";"在需要下列之一时: (-+modnotnull avg countcurrentexistsmaxminpriorsqlstddevsumvarianceexecute foralltimetimestampintervaldate 六. 移除定时任务 移除一个定时任务,可以如下操作: SQL> select job,log_user,priv_user,last_date,next_date,interval from user_jobs; JOB LOG_USER LAST_DATE NEXT_DATE INTERVAL ---------- ------------------------------ ------------------------------ --- -------- 22 PERFSTAT 2002-12-5:14:33:26 2002-12-5 14:43:00 trunc(SYSDATE+1/144,'MI') SQL> execute dbms_job.remove('22') PL/SQL procedure successfully completed 七. 删除历史数据 删除stats$snapshot数据表中的相应数据,其他表中的数据会相应的级连删除: SQL> select max(snap_id) from stats$snapshot; MAX(SNAP_ID) ------------ 166 SQL> delete from stats$snapshot where snap_id < = 166; 143 rows deleted 你可以更改snap_id的范围以保留你需要的数据。 在以上删除过程中,你可以看到所有相关的表都被锁定。 SQL> select a.object_id,a.oracle_username ,b.object_name from v$locked_object a,dba_objects b where a.object_id = b.object_id / OBJECT_ID ORACLE_USERNAME OBJECT_NAME ------------------------------------- ------------------------------ 39700 PERFSTAT STATS$LIBRARYCACHE 39706 PERFSTAT STATS$ROLLSTAT 174 PERFSTAT JOB$ 20 rows selected 八. 调整STATSPACK的收集门限 Statspack有两种类型的收集选项: 级别(level):控制收集数据的类型 门限(threshold):设置收集的数据的阈值。 1.级别(level) Statspack共有三种快照级别,默认值是5 a.level 0: 一般性能统计。包括等待事件、系统事件、系统统计、回滚段统计、行缓存、SGA、会话、锁、缓冲池统计等等。 b.level 5: 增加SQL语句。除了包括level0的所有内容,还包括SQL语句的收集,收集结果记录在stats$sql_summary中。 c.level 10: 增加子锁存统计。包括level5的所有内容。并且还会将附加的子锁存存入stats$lathc_children中。在使用这个级别时需要慎重,建议在Oracle support的指导下进行。 可以通过statspack包修改缺省的级别设置 SQL>execute statspack.snap(i_snap_level=>0,i_modify_parameter=>’true’); 通过这样的设置,以后的收集级别都将是0级。 如果你只是想本次改变收集级别,可以忽略i_modify_parameter参数。 SQL>execute statspack.snap(i_snap_level=>10); 2.快照门限 快照门限只应用于stats$sql_summary表中获取的SQL语句。 因为每一个快照都会收集很多数据,每一行都代表获取快照时数据库中的一个SQL语句,所以stats$sql_summary很快就会成为Statspack中最大的表。 门限存储在stats$statspack_parameter表中。让我们了结一下各种门限: a. executions_th 这是SQL语句执行的数量(默认值是100) b. disk_reads_tn 这是SQL语句执行的磁盘读入数量(默认值是1000) c. parse_calls_th 这是SQL语句执行的解析调用的数量(默认值是1000) d. buffer_gets_th 这是SQL语句执行的缓冲区获取的数量(默认值是10000) 任何一个门限值超过以上参数就会产生一条记录。 通过调用statspack.modify_statspack_parameter函数我们可以改变门限的默认值。 例如: SQL>execute statspack.modify_statspack_parameter(i_buffer_gets_th=>100000,i_disk_reads_th=>100000; 一一. 在815上的安装配置 a. 数据库状况 SQL> select * from v$version; BANNER ---------------------------------------------------------------- Oracle8i Enterprise Edition Release 8.1.5.0.0, 64 bit - Producti PL/SQL Release 8.1.5.0.0 - Production CORE Version 8.1.3.0.0 - Production TNS for HPUX: Version 8.1.5.0.0 - Production NLSRTL Version 3.4.0.0.0 - Production b. 运行statscbps.sql SQL> @statscbps.sql View created. Synonym created. Grant succeeded. SQL> 其他步骤都是相同的。
|