report1文档通过修改值设置采集时间

一.查看产生的report1文档

C:\oracle\ora92\bin\report1.txt

二.自动执行STATSPACK收集统计信息

SQL>@C:\oracle\ora92\rdbms\admin\spauto.sql;

Job number for automated statistics collection for this instance

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

Note that this job number is needed when modifying or removing

the job:

JOBNO

----------

1

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_COL_PLUS_SHOW_PARAM

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

TYPE

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

VALUE_COL_PLUS_SHOW_PARAM

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

job_queue_processes

integer

6

Next scheduled run

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

The next scheduled run for this job is:

JOB NEXT_DATE

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

NEXT_SEC

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

1 11-10月-06

23:00:00

spauto.sql中主要调用dbms_job.submit，默认每小时收集1次（1/24）

variable jobno number;

variable instno number;

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 每五分钟一次

三. 移除自动执行STATSPACK收集

SQL>select job,log_user,priv_user,last_date,next_date,interval from user_jobs; --先查看当前自动收集的jobs

SQL> execute dbms_job.remove('1'); --移除任务1 oracle 教程