statspack简明使用指南

statspack简明使用指南
一.statspack的来源
Oracle Statspack 从Oracle8.1.6 开始被引入Oracle,在数据库中Statspack 的脚本位于$ORACLE_HOME/RDBMS/ADMIN 目录下，对于ORACLE8.1.6,是
以stat 开头的文件；对于ORACLE8.1.7以后,是以sp 开头的文件
在816 以前的版本使用Statspack，你需要使用statscbps.sql 脚本建立一个v$buffer_pool_statistics视图


二.安装前设置系统参数。
1. job_queue_processes
为了能够建立自动任务，执行数据收集，该参数需要大于0。
在Oracle9i 当中，可以指定范围，如 both,这样就在spfile中生效了。
SQL> alter system set job_queue_processes = 6 scope=both;

2. timed_statistics
收集操作系统的计时信息，这些信息可被用来显示时间等统计信息，该参数使收集的时间信息存储在在V$SESSTATS 和V$SYSSTATS 等动态性能视图中。

三.安装statspack需要注意的问题。
安装Statspack 需要用internal 身份登陆，或者拥有SYSDBA(connect / as sysdba)权限的用户登陆。需要在本地安装或者通过telnet 登陆到服务器。
在Oracle8.1.6 版本中运行statscre.sql;在Oracle8.1.7 版本中运行spcreate.sql。
创建存放系统信息的数据表空间的时候，创建的表空间不能太小，如果太小创建对象会失败，至少需要建立100M 表空间。
在创建过程中，如果出现了错误，，那么你可以运行spdrop.sql 脚本来删除这些对象。然后重新运行spcreate.sql来创建这些对象。

四.执行statspack.snap，记录系统当前信息，测试安装的成功性。
在执行的过程中，有可能你会得到以下错误：
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 个字有中文，就会出现此错误。

五.自动执行任务。
可以使用spauto.sql 来定义自动任务，自动收集系统信息。
spauto.sql的内容：

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

定义时间修改时间间隔：
1/24 HH 每小时一次
1/48 MI 每半小时一次
1/144 MI 每十分钟一次
1/288 MI 每五分钟一次

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

六.生成分析报告。
用spreport.sql生成report报告，可是如果中间停过机，那么你可能收到以下错误信息：
ERROR: Snapshots chosen span an instance shutdown: RESULTS ARE INVALID
一个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

八.删除历史快照。
删除stats$snapshot 数据表中的相应数据，其他表中的数据会相应的级连删除：
SQL> select max(snap_id) from stats$snapshot;
MAX(SNAP_ID)
------------
166
SQL> delete from stats$snapshot where snap_id < = 166;
你可以更改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

Oracle 还提供了系统脚本用于Truncate这些统计信息表，这个脚本名字是: sptrunc.sql

九.其他重要脚本。
1．通过导出保存及共享数据
我们可以导出Statspack 表数据，其中我们可能用到：spuexp.par，其内容主要为：
file=spuexp.dmp log=spuexp.log compress=n 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．Oracle92 中新增加的脚本
1．用于升级statspack 对象的脚本,这些脚本需要以具有SYSDBA 权限的用户运行, 升级前请先备份存在的Schema 数据:
SPUP90.SQL: 用于升级9.0 版本的模式至9.2 版本。
SPUP817.SQL: 如果从Statspack 8.1.7 升级,需要运行这个脚本
SPUP816.SQL: 从Statspack 8.1.6 升级,需要运行这个脚本，然后运行SPUP817.SQL.
2．sprepsql.sql 用于根据给定的SQL Hash 值生成SQL 报告

十.调整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 包修改缺省的级别设置，如果只是更改本次收集信息的级别的话，可以不用i_modify_parameter 参数
SQL>execute statspack.snap(i_snap_level=>0,i_modify_parameter=>’true’);
通过这样的设置，以后的收集级别都将是0 级。


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;

十一.常见等待事件说明。
主要有两种类别的等待事件，即空闲（idle）等待事件和非空闲（non-idle）等待事件。
1. db file scattered read-DB 文件分散读取,此等待事件通常与全表扫描相关联。
当全表扫描被限制在内存时，它们很少会进入连续的缓冲区内，而是分散于整个缓冲存储器中。如果这个数目很大，就表明该表找不到索引，或者只能找到有限的索引。
尽管在特定条件下执行全表扫描可能比索引扫描更有效，但如果出现这种等待时，最好检查一下这些全表扫描是否必要,
是否可以通过建立合适的索引来减少对于大表全表扫描所产生的大规模数据读取。
对于经常使用的小表，应该尽量把他们pin 在内存中，避免不必要的老化清除及重复读取。
2. db file sequential read-DB 文件顺序读取。
这一等待事件通常显示单个块的读取(通常指索引读取)。
这种等待的数目很多时，可能显示表的连接顺序不佳，或者不加选择地进行索引。
对于高级事务处理（high-transaction）、调整良好（welltuned）的系统，这一数值很大是很正常的，
但在某些情况下，它可能暗示着系统中存在问题。
你应当将这一等待统计量与Statspack 报告中的已知问题（如效率较低的SQL）联系起来。检查
索引扫描，以保证每个扫描都是必要的，并检查多表连接的连接顺序。DB_CACHE_SIZE 也是这些等
待出现频率的决定因素。有问题的散列区域（Hash-area）连接应当出现在PGA 内存中，但它们也会
消耗大量内存，从而在顺序读取时导致大量等待。它们也可能以直接路径读／写等待的形式出现。
3. Free Buffer Wait-释放缓冲区
这种等待表明系统正在等待内存中的缓冲，因为内存中已经没有可用的缓冲空间了。如果所有
SQL 都得到了调优，这种等待可能表示你需要增大DB_BUFFER_CACHE。释放缓冲区等待也可能表
示不加选择的SQL 导致数据溢出了带有索引块的缓冲存储器，没有为等待系统处理的特定语句留有缓冲区。
这种情况通常表示正在执行相当多数量的DML（插入／更新／删除），并且可能说明DBWR 写
的速度不够快，缓冲存储器可能充满了相同缓冲器的多个版本，从而导致效率非常低。为了解决这个
问题，可能需要考虑增加检查点、利用更多的DBWR 进程，或者增加物理磁盘的数量。
4. Buffer Busy Wait-缓冲区忙
该等待事件表示正在等待一个以unshareable方式使用的缓冲区，或者表示当前正在被读入buffer cache。一般来说Buffer Busy Wait不应大于1%。
检查缓冲等待统计部分（或V$WAITSTAT），看一下等待是否位于段头。如果是，可以考虑增加
自由列表（freelist，对于Oracle8i DMT）或者增加freelist groups.其修改语法为：SQL> alter table sp_item storage (freelists 2);
如果这一等待位于undo header，可以通过增加回滚段（rollback segment）来解决缓冲区的问题。
如果等待位于undo block上，我们可能需要检查相关应用，适当减少大规模的一致性读取，或者降低一致性读取(consistent read)的表中的数据密度或者增大DB_CACHE_SIZE。
如果等待处于data block，可以考虑将频繁并发访问的表或数据移到另一数据块或者进行更大范围的
分布（可以增加pctfree 值 ，扩大数据分布，减少竞争），以避开这个"热点"数据块，或者可以考虑增加表
中的自由列表或使用本地化管理的表空间（Locally Managed Tablespaces）。
如果等待处于索引块，应该考虑重建索引、分割索引或使用反向键索引。
为了防止与数据块相关的缓冲忙等待，也可以使用较小的块：在这种情况下，单个块中的记录就较少，
所以这个块就不是那么"繁忙"；或者可以设置更大的pctfree,使数据扩大物理分布，减少记录间的热点竞争。
在执行DML (insert/update/ delete)时，Oracle向数据块中写入信息，对于多事务并发访问的数据表，关
于ITL的竞争和等待可能出现，为了减少这个等待，可以增加initrans，使用多个ITL槽。

[@more@]

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/9721529/viewspace-916141/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/9721529/viewspace-916141/