暂无图片
暂无图片
暂无图片
暂无图片
暂无图片

AWR 报告深度解读:从Statspack到AWR的传承和演进

原创 eygle 2019-08-29
5858

为了系统化的梳理 AWR 的知识体系,我们整理了一个系列文章,希望从原理、使用到 AWR 报告的解读,给读者展示全面的 AWR 知识体系,本文是这个系列文章的第二篇。


Oracle数据库的性能优化一直以来都是DBA关注的焦点,在不同的版本中,Oracle都提供了相关的工具用于数据库的性能诊断。在这些工具中,最广为使用的就是Statspack和AWR,后者在Oracle Database 10g中开始提供,是前者的演进。让我们从 Statspack 开始逐步了解和深入这个数据库诊断的世界。相关阅读:AWR的原理和基本功能


在Oracle数据库中,性能一直是倍受瞩目的关键,在数据库内部,Oracle通过时间和事件来精确记录数据库的运行状况和性能波动,这其中非常核心的是等待模型(OWI – Oracle Wait Interface)。在实现上,Oracle通过一系列的动态性能视图来实时展现数据库的核心运行状况。

1. Oracle性能分析沿革

然而谈到分析,就往往需要一些工具和手段去具体构建基线,计算趋势等,在这条路线上,Oracle大致经历了从脚本到工具再到产品的演进过程。

• 脚本时代

在Oracle 8i之前,常用于诊断数据库的工具是两个脚本UTLBSTAT.SQL和UTLESTAT.SQL(这两个脚本直至现在的12c版本仍然包含在软件发布中),BSTAT/ESTAT是一个非常简单的性能诊断工具。UTLBSTAT获得起始时部分V$视图的快照,UTLESTAT通过当前状态和先前的快照比较生成一个报表,通过这个报表,DBA们可以进行分析和诊断。 

• 工具时代

Oracle Statspack工具从Oracle 8.1.6开始被引入,将BSTAT/ESTAT的原理推进了一步,通过运行Statspack相关脚本,可以以采样形式定时收集数据库性能数据,并持久化存储,从而能够实现更佳的趋势分析和长期留存,通过Statspack收据的数据可以在任意两个采样点之间生成分析报告,辅助DBA们分析和判断,Statspack工具推出之后,获得DBA们的广泛采用,成为了Oracle专家用来诊断数据库性能的强有力工具。

• 产品时代

从Oracle 10g开始,Oracle推出了新的工具 - 自动负载信息库(Automatic Workload Repository,AWR),AWR收集关于该特定数据库的操作统计信息和其他统计信息,Oracle以固定的时间间隔(默认为每小时一次)为其所有重要统计信息和负载信息执行一次快照,并将这些快照存储在AWR中。这些信息在AWR中保留给定的时间(默认为一周),然后被清除。AWR中调用的某些功能属于Oracle的收费范畴,至此进入了产品时代。


从Oracle性能分析手段的沿革,可以清晰的看到从一个简单的想法发展为工具以及产品的过程,现在很多数据库监控、优化工具产品的发展过程莫不与此类同。

具体比较起来,Statspack需要由用户自行运行脚本安装、调度,并且其收集的信息较为有限;而自Oracle 10g开始的AWR缺省自动调度、采集和清理数据,收集的信息也大大增加,为DBA诊断和优化数据库提供了极大的支持。

由于AWR收集的信息十分完备,并且可以持续累积存储数据库的性能数据,所以经常被称为“数据库的数据仓库”;而在Oracle 12c中,基于OEM的一个新功能AWR Warehouse被开发出来,可以采集企业级环境中多套数据库的AWR信息,集中持久存储,真正的实现了“数据仓库”的功能。

但是不管是Statspack还是AWR,其本质都是相同的,就是通过持续不断地收集数据库或者系统的性能信息(主要来自动态性能视图)来提炼有意义的数据库报告,以作为性能分析的基础。那么实际上,深入了解Oracle的各种性能指标、统计数据、等待事件仍然是了解Oracle数据库的基础。


在statspack的第一个版本中,从statspack.doc说明文件中可以看到,这个产品的两个核心人物:

Author:  Connie Dialeris Green

Contributing Authors:  Graham Wood

Connie Dialeris Green自1995至2009年服务于Oracle公司,在那之后,他创立了一个以预防食物过敏和宣传食物过敏知识为使命的组织。在他的履历中,关于Statspack有这样一段简短介绍:

Prepared and made production-worthy the internal-Statspack performance diagnostic tool widespread internal and customer distribution in Oracle8. Designed, developed and maintained all features of Statspack since it was first shipped.

自从第一个版本开始,他一直维护着statspack工具,虽然后来他离开了Oracle,但是时至今日,在12c版本的说明文档中,他仍然是第一位的作者:

Author:  Connie Dialeris Green

Contributors:  Cecilia Gervasio, Graham Wood, Russell Green, Patrick Tearle,

               Harald Eri, Stefan Pommerenk, Vladimir Barriere, Kathryn Chou

而最初追随他的Graham Wood成为了AWR之父,并且仍然活跃在Oracle技术领域,他目前是Oracle RWP(Real-World Performance)团队的主要领导者之一。


在 Connie 的履历上,她还自豪的写到:

在 Oracle 8 中设计并研发了广受内部和外部客户欢迎,并广泛使用的 Statspack性能诊断工具。

 设计,开发和维护Statspack自首次发布以来的所有功能。


我还在一次和 Graham Wood 的会面中谈起 Connie :

ConnieDialerisGreen.jpg

在离开 Oracle 之后,Connie 创立了 California Advocates for People with Food Allergies & SF Bay Area Food Allergy Network (加州食品过敏人士和SF湾区食品过敏网络的倡导者)


言归正传,Oracle Statspack工具从Oracle 8.1.6开始被引入,并马上成为DBA和Oracle专家用来诊断数据库性能的强有力的工具。通过Statspack可以很容易地收集数据库性能数据,并通过这些数据进而分析确定Oracle数据库的瓶颈所在,通过Statspack收据的数据生成分析报告,也可以使远程技术支持人员迅速了解数据库运行状况。因此了解和使用Statspack对于Oracle DBA来说是一项必备技能。Statspack可以通过手工方式来执行,也可以通过定时任务来进行定期采样,使用上非常灵活。


从Oracle 10g开始,Oracle推出了新的工具──自动负载信息库(Automatic Workload Repository,AWR),AWR收集关于该特定数据库的操作统计信息和其他统计信息,Oracle以固定的时间间隔(默认为每小时一次)为其所有重要统计信息和负载信息执行一次快照,并将这些快照存储在AWR中。这些信息在AWR中保留给定的时间(默认为一周),然后被清除。执行快照的频率及其保持时间都可以自定义,以满足不同环境的独特需要。


比较起来,Statspack需要由用户自行安装调度,并且其收集的信息较为有限;AWR收集的信息大大增加,为DBA诊断和优化数据库提供了极大的支持。由于AWR收集的信息十分完备,所以经常被称为“数据库的数据仓库”。从Oracle 10g开始,AWR被设计用来取代Statspack工具,并且在Oracle 11g中被不断增强。


但是不管是Statspack还是AWR,其本质都是相同的,就是通过持续不断地收集数据库或者系统的性能信息来提炼有意义的数据库报告,以作为性能分析的基础。那么实际上,深入了解Oracle的各种性能指标、统计数据、等待事件仍然是了解Oracle数据库的基础。


虽然 AWR 被广泛使用,但是 Statspack 仍然有其用武之地,这主要体现在两个方面:

  1. 在 11g 的 ADG 环境中,可以通过 Statspack 采样和诊断备库性能;

  2. 在不具备 AWR 授权许可的情况下;


关于第二点我们需要做一点点说明,AWR 工具中的很多功能是需要单独收费的,需要向 Oracle购买授权许可,AWR 的核心 Oracle Database Diagnostic Pack 是需要收费的。 AWR 和 ADDM 组件中需要授权的包括 dbms_workload_repository 包, awrrpt.sql 和 awrrpti.sql 报告, 任何 dba_hist 和 v$active_session_history 视图, ADDM - Automatic Database Diagnostic Monitor, 和所有的 dba_advisor 视图。

但是也存在极少数例外,文档中明确说明:

All data dictionary views beginning with the prefix DBA_HIST_ are part of this pack, along with their underlying tables.The only exception are the views: DBA_HIST_SNAPSHOT, DBA_HIST_DATABASE_INSTANCE, DBA_HIST_SNAP_ERROR, DBA_HIST_SEG_STAT, DBA_HIST_SEG_STAT_OBJ, and DBA_HIST_UNDOSTAT. They can be used without the Diagnostic Pack license.

如果查询以下参数,你就可以了解自己是否使用了需要授权的特性 

SQL> show parameter CONTROL_MANAGEMENT_PACK_ACCESS 

NAME TYPE VALUE 
------------------------------------ ----------- ---------------
control_management_pack_access       string    DIAGNOSTIC+TUNING 


本文将对Statspack和AWR的使用进行介绍,并对最终支撑这些工具的底层数据进行深入解析。



Statspack概述

在数据库中Statspack初始并未安装,但是其安装配置脚本随软件提供,要使用这个工具,首先需要通过一系列的脚本来安装和初始化这个工具。


Statspack的脚本文件位于$ORACLE_HOME/rdbms/admin目录下,对于Oracle 8.1.6来说,脚本是一组以stat开头的文件;对于Oracle 8.1.7及以后版本来说,是一组以sp开头的文件。在Oracle 8.1.6中,Statspack第一次发布,但是经过修正,该工具也可以用于Oracle 8~Oracle 8.1.5的版本。


在Statspack发布之前,常用于诊断数据库的工具是两个脚本UTLBSTAT.SQL和UTLESTAT.SQL,BSTAT/ESTAT是一个非常简单的性能诊断工具。UTLBSTAT获得开始时很多V$视图的快照,UTLESTAT通过先前的快照和当前视图生成一个报表;该报表实际上相当于Statspack中的两个采样点。 


Statspack通过连续的采样,能够提供至关重要的趋势分析数据。这是一个巨大的进步;Statspack出现之后,BSTAT/ESTAT工具已经渐渐被大家所遗忘。


在使用授权上,Statspack的重要特点是免费,而AWR工具涉及的很多特性都是需要进行收费的,在注重版权的企业,Statspack仍然是很多DBA的首要选择。


数据库参数修改

为了能够顺利安装和运行Statspack,可能需要设置以下两个系统参数。


 ·   job_queue_processes

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

SQL> alter system set job_queue_processes = 6;
System altered

在Oracle 9i或Oracle 10g中,如果使用了spfile,可以指定修改范围,如both,这样该修改在当前及数据库重新启动之后都保持有效(如果在仍然使用pfile,那么更改方法同Oracle 8i相同):

SQL> alter system set job_queue_processes = 6 scope=both;
系统已更改。


  ·  timed_statistics

设置该参数为True时,Oracle将实时收集操作系统的计时信息,这些信息可被用来显示时间等统计信息、优化数据库和SQL语句。

使用Statspack收集统计信息时建议将该值设置为True,在Oracle 9i之前这个参数的缺省值为False,从Oracle 9i开始这个参数缺省值为True,将timed_statistics设置为True会对数据库带来一定的性能影响,但是这个性能影响与收集信息所带来的好处相比是微不足道的。

该参数使收集的时间信息存储在V$SESSTATS和V$SYSSTATS等动态性能视图中。timed_statistics参数可以在实例级进行动态更改:

SQL> alter system set timed_statistics = true;
System altered


安装Statspack

在Oracle 9i之前,安装Statspack需要用internal身份登录;在Oracle 9i及之后,由于取消了internal用户,需要用sys用户以sysdba身份登录到数据库安装;安装需要在数据库服务器本地进行,或者通过telnet等工具登录到服务器。


安装过程通常很简单,在Oracle 8.1.6版本中需要运行statscre.sql;在Oracle 8.1.6之后版本中运行spcreate.sql即可,但是为了更好的规划,还有一些准备工作需要提前做好。


首先登录到数据库,最好切换到$ORACLE_HOME/rdbms/admin目录,这样执行脚本就可以方便些。在Oracle 8i中,使用internal用户:

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_TIME
----------------------------------------------------------------
eygle          AM-SERVER 8.1.7.0.0         22-11月-02


在Oracle 9i中,可以使用如下方式登录:

D:\oracle\ora92\rdbms\admin>sqlplus "/ as sysdba"
SQL*Plus: Release 9.2.0.3.0 - Production on 星期四 7月 10 19:18:54 2003
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
 
连接到: Oracle9i Enterprise Edition Release 9.2.0.3.0 - Production
With the Partitioning, Oracle Label Security, OLAP and Oracle Data Mining options
JServer Release 9.2.0.3.0 - Production
SQL>


由于Statspack需要一定的存储空间,最好建立独立的表空间;所以接下来检查数据文件路径及磁盘空间,以决定创建数据文件的位置:

SQL> select file_name from dba_data_files;
FILE_NAME
---------------------------------------------------------------------------
D:\ORACLE\ORADATA\EYGLE\SYSTEM01.DBF
D:\ORACLE\ORADATA\EYGLE\TEMP01.DBF
……
D:\ORACLE\ORADATA\EYGLE\HH_AM01.ORA


创建存储数据的表空间,如果采样间隔较短,周期较长,打算长期使用,那么就可能需要一个大一点的表空间,假设每个半个小时采样一次,连续采样一周,那么Statspack产生的数据量是很大的。本例创建一个500MB的测试表空间。


注意:这里创建的表空间不能太小,如果太小创建对象会失败,至少需要建立100MB表空间,如果打算长期使用,可以建立稍大的表空间,本例创建500MB LMT类型表空间。


SQL> create tablespace perfstat
  2  datafile 'd:\oracle\oradata\eygle\perfstat.dbf'
  3  size 500M
  4  extent management local;
 
表空间已创建。


检查是否存在安装所需要的脚本文件(对于不同的版本,脚本有所不同):

E:\Oracle\ora92\rdbms\admin>dir /w sp*
  E:\Oracle\ora92\rdbms\admin 的目录
spauto.sql     spcpkg.sql     spcreate.sql   spctab.sql     spcusr.sql     spdoc.txt
spdrop.sql     spdtab.sql     spdusr.sql     sppurge.sql    sprepins.sql   spreport.sql   sprepsql.sql   sptrunc.sql    spuexp.par     spup816.sql    spup817.sql    spup90.sql
              18 个文件        510,296 字节
               0 个目录  4,146,565,120 可用字节


接下来就可以开始安装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


注意:在Statspack创建过程中,当提示输入口令时,可以输入一个明文口令,但是如果输入口令不符合规范(如123或以数字开头的口令),创建会失败。


... Creating PERFSTAT user ...
 
Choose the PERFSTAT user's password.
 
Not specifying a password will result in the installation FAILING
Specify PERFSTAT password
输入 perfstat_password 的值: 123
123
PL/SQL 过程已成功完成。
 
create user perfstat identified by 123
*
ERROR 位于第 1 行:
ORA-00988: 缺少或无效口令

 

输入口令时可以暂时输入perfstat,稍后可以再进行更改。


注意:曾经有朋友在输入口令这里总是遇到莫名其妙的问题,如果实在难以诊断,可以手工修改spcreate.sql的脚本,将口令直接写入脚本,随后的创建就不会提示口令了。


如果安装成功,可以看到如下的输出信息:

….
Creating Package STATSPACK...
 
程序包已创建。
 
没有错误。
Creating Package Body STATSPACK...
 
程序包主体已创建。
 
没有错误。
 
NOTE:
SPCPKG complete. Please check spcpkg.lis for any errors.


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

SQL> host dir *.lis
 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> @spdrop.sql
.
视图已丢掉。
 
同义词已丢弃。
 
用户已丢弃
 
NOTE:
SPDUSR complete. Please check spdusr.lis for any errors.
 
SQL>


spcreate的内容

    完成创建之后,我们花点时间对spcreate.sql脚本进行一点研究。这个脚本很简单,主要工作是调用另外3个脚本:

--  Create PERFSTAT user and required privileges
@@spcusr
 
--
--  Build the tables and synonyms
connect perfstat/&&perfstat_password
@@spctab
--  Create the statistics Package
@@spcpkg


其中,第一个脚本是用来创建用户并进行授权,第二个脚本用于创建表及同义词等对象,第三个脚本则用于创建最重要的系统包。


如果在使用Statspack的过程中遇到问题,那么可以通过查看这些脚本文件来找到Statspack的运行机制及原理,通常都可以找到问题的答案。在本章后面部分,我们将对这些文件的内容进行简单介绍。


测试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
…


在Oracle 8.1.7的某些版本中,可能会得到以下错误:

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个字节有中文,就会出现此错误。


在不同的版本中,Oracle的Statspack可能存在不同的Bug,我们可以根据具体错误,认真分析,找出问题的根本原因所在。


规划自动任务

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 M,即每半小时一次

 · 1/144 MI,即每10分钟一次

 ·  1/288 MI,即每5分钟一次

可以修改spauto.sql来更改执行间隔,例如:

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


然后执行spauto,这样就建立了一个每30分钟执行一次的数据收集计划。可以查看spauto.lis来获得输出信息:

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
----------
        28
 
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                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
job_queue_processes                  integer        5
 
Next scheduled run
~~~~~~~~~~~~~~~~~~
The next scheduled run for this job is:
       JOB NEXT_DATE NEXT_SEC
---------- --------- ----------------
        28 15-AUG-03 16:00:00


关于采样间隔,通常建议以1小时为时间间隔,对于有特殊需要的环境,可以设置更短的,如半小时作为采样间隔,但是不推荐更短。因为statspack的执行本身需要消耗资源,对于繁忙的生产系统,太短的采样对系统的性能会产生较大的影响(甚至会使Statspack的执行出现在采样数据中)。如果是实时的问题诊断,需要采样分析,那么可以根据具体需要手工执行采样。


生成分析报告

调用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:48     5
                              2 04 12月 2002 15:00     5
………………..
                             98 05 12月 2002 04:13     5
eygle        EYGLE           99 05 12月 2002 04:23     5
                            100 05 12月 2002 04:33     5
 
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


注意:在生成报告之前,Oracle会查询列出所有采样的Snap ID,可以按照先后顺序任选两个Snap ID生成一个报告,begin_snap就是指起始Snap号,end_snap指终止Snap号;生成报表的名称可以自己定义,也可以采用缺省的;输入完成之后,会在当前目录下生成一个报告。


如果系统在采样过程中间停过机,那么就可能收到以下错误信息:

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


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


移除定时任务

移除一个定时任务,可以如下操作:

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

 

当完成了一个采样报告,就应该及时移除这个job任务,在生产环境中,遗漏一个无人照顾的job是非常危险的,如果Statspack连续运行一个星期或更长时间,采样的数据量可能会是非常惊人的。有的生产企业因疏忽而当机!


删除历史数据

删除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_namefrom v$locked_object a,dba_objects bwhere a.object_id = b.object_id/
OBJECT_ID ORACLE_USERNAME                OBJECT_NAME
------------------------------------- -----------------------------------------
       156 PERFSTAT                       SNAP$
     39700 PERFSTAT                       STATS$LIBRARYCACHE
     39706 PERFSTAT                       STATS$ROLLSTAT
     39712 PERFSTAT                       STATS$SGA
     39754 PERFSTAT                       STATS$PARAMETER
     39745 PERFSTAT                       STATS$SQL_STATISTICS
     39739 PERFSTAT                       STATS$SQL_SUMMARY
     39736 PERFSTAT                       STATS$ENQUEUESTAT
     39733 PERFSTAT                       STATS$WAITSTAT
     39730 PERFSTAT                       STATS$BG_EVENT_SUMMARY
     39724 PERFSTAT                       STATS$SYSTEM_EVENT
     39718 PERFSTAT                       STATS$SYSSTAT
     39715 PERFSTAT                       STATS$SGASTAT
     39709 PERFSTAT                       STATS$ROWCACHE_SUMMARY
     39703 PERFSTAT                       STATS$BUFFER_POOL_STATISTICS
     39697 PERFSTAT                       STATS$LATCH_MISSES_SUMMARY
     39679 PERFSTAT                       STATS$SNAPSHOT
     39682 PERFSTAT                       STATS$FILESTATXS
     39688 PERFSTAT                       STATS$LATCH
            174 PERFSTAT                       JOB$
 
20 rows selected


sppurge.sql脚本可以用于删除一定范围的采样数据,运行该脚本,输入一个采样范围,会自动删除该区间之内的采样数据,但是需要注意的是,删除会产生大量的归档日志,在生产环境上要特别关注:

SQL> @?/rdbms/admin/sppurge
 
Database Instance currently connected to
========================================
                                Instance
   DB Id    DB Name    Inst Num Name
----------- ---------- -------- ----------
 2213325640 RAC               2 RAC2
 
Snapshots for this database instance
====================================
 
          Snap
 Snap Id Level Snapshot Started      Host            Comment
-------- ----- --------------------- --------------- -------------------------
   11251     5  28 Oct 2008 17:00:04 p55a2
   11252     5  28 Oct 2008 18:00:03 p55a2
........
   11287     5  30 Oct 2008 05:00:02 p55a2
   11288     5  30 Oct 2008 06:00:00 p55a2
   11289     5  30 Oct 2008 07:00:03 p55a2
   11290     5  30 Oct 2008 08:00:01 p55a2
   11291     5  30 Oct 2008 09:00:01 p55a2
 
Warning
~~~~~~~
sppurge.sql deletes all snapshots ranging between the lower and
upper bound Snapshot Id's specified, for the database instance
you are connected to.
 
You may wish to export this data before continuing.
 
Specify the Lo Snap Id and Hi Snap Id range to purge
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Enter value for losnapid: 11251
Using 11251 for lower bound.
Enter value for hisnapid: 11290
Using 11290 for upper bound.
 
Deleting snapshots 11251 - 11290.
 
 
Purge of specified Snapshot range complete.  If you wish to ROLLBACK
the purge, it is still possible to do so.  Exitting from SQL*Plus will
automatically commit the purge.
 
SQL> commit;
 
Commit complete.

 

Oracle还提供了系统脚本用于Truncate这些统计信息表,这个脚本名字是sptrunc.sql(8i和9i都相同)。该脚本主要内容如下,里面看到的就是Statspack相关的所有系统表:

truncate table STATS$FILESTATXS;
truncate table STATS$LATCH;
truncate table STATS$LATCH_CHILDREN;
truncate table STATS$LATCH_MISSES_SUMMARY;
truncate table STATS$LATCH_PARENT;
truncate table STATS$LIBRARYCACHE;
truncate table STATS$BUFFER_POOL_STATISTICS;
truncate table STATS$ROLLSTAT;
truncate table STATS$ROWCACHE_SUMMARY;
truncate table STATS$SGA;
truncate table STATS$SGASTAT;
truncate table STATS$SYSSTAT;
truncate table STATS$SESSTAT;
truncate table STATS$SYSTEM_EVENT;
truncate table STATS$SESSION_EVENT;
truncate table STATS$BG_EVENT_SUMMARY;
truncate table STATS$WAITSTAT;
truncate table STATS$ENQUEUESTAT;
truncate table STATS$SQL_SUMMARY;
truncate table STATS$SQL_STATISTICS;
truncate table STATS$SQLTEXT;
truncate table STATS$PARAMETER;
 
delete from STATS$SNAPSHOT;
delete from STATS$DATABASE_INSTANCE;
 
commit;


如果采样了大量的数据,直接Delete是非常缓慢的,可以考虑使用上述SQL截断所有表。


其他重要脚本

1.通过导出保存及共享数据

在诊断系统问题时,可能需要向专业人士提供原始数据,这时可以导出Statspack表数据,其中可能用到spuexp.par,其内容主要为:

file=spuexp.dmp log=spuexp.log compress=y grants=y indexes=y rows=y constraints=y owner=PERFSTAT consistent=y

可以导出如下:

exp userid=perfstat/my_perfstat_password parfile=spuexp.par


2.删除数据

spdrop.sql在执行时主要调用两个脚本spdtab.sql和spdusr.sql,前者删除表及同义词等数据,后者删除用户。


3.Oracle 92中新增加的脚本

用于升级statspack对象的脚本,这些脚本需要以具有SYSDBA权限的用户运行,升级前请先备份存在的Schema数据。

 ·  SPUP90.SQL:用于升级9.0版本的模式至9.2版本。

 · SPUP817.SQL:如果从Statspack 8.1.7升级,需要运行这个脚本

 · SPUP816.SQL:从Statspack 8.1.6升级,需要运行这个脚本,然后运行SPUP817.SQL.


此外,sprepsql.sql用于根据给定的SQL Hash值生成SQL报告。


调整STATSPACK的收集门限

Statspack有两种类型的收集选项:级别(level)和门限(threshold),其中级别用于控制收集数据的类型,门限用于设置收集的数据的阈值。


Statspack共有以下5类快照级别(Level),默认级别是Level 5。

 · Level 0:包含一般性能统计数据,如等待事件、系统事件、系统统计、回滚段统计、行缓存、SGA、会话、锁、缓冲池统计等。

 · Level 5:较之前级别增加了SQL语句信息,除了包括Level 0的所有内容,还包括SQL语句的收集,收集结果记录在stats$sql_summary中。

 · Level 6:从Oracle 9i开始引入,除包含Level 5的全部信息外,增加了SQL执行计划部分信息。

 · Level 7:从Oracle 9i开始引入,除包含低级别的所有信息之外,还增加段级统计信息(Segment Level Statistics)收集。

 · Level 10:增加Parent/Children Latch信息统计。这一级别包括低级别收集得所有信息,并且还会将附加的Parent/Children Latch信息记入stats$latch_children字典表中。使用这个级别收集信息时可能会耗费大量的CPU时间,所以使用时需要慎重,建议在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);


而快照门限只应用于stats$sql_summary表中获取的SQL语句。因为每一个快照都会收集很多数据,每一行都代表获取快照时数据库中的一个SQL语句,所以stats$sql_summary很快就会成为Statspack中最大的表。


门限信息存储在stats$statspack_parameter表中,各种门限说明如下。

 ·  executions_th:这是SQL语句执行的数量(默认值是100)。

 ·  disk_reads_tn:这是SQL语句执行的磁盘读入数量(默认值是1000)。

 ·  parse_calls_th:这是SQL语句执行的解析调用的数量(默认值是1000)。

 ·  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;


ORA-00001错误解决

在Statspack的不同版本中,你都可能遇到ORA-00001号错误,一般的提示信息大致是:

Sun Oct 16 00:43:39 2005
Errors in file /oracle/app/oracle/admin/shyz/bdump/shyz1_ora_2588734.trc:
ORA-12012: error on auto execute of job 328
ORA-00001: unique constraint (PERFSTAT.STATS$SQL_SUMMARY_PK) violated
ORA-06512: at "PERFSTAT.STATSPACK", line 1361
ORA-06512: at "PERFSTAT.STATSPACK", line 2471
ORA-06512: at "PERFSTAT.STATSPACK", line 91
ORA-06512: at line 1


这个错误一般是由于主键冲突导致的,通常的解决方法是可以禁用或删除唯一性约束,然后再创建一个非唯一性索引(避免查询缓慢)。

有的朋友在Oracle 10gR2里还能遇到类似以下的Bug:

Errors in file /opt/oracle/admin/forum/bdump/forum11_j000_17729.trc:
ORA-12012: error on auto execute of job 62
ORA-00001: unique constraint (PERFSTAT.STATS$MUTEX_SLEEP_PK) violated
ORA-06512: at "PERFSTAT.STATSPACK", line 5264
ORA-06512: at "PERFSTAT.STATSPACK", line 104


首先可以根据提示找到这个唯一性索引:

SQL> select index_name,column_name from dba_ind_columns where table_name = 'STATS$MUTEX_SLEEP';
INDEX_NAME             COLUMN_NAME
---------------------------------- -------------------------------
STATS$MUTEX_SLEEP_PK        SNAP_ID
STATS$MUTEX_SLEEP_PK        DBID
STATS$MUTEX_SLEEP_PK        INSTANCE_NUMBER
STATS$MUTEX_SLEEP_PK        MUTEX_TYPE
STATS$MUTEX_SLEEP_PK        LOCATION


然后可以手工禁用这个唯一性约束:

SQL> alter table STATS$MUTEX_SLEEP disable constraint STATS$MUTEX_SLEEP_pk;
Table altered.
 
SQL> select index_name,column_name from dba_ind_columns where table_name = 'STATS$MUTEX_SLEEP';
no rows selected


再手工建立一个非唯一性索引:

SQL> create index STATS$MUTEX_SLEEP_pk on
 STATS$MUTEX_SLEEP(snap_id,dbid,INSTANCE_NUMBER,MUTEX_TYPE,LOCATION) tablespace users;
Index created.


手工执行job:

SQL> exec dbms_job.run(62);
PL/SQL procedure successfully completed.
 
SQL> commit;
Commit complete.


然后可以查询这个采样表,看问题出现在哪些数据上面:

SQL> select count(*) from STATS$MUTEX_SLEEP;
COUNT(*)
----------
5
 
SQL> select * from  STATS$MUTEX_SLEEP;
SNAP_ID       DBID IN MUTEX_TYPE     LOCATION                 SLEEPS  WAIT_TIME
------- ---------- -- ------------ -------------- -----------------------------
   5298 1834086304  1 Cursor Parent  kkspsc0 [KKSPRTLOC26]         2        100
   5298 1834086304  1 Cursor Pin     kksLockDelete [KKSCHLPIN6] 1881   10384899
   5298 1834086304  1 Cursor Pin     kkslce [KKSCHLPIN2]         877    6442099
   5298 1834086304  1 Cursor Pin     kksfbc [KKSCHLFSP2]         296    1699393
   5298 1834086304  1 Cursor Pin     kksfbc [KKSCHLFSP2]        5835  116531144


注意到查询结果中的确有重复数据出现,这种情况通常是Bug导致的,可以参考Oracle的Metalink站点解决。另外也可以使用10046事件对采样进行跟踪,从跟踪文件里就能够找到存在问题的数据,进而就可以根据具体情况进行针对性问题解决:

alter session set events '10046 trace name context forever, level 12'; 
exec statspack.snap;
alter session set events '10046 trace name context off';


Oracle 10g Statspack报告的增强

从Oracle Database 10g开始,Oracle在数据库中引入了时间模型,从而可以从很多维度对时间信息进行展现,这些内容在Statspack报告中也有所体现。


最为独特的,Statspack报告可以展示数据库等待时间的柱状图信息(这个信息在Oracle 10g 10.2.0.4中才被引入到AWR报告中),通过柱状图,我们可以清晰地看到等待时间的时长分布。


比如在以下报告中,可以看到db file scattered read等待事件35.7%的等待在4ms~8ms之间,是执行较慢的:

Wait Event Histogram  DB/Inst: SPECDB/specdb  Snaps: 8-9
-> Total Waits - units: K is 1000, M is 1000000, G is 1000000000
-> % of Waits - column heading: <=1s is truly <1024ms, >1s is truly >=1024ms
-> % of Waits - value: .0 indicates value was <.05%, null is truly 0
-> Ordered by Event (idle events last)
 
                           Total ----------------- % of Waits ------------------
Event                      Waits  <1ms  <2ms  <4ms  <8ms <16ms <32ms  <=1s   >1s
-------------------------- ----- ----- ----- ----- ----- ----- ----- ----- -----
LGWR wait for redo copy      11K  94.1    .1    .1    .1   5.6    .0    .1
SQL*Net more data to clien  254K 100.0    .0    .0    .0          .0
buffer busy waits          8698   79.2   8.6   5.7   2.9   1.4    .4   1.7
buffer deadlock             630   99.7    .3
control file parallel writ  850         86.7   6.7   3.9   2.5    .2
control file sequential re 1060   97.3          .8   1.3    .6
cursor: mutex S               3  100.0
db file parallel write       98K 100.0    .0    .0
db file scattered read       42   21.4  16.7  14.3  35.7   9.5         2.4
db file sequential read     263   52.9   6.1  17.5  18.3   3.4   1.5    .4
direct path read            232  100.0
direct path write           236  100.0
enq: CF - contention          1              100.0
enq: FB - contention          6   66.7  16.7  16.7
enq: TX - contention          1              100.0
enq: TX - index contention 1233   82.7   4.7   3.6   4.9   3.6    .6
enq: TX - row lock content  169   40.2  21.3   6.5   3.6    .6  11.2  16.0    .6
latch free                  125   72.8   1.6   2.4  11.2   4.0         6.4   1.6
latch: In memory undo latc 6736   96.7    .1    .3    .1    .1    .2   1.9    .5
latch: cache buffer handle    1  100.0
latch: cache buffers chain  638   61.1   1.1   1.4   1.6   1.3   2.2  22.6   8.8
latch: cache buffers lru c    7   85.7                                14.3
latch: checkpoint queue la    6   33.3                                66.7
latch: enqueue hash chains   13  100.0
latch: library cache         94   94.7   3.2   2.1
latch: library cache pin     29  100.0
latch: messages              42   81.0  19.0
latch: object queue header    8   37.5                                50.0  12.5
latch: redo allocation       31   93.5                                 6.5
latch: redo writing          14  100.0
latch: row cache objects     17  100.0


在 Oracle 11g 中,为 ADG (Active Data Guard)使用Statspack报告进行诊断


要知道,在 Oracle 11g 中,如果要诊断 Active Dataguard 环境,是不能通过 AWR 报告取分析备库性能的。

在备库上生成的 awr 信息源自主库,而不是 Dataguard 的awr信息,但是可以通过 statspack 来采样备库信息,诊断备库的性能状况。其原理是在主库创建 Statspack 对象,然后通过网络连接,采集备库信息,存储在主库上。


以下是一个简单的步骤说明:

1.在主库创建单独的表空间

$ sqlplus / as sysdba

SQL> create tablespace statspack '/oradata/statspack1.dbf' size 1000m autoextend on maxsize unlimited;

2.创建statspack用户和对象

$ sqlplus / as sysdba

SQL> @?/rdbms/admin/spcreate.sql
SQL> @?/rdbms/admin/sbcreate.sql

3.添加备库实例

在运行sbcreate.sql脚本时,创建完对象时会自动调用/rdbms/admin/sbaddins脚本来添加备库实例:

THE INSTANCE YOU ARE GOING TO ADD MUST BE ACCESSIBLE AND OPEN READ ONLY

Do you want to continue (y/n) ?
Enter value for key: y
You entered: y

需要输入standby数据库在主库中的TNS的名称:
Enter the TNS ALIAS that connects to the standby database instance
-----------------------------------------------------------------
Make sure the alias connects to only one instance (without load balancing).
Enter value for tns_alias: stdby

需要输入standby数据库中perfstat用户的密码:
Enter the PERFSTAT user's password of the standby database
---------------------------------------------------------
Performance data will be fetched from the standby database via
database link. We will connect to user PERFSTAT.
Enter value for perfstat_password:

4.创建快照

$ sqlplus stdbyperf

SQL> exec statspack_stdby_orcl.snap;

SQL> exec statspack_stdby_orcl.snap;

格式:statspack_(db_unique_name)_(instance_name).snap

5.生成报表:

SQL> @?/rdbms/admin/sbreport.sql

在 ADG 备库的应用,让 Statspack 获得了新的生命力,虽然在 Oracle 12.2 中,备库的 AWR 功能被类似的实现了出来。



最后修改时间:2019-08-30 09:38:33
「喜欢这篇文章,您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容,转载时必须标注文章的来源(墨天轮),文章链接,文章作者等基本信息,否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容,欢迎发送邮件至:contact@modb.pro进行举报,并提供相关证据,一经查实,墨天轮将立刻删除相关内容。

评论