oracle Statspack

1、设置系统参数

 

sys@ORCL>alter system set job_queue_processes=1000 scope=both;
系统已更改。

sys@ORCL>show parameter job_queue_processes

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
job_queue_processes                  integer     1000

job_queue_processes > 0，该参数可以在参数文件中修改（重启后依然有效），也可以在系统级动态修改（重启失效）

 

检查timed_statistics是否为true:

 

sys@ORCL>show parameter timed_statistics
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
timed_statistics                     boolean     TRUE

如果timed_statistics为false，可以通过以下方式更改:

 

alter system set timed_statistics=true scope=both;

2、安装statspack

因为statspack需要一定的存储空间，所以最好建立独立的表空间，创建的表空间不能太小，至少100M，否则创建 对象会失败。

create tablespace statperf
datafile 'D:\app\Administrator\product\11.2.0\statperf.dbf' size 300M
extent management local;

查看脚本创建文件
D:\app\Administrator\product\11.2.0\dbhome_1\RDBMS\ADMIN\sp*

 

 

 

执行脚本：

 

 

 

 

@D:\app\Administrator\product\11.2.0\dbhome_1\RDBMS\ADMIN\spcreate;

 

以此输入

statperf_pwd

statperf

temp

若执行失败 则需要删除 重试：

 

drop user  PERFSTAT cascade;
drop tablespace statperf including contents and datafiles cascade constraint;

删除全部的STATS 开头的同义词：

 

begin
for i in
(select  synonym_name from all_synonyms where synonym_name like 'STATS%')  
	loop
		execute immediate 'drop public synonym '||i.synonym_name;
	end loop;
end;

 

3、测试statspack

运行statspack.snap 可以产生系统快照，运行两次，然后执行spreport.sql就可以产生一个基于两个时间点的报告。如果正常，说明安装成功。

Sql>execute statspack.snap
Pl/sql 过程已成功完成

Sql>execute statspack.snap
Pl/sql 过程已成功完成

#查看报告
@D:\app\Administrator\product\11.2.0\dbhome_1\RDBMS\ADMIN\spreport.sql;
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
输入 begin_snap 的值:1
Begin Snapshot Id specified: 1

输入 end_snap 的值:  5
End   Snapshot Id specified: 2
…..

输入 report_name 的值:  d:\11.txt
…..（省略部分结果）

直接查看 d:\\11.txt 文件即可。

快照id 查询：

scott@ORCL>select snap_id,to_char(snap_time,'yyyy-mm-dd hh24:mi:ss') time
  2  from stats$snapshot
  3  order by snap_time desc;

   SNAP_ID TIME
---------- -------------------
         2 2018-05-30 17:14:06
         1 2018-05-30 17:12:41

已选择2行。

 

删除历史 快照id ：

 

system@ORCL> delete from perfstat.stats$snapshot ;

已删除155行。

删除报告中使用的数据和表：

 

@D:\app\Administrator\product\11.2.0\dbhome_1\RDBMS\ADMIN\spdrop.sql

connect stdbyperf/statperf_pwd

 

4.job方式

 

sys@ORCL>@D:\app\Administrator\product\11.2.0\dbhome_1\RDBMS\ADMIN\spauto.sql
PL/SQL 过程已成功完成。

Job number for automated statistics collection for this instance
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Note that this job number is needed when modifying or removing
the job:

     JOBNO
----------
        23

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:

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
job_queue_processes                  integer     1000

Next scheduled run
~~~~~~~~~~~~~~~~~~
The next scheduled run for this job is:

       JOB NEXT_DATE      NEXT_SEC
---------- -------------- ----------------
        23 24-4月 -18     17:00:00

 

测试：

Declare
  N1 number;
Begin
  Dbms_job.submit(n1, 'update scott.emp set sal=sal+1;', sysdate + 1/24/60, 'sysdate + 1/24/120' );
Commit;
End;
/

移除定时任务

 

select job,log_user,priv_user,last_date,next_date,interval from user_jobs;
execute dbms_job.remove('43');

如果采样完成，应该及时移除job任务，不然statspack长时间运行，数据量将非常大，因此会引起宕机。