statspck功能笔记

1 安装statspack
创建表空间
CREATE TABLESPACE perfstat_tbs DATAFILE '/u02/oradata/test/perfstat_tbs01.dbf' SIZE 50M AUTOEXTEND ON NEXT 1M MAXSIZE 1000M
    EXTENT MANAGEMENT LOCAL AUTOALLOCATE;

运行脚本
?/rdbms/admin/spcreate.sql

2 设置定时运行快照
--batch auto run statspack
?/rdbms/admin/spauto.sql

variable jobno number;
variable instno number;
begin
  select instance_number into :instno from v$instance;
  dbms_job.submit(:jobno, 'statspack.snap;', trunc(sysdate+1/48,'MI'), 'trunc(SY
SDATE+1/48,''MI'')', TRUE, :instno);
  commit;
end;
/

SQL> select job,interval from dba_jobs;
       JOB INTERVAL
---------- ------------------------------
         2 trunc(SYSDATE+1/48,'MI')

SYSDATE+1/24  每隔一个小时
SYSDATE+1/48  每隔半个小时

3 运行快照

SQL> exec statspack.snap;

PL/SQL procedure successfully completed.

--remove history data
SQL> exec statspack.snap;

PL/SQL procedure successfully completed.

4 建立baseline
exec statspack.make_baseline(1,3);
exec statspack.clear_baseline(1,3);

5 更改统计信息的level
-- set event gather level
level 0: 一般性能统计信息
level 5: 增加SQL语句
level 10: 增加子锁存信息
exec statspack.snap(i_snap_level => 10, i_modify_parameter=>'true');
exec statspack.snap(i_snap_level => 10);
exec statspack.modify_statspack_parameter(I_SNAP_LEVEL => 5, I_BUFFER_GETS_TH=>10000);

select * from stats$statspack_parameter;

--threshold
a. executions_th (100)
b. disk_reads_tn (1000)
c. parse_calls_th (1000)
d. buffer_gets.th (10000)

exec statspack.snap(i_executions_th=> 200, i_modify_parameter=>'true');

NOTE:
自动运行statspack，需要设置job_queue_processes>0

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

转载于:http://blog.itpub.net/27143/viewspace-673222/