Oracle_Statspack性能诊断工具

Statspack是oracle强大的状态收集及性能诊断工具，是DBA及数据库相关人员进行问题诊断的重要手段，是DBAD的一大利器
1、系统参数设置
先查询系统参数

show parameter job_queue_processes
show parameter timed_statistics

如果值分别为0和false，则做如下更改

alter system set job_queue_processes = 10;
alter system set timed_statistics = true;

timed_statistics设置为true对系统的性能有一定的影响，再数据采样结束后，把该值设置为false,该参数收集的时间信息存储在v$sesstats和v$sysstats等动态性能视图中

2、安装Statspack

查询数据库实例名和版本
select instance_name,host_name,version,startup_time from v$instance;

查询物理文件名及路径
select file_name from dba_data_files;

//创建表空间
create tablespace Statspack datafile 'E:/ORACLE/ORADATA/DB2/Statspack01.dbf' size 500M

安装
@spcreate
按照提示输入表空间，密码，临时表空间等，如果出现错误会生成一个spcpkg.lis错误文件，打开该文件可以看到详细的错误信息，并重新运行@spdrop.sql来删除刚才创建的对象,有时候运行@spcreate会提示找不到文件，可以输入完整路径，如：@E:/oracle/ora90/rdbms/admin/spcreate.sql
至此，Statspack就安装完成了

3、测试安装好的 Statspack

执行stataspack.snap可以产生系统快照，运行两次，然后执行@spreport.sql就可以生成一个基于两个时间点的报告
execute statspack.snap

隔一段时间再运行一次
execute statspack.snap

运行@spreport.sql就可以生成这段时间内的报告

statspack收集数据的级别，statspack一共有三个级别，默认值是5
level0：一般性能统计。包括等待事件、系统事件、系统统计、回滚段统计、行缓存、SGA、会话、锁、缓冲统计等
level5：增加sql语句
level10：增加字锁统计

收集数据级别
可通过以下语句指定收集级别
execute statspack.snap(i_snap_level=>0,i_modify_parameter=>'true');
如果只是想本次改变收集级别，可以省略i_modify_parameter=>'true'
execute statspack.snap(i_snap_level=>0）；

"=>"是等于的意思，而非大于等于0级别的意思

快照门限
execute_th         SQL语句执行的数量，默认是100
disk_reads_th      SQL语句执行的磁盘读入数量，默认是1000
parse_calls_th     SQL语句执行的解析调用的数量，默认是1000
buffer_gets_th     SQL语句执行的缓冲区获取的数量，默认是10000

例如：
execute statspack.modify_statspack_parameter(i_buffer_gets_th=>100000,i_disk_reads_th=>10000);

4、规划自动任务
可用@spauto.sql来定义自动任务
这个job里定义了收集数据的时间间隔
begin
  select instance_number into :instno from v$instance;
  dbms_job.submit(:jobno, 'statspack.snap;', trunc(sysdate+1/24,'HH'), 'trunc(SYSDATE+1/24,''HH'')', TRUE, :instno);
  commit;
end;

1/24  每小时运行一次    格式：HH
1/48  每半小时运行一次  格式：MI
1/144 每十分钟运行一次  格式：MI
1/288 每五分钟运行一次  格式：MI

执行后可用下列语句查看任务运行的时间
select job, next_date, next_sec from user_jobs where job = :jobno;

5、移除定时任务
execute dbms_job.remove('1');
其中1为作业队列号

6、删除历史数据
删除stats$snapshot表中的相应数据，其他表中的数据也会相应的级连删除
select max(snap_id) from stats$snapshot;
delete from stats$snapshot where snap_id <= 4;
可以更改snap_id的范围保留需要的数据

也可以用@sptrunc.sql脚本删除所有的数据