Statspack基本使用
参考手册：$ORACLE_HOME/rdbms/admin/spdoc.txt
一、设置参数
1.1、job_queue_processes
SQL> alter system set job_queue_processes=20 scope=both;
系统已更改。 
 
1.2、timed_statistics
SQL> alter system set timed_statistics=true scope=both;
系统已更改。 
说明：采样时设置成TRUE 不采样时设置成 FALSE
 
二、安装statspack
2.1、创建表空间
SQL> 
create tablespace perfstat 
datafile '/home/oracle/oradata/moree/perfstat.dbf' 
size 100M 
autoextend on;
表空间已创建。 
 
2.2、运行脚本安装statspack
SQL>@$ORACLE_HOME/rdbms/admin/spcreate.sql (可以用‘？’代替$ORACLE_HOME， 即： SQL>@？/rdbms/admin/spcreate.sql)
 
……
... Creating PERFSTAT user ...
Choose the PERFSTAT user's password.
Not specifying a password will result in the installation FAILING
Specify PERFSTAT password
输入 perfstat_password 的值: perfstat
Perfstat
……
……
Specify PERFSTAT user's default tablespace
输入 default_tablespace 的值: perfstat
……
……
 
Specify PERFSTAT user's temporary tablespace.
 
输入 temporary_tablespace 的值: temp
 
….. 
 
2.3、测试安装好的statspack
SQL> execute statspack.snap
PL/SQL 过程已成功完成。
SQL> execute statspack.snap
PL/SQL 过程已成功完成。
SQL> @$ORACLE_HOME/rdbms/admin/spreport.sql
Specify the Begin and End Snapshot Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
输入 begin_snap 的值:1
Begin Snapshot Id specified: 1
输入 end_snap 的值: 2
Specify the Report Name
~~~~~~~~~~~~~~~~~~~~~~~
The default report file name is sp_1_2. To use this name,
press <return> to continue, otherwise enter an alternative.
输入 report_name 的值: /home/oracle/report.txt
 
2.4、删除安装后的statspack
运行脚本
SQL>@$ORACLE_HOME/rdbms/admin/spdrop.sql
 
 
三、自动任务
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/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: NAMETYPEVALUE ------------------------------------ ----------- ------------------------------ job_queue_processesinteger 5 Next scheduled run ~~~~~~~~~~~~~~~~~~ The next scheduled run for this job is: JOB NEXT_DATE NEXT_SEC ---------- --------- ---------------- 28 15-AUG-0316:00:00 关于采样间隔，我们通常建议以1小时为时间间隔，对于有特殊需要的环境，可以设置更短的，如半小时作为采样间隔， 但是不推荐更短。因为statspack的执行本身需要消耗资源，对于繁忙的生产系统，太短的采样对系统的性能会产生较 大的影响（甚至会使statspack的执行出现在采样数据中）。
 
  
 
  

    四、生成分析报告 
  
 
  

    运行：SQL>$ORACLE_HOME\rdbms\admin\spreport.sql 
  
 
  

      
  
 
  

    说明：中间停机，数据将是不准确的 
  
 
  

      
  
 
  

    SQL> @spreport.sql 
  
 
  

      
  
 
  

    Specify the Begin and End Snapshot Ids 
  
 
  

      
  
 
  

    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ 
  
 
  

    输入 begin_snap 的值:1 
  
 
  

      
  
 
  

    Begin Snapshot Id specified: 1 
  
 
  

      
  
 
  

    输入 end_snap 的值: 2 
  
 
  

      
  
 
  

    Specify the Report Name 
  
 
  

      
  
 
  

    ~~~~~~~~~~~~~~~~~~~~~~~ 
  
 
  

      
  
 
  

    The default report file name is sp_1_2. To use this name, 
  
 
  

      
  
 
  

    press <return> to continue, otherwise enter an alternative. 
  
 
  

      
  
 
  

    输入 report_name 的值: c:report.txt  
  
 
  

      
  
 
  

    五、及时移除JOB 
  
 
  

    完成分析采样后，应该及时删除JOB 
  
 
  

      
  
 
  

    select job,log_user,priv_user,what from user_jobs; 
  
 
  

      
  
 
  

    找到jobno 
  
 
  

      
  
 
  

    Execute dbms_remove(jobno) 
  
 
  

      
  
 
  

    六、删除历史分析数据 
  
 
  

    方法1：执行DELETE 
  
 
  

      
  
 
  

    SQL> select max(snap_id) from stats$snapshot; 
  
 
  

      
  
 
  

    MAX(SNAP_ID) 
  
 
  

      
  
 
  

    ------------ 
  
 
  

      
  
 
  

    5 
  
 
  

    SQL> delete from stats$snapshot where snap_id <=5 ; 
  
 
  

    已删除5行。 
  
 
  

      
  
 
  

    SQL> commit; 
  
 
  

      
  
 
  

    提交完成。 
  
 
  

      
  
 
  

    SQL> select max(snap_id) from stats$snapshot; 
  
 
  

      
  
 
  

      
  
 
  

    MAX(SNAP_ID) 
  
 
  

      
  
 
  

    ------------ 
  
 
  

      
  
 
  

    方法2：执行$ORACLE_HOMErdbmsadminsptrunc.sql 
  
 
  

      
  
 
  

    一、导出分析数据 
  
 
  

      
  
 
  

    把perfstat用户的数据 
  
 
  

      
  
 
  

    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/perfstat parfile=Spuexp.par 
  
 
  

      
  
 
  

    七、调整statspack的收集门限 
  
 
  

    两种收集选项：级别(level) 控制收集数据的类型 
  
 
  

      
  
 
  

    门限(threshold) 控制收集数据的阈值 
  
 
  

      
  
 
  

    7.1、级别 
  
 
  

    三种快照级别(默认level5) 
  
 
  

      
  
 
  

    (1) level0 一般性能统计。等待事件、系统事件、系统统计、回滚段统计、行缓存、SGA、会话、锁、缓冲池 
  
 
  

      
  
 
  

    (2)level5 包括level0的所用内容。增加了SQL语句的收集，收集的结果放到stat$sql_summary中 
  
 
  

      
  
 
  

    (3)level10 包括level5的所用内容。增加了子锁存统计，，收集的结果放到stat$lachc_children中 
  
 
  

      
  
 
  

    修改缺省的级别设置： 
  
 
  

      
  
 
  

      
  
 
  

    SQL> Execute statspack.snap(i_snap_level=>0,i_modify_parameter=>'true'); 
  
 
  

      
  
 
  

      
  
 
  

    PL/SQL 过程已成功完成。 
  
 
  

      
  
 
  

      
  
 
  

    i_modify_parameter参数说明以后的收集级别为level0 
  
 
  

      
  
 
  

    忽略i_modify_parameter说明此次的收集级别为level0 
  
 
  

      
  
 
  

      
  
 
  

    SQL> Execute statspack.snap(i_snap_level=>0,i_modify_parameter=>'false'); 
  
 
  

      
  
 
  

      
  
 
  

    PL/SQL 过程已成功完成。 
  
 
  

      
  
 
  

      
  
 
  

    SQL> Execute statspack.snap(i_snap_level=>10); 
  
 
  

      
  
 
  

      
  
 
  

    PL/SQL 过程已成功完成。 
  
 
  

      
  
 
  

      
  
 
  

      
  
 
  

    7.2、门限 
  
 
  

    只用于stat$sql_summary表中获取SQL语句。每一行就对应数据库中的一条SQL语句，所以stat$sql_summary是快照中的最大的表。门限参数存储在stat$statspack_parameter表中，有四种门限 
  
 
  

      
  
 
  

    (1) execution_th SQL语句执行的数量 缺省100条 
  
 
  

      
  
 
  

    (2) disk_reads_th SQL语句执行磁盘读入数量 缺省1000blocks 
  
 
  

      
  
 
  

    (3) parse_calls_th SQL语句解析调用的数量 缺省1000 
  
 
  

      
  
 
  

    (4) buffer_gets_th SQL语句执行时获取缓冲区的数量 缺省1000 
  
 
  

      
  
 
  

      
  
 
  

    说明：超过以上门限值，快照将产生一条记录 
  
 
  

      
  
 
  

      
  
 
  

    修改缺省的门限设置：使用statspack.modify_statspack_parameter 
  
 
  

      
  
 
  

    SQL> Execute statspack.modify_statspack_parameter(i_disk_reads_th=>10000); 
  
 
  

      
  
 
  

      
  
 
  

    PL/SQL 过程已成功完成。