statspack

一、安装perfstat schema

  1、创建表空间

   cd  /u01/app/oracle/product/10.2.0/db_1/rdbms/admin

   SQL> create tablespace perfstat datafile '/u01/app/oracle/oradata/rac1/perfstat01.dbf' size 200M autoextend off;

  2、执行脚本

SQL> @spcreate.sql

如果报错，则执行

SQL>@spdrop.sql

 

3、执行快照
SQL> conn perfstat/perfstat
Connected.
SQL> execute statspack.snap

 

4、查看

select * from stats$statspack_parameter;

select * from stats$snapshot;

 

 5、自动创建报告

SQL> variable jobno number;
SQL> variable instno number;
SQL> 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;
   /

SQL> select * from user_jobs;

SQL> execute dbms_job.interval(21,'sysdate+1/(24*60)'); -----1分钟执行一次

 

 6、report

    instance report

        Spreport.sql

        Sprepins.sql (multi-instance)

    sql report

          Sprepsql.sql

          Sprsqins.sql (multi-instance)

 

7、生成instance report报告

SQL> @spreport.sql

 

8、与之相关的脚本

sprepins.sql

 sprepcon.sql

 

9、生成sql report

@sprepsql.sql

哈希值来自于instance报告