【STATSPACK】Statspack安装、测试与使用

Statspack脚本存放目录:
$oracle_HOME/RDBMS/ADMIN
 
1.需要设置的参数:
1).job_queue_processes
sql> alter system set job_queue_processes = 6;
SQL> alter system set job_queue_processes = 6 scope=both;
为了能够建立自动任务,执行数据收集,该参数需要大于0.
你可以在初试化参数文件中修改该参数(使该参数在重起后以然有效).
该参数可以在系统级动态修改(重起后失效).

2).timed_statistics
SQL> alter system set timed_statistics = true;
收集操作系统的计时信息,这些信息可被用来显示时间等统计信息、优化数据库和 SQL 语句.
false-防止因从操作系统请求时间而引起的开销
true-可使statspack收集统计信息,否则收集的统计信息大约只能起到10%的作用.可以在使用statspack之前在system更改,采样过后把该参数动态修改成false.亦可一致打开利大于弊.
该参数使收集的时间信息存储在在V$SESSTATS和V$SYSSTATS等动态性能视图中.
 
2.安装statspack
1).以internal身份或具有SYSDBA权限的用户登陆
cd $ORACLE_HOME/RDBMS/ADMIN
sqlplus / as sysdba
2).检查数据文件路径及磁盘空间,以决定创建数据文件的位置,建立一个100M以上的表空间.
SQL> select file_name from dba_data_files;
SQL> create tablespace perfstat datafile 'd:\oracle\oradata\eygle\perfstat.dbf' size 500M extent management local;
3).执行创建脚本,创建过程中会提示输入default_tablespace和temporary_tablespace的内容,若有错误可以查看相应生成的.lis文件.
SQL> @spcreate
若需要重建,可以运行spdrop.sql脚本来删除这些对象.然后重新运行spcreate.sql
SQL> @spdrop.sql
SQL> @spcreate

3.测试Statspack的可用性
运行statspack.snap可以产生系统快照,运行两次,然后执行spreport.sql就可以生成一个基于两个时间点的报告.
如果一切正常,说明安装成功.
SQL>execute statspack.snap
SQL>execute statspack.snap
SQL>@spreport.sql
 
4.设置定时任务
SQL> @spauto
脚本中有关时间间隔的设置内容,可以修改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每五分钟一次

5.移除定时任务
SQL> select job,log_user,priv_user,last_date,next_date,interval from user_jobs;
SQL> execute dbms_job.remove('28')

6.生成分析报告
SQL> @spreport

7.删除历史数据
第一种方法:删除stats$snapshot数据表中的相应数据,其他表中的数据会相应的级连删除:
SQL> select max(snap_id) from stats$snapshot;
SQL> delete from stats$snapshot where snap_id <= 166;
第二种方法:使用自带的脚本sptrunc.sql
SQL> @sptrunc

8.使用spuexp.par文件exp保存相应数据
spuexp.par内容:
file=spuexp.dmp log=spuexp.log compress=y grants=y indexes=y rows=y constraints=y wner=PERFSTAT consistent=y
导出语句:
exp userid=perfstat/perfstat parfile=spuexp.par

9.使用sprepsql.sql根据给定的SQL Hash值生成SQL报告
SQL> @sprepsql

10.调整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’);
如果你只是想本次改变收集级别,可以忽略i_modify_parameter参数.
SQL> execute statspack.snap(i_snap_level=>0);
2).快照门限
快照门限只应用于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);

11.Statspack报告重要内容
1)."Execute to Parse %" 执行分析比率
= 100 * (1 - Parses/Executions)
Parses = "parse count (total) "
Executions = "execute count"
当Parses > Executions时,就会出现比率小于0的情况.
该值<0通常说明shared pool设置或效率存在问题
造成反复解析,reparse可能较严重,或者可能与snapshot有关
如果该值为负值或者极低,通常说明数据库性能存在问题

2)."Parse CPU to Parse Elapsd % "
= 100*（"parse time cpu" / "parse time elapsed"）= "Parse CPU to Parse Elapsd %"

3)."Rollback per transaction %" 平均事务回滚率
= Round("user rollbacks" / ("user commits" + "user rollbacks") ,4)* 100%
如果回滚率过高,可能说明你的数据库经历了太多的无效操作
过多的回滚可能还会带来Undo Block的竞争

4).db file scattered read DB 文件分散读（全表扫）
这种情况通常显示与全表扫描相关的等待.当数据库进行全表扫时,基于性能的考虑,数据会分散(scattered)读入Buffer Cache.如果这个等待事件比较显著,可能说明对于某些全表扫描的表,没有创建索引或者没有创建合适的索引,我们可能需要检查这些数据表已确定是否进行了正确的设置.
然而这个等待事件不一定意味着性能低下,在某些条件下Oracle会主动使用全表扫描来替换索引扫描以提高性能,这和访问的数据量有关,在CBO下Oracle会进行更为智能的选择,在RBO下Oracle更倾向于使用索引.
因为全表扫描被置于LRU（Least Recently Used,最近最少使用）列表的冷端（cold end）,对于频繁访问的较小的数据表,可以选择把他们Cache到内存中,以避免反复读取.
当这个等待事件比较显著时,可以结合v$session_lonGops动态性能视图来进行诊断,该视图中记录了长时间（运行时间超过6秒的）运行的事物,可能很多是全表扫描操作（不管怎样,这部分信息都是值得我们注意的）.

5).Enqueue
enqueue是一种?す蚕碜试吹乃?ɑ??该锁定机制?す蚕碜试?如记录中的数据,以避免两个session在同一时间更新同一数据.enqueue包括一个排队机制,即FIFO（先进先出）排队机制.
Enqueue等待常见的有ST、HW 、TX 、TM等
(1).ST enqueue,用于空间管理和字典管理的表空间(DMT)的区间分配,在DMT中典型的是对于uet$和fet$数据字典表的争用.对于支持LMT的版本,应该尽量使用本地管理表空间. 或者考虑手工预分配一定数量的区(Extent),减少动态扩展时发生的严重队列竞争.
(2).HW enqueue指和段的高水位标记相关等待;手动分配适当区可以避免这一等待.
(3).TX是最常见的enqueue等待.TX enqueue等待通常是以下三个问题之一产生的结果.
第一个问题是唯一索引中的重复索引,你需要执行提交（commit）/回滚（rollback）操作来释放enqueue.
第二个问题是对同一位图索引段的多次更新.因为单个位图段可能包含多个行地址（rowid）,所以当多个用户试图更新同一段时,可能一个用户会锁定其他用户请求的记录,这时等待出现.直到获得锁定的用户提交或回滚,enqueue释放.
第三个问题,也是最可能发生的问题是多个用户同时更新同一个块.如果没有足够的ITL槽,就会发生块级锁定.通过增大initrans和/或
maxtrans以允许使用多个ITL槽（对于频繁并发进行DML操作的数据表,在建表之初就应该考虑为相应参数设置合理的数值,避免系统运行
以后在线的更改,在8i之前,freelists等参数不能在线更改,设计时的考虑就尤为重要）,或者增大表上的pctfree值,就可以很容易的避免这种情况.
(4).TM enqueue队列锁在进行DML操作前获得,以阻止对正在操作的数据表进行任何DDL操作(在DML操作一个数据表时,其结构不能被更改).

6).log file sync  等待事件
当一个用户提交(commits)或者回滚(rollback),session的redo信息需要写出到redo logfile中.
用户进程将通知LGWR执行写出操作,LGWR完成任务以后会通知用户进程.
这个等待事件就是指用户进程等待LGWR的写完成通知.
对于回滚操作,该事件记录从用户发出rollback命令到回滚完成的时间.
如果该等待过多,可能说明LGWR的写出效率低下,或者系统提交过于频繁.
针对该问题,可以关注:
log file parallel write等待事件
user commits,user rollback等统计信息可以用于观察提交或回滚次数
解决方案:
a.提高LGWR性能
尽量使用快速磁盘,不要把redo log file存放在raid 5的磁盘上
b.使用批量提交
c.适当使用NOLOGGING/UNRECOVERABLE等选项
可以通过如下公式计算平均redo写大??
avg.redo write size = (Redo block written/redo writes)*512 bytes
如果系统产生redo很多,而每次写的较少,一般说明LGWR被过于频繁的激活了.
可能导致过多的redo相关latch的竞争,而且Oracle可能无法有效的使用piggyback的功能.

Good luck.

secooler

-- The End --