一、设定自动执行
修改spauto.sql脚本来设定自动执行任务。
脚本主要内容如下：
spool spauto.lis
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;
/
select job, next_date, next_sec
from user_jobs
where job = :jobno;
spool off;
主要是修改执行job中的间隔时间，默认的是每个小时进行执行一次采样，按照实际需要进行采样间隔时间的设置。对于一些特殊的环境可以采用每30分钟采样，但是不推荐更短的间隔时间，因为statspack执行本身需要消耗资源，采样间隔太短会对系统性能造成比较大的影响。以下是执行脚本：
SQL> @E:\oracle\product\10.2.0\db_1\RDBMS\ADMIN\spauto.sql;
PL/SQL procedure successfully completed
instno
---------
1
jobno
---------
21
Job number for automated statistics collection for this instance
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Note that this job number is needed when modifying or removing
the job:
jobno
---------
21
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 10
Next scheduled run
~~~~~~~~~~~~~~~~~~
The next scheduled run for this job is:
JOB NEXT_DATE NEXT_SEC
---------- ----------- ----------------
21 2009-2-22 1 12:00:00
jobno
---------
21
二、取消自动执行计划
取消自动执行计划只需要移除已建的job即可。
SQL> select job,last_date,next_date,interval from user_jobs where log_user='PERFSTAT';
JOB LAST_DATE NEXT_DATE INTERVAL
---------- -------------- -------------- ----------------------------
23 2009-2-22 trunc(SYSDATE+1/24,'HH')
SQL> execute dbms_job.remove('23');
PL/SQL procedure successfully completed
注意在完成采样报告之后一定要记得取消执行计划，否则Statspack执行一周的数据量是惊人的，严重的会造成宕机。
三、生成分析报告
使用spreport.sql脚本来生成分析报告。
SQL> @E:\oracle\product\10.2.0\db_1\RDBMS\ADMIN\spreport.sql;
... ...
Enter value for begin_snap：1
Enter value for end_snap：3
Enter value for report_name：test
... ...
会在当前目录下生成test.lst的report文档
注意：选取的两个snap不能跨越一次停机，否则报错：
ORA-20200: The instance was shutdown between snapshots 1 and 21
ORA-06512: 在 line 49
四、删除历史数据
在stats$snapshot表中查找所有的snap序号：
SQL> select max(snap_id) from stats$snapshot;
MAX(SNAP_ID)
------------
21
在stats$snapshot表中删除数据后，所有生成的数据会相应得自动删除完成。
SQL> delete from stats$snapshot where snap_id <= 21;
8 rows deleted.
另外oracle还提供了一个sptrunc.sql的脚本，用以清空statspack相关的所有系统表：
truncate table STATS$FILESTATXS;
truncate table STATS$TEMPSTATXS;
truncate table STATS$LATCH;
truncate table STATS$LATCH_CHILDREN;
truncate table STATS$LATCH_MISSES_SUMMARY;
truncate table STATS$LATCH_PARENT;
truncate table STATS$LIBRARYCACHE;
truncate table STATS$BUFFER_POOL_STATISTICS;
truncate table STATS$ROLLSTAT;
truncate table STATS$ROWCACHE_SUMMARY;
truncate table STATS$SGA;
truncate table STATS$SGASTAT;
truncate table STATS$SYSSTAT;
truncate table STATS$SESSTAT;
truncate table STATS$SYSTEM_EVENT;
truncate table STATS$SESSION_EVENT;
truncate table STATS$BG_EVENT_SUMMARY;
truncate table STATS$WAITSTAT;
truncate table STATS$ENQUEUE_STATISTICS;
truncate table STATS$SQL_SUMMARY;
truncate table STATS$SQL_STATISTICS;
truncate table STATS$SQLTEXT;
truncate table STATS$PARAMETER;
truncate table STATS$RESOURCE_LIMIT;
truncate table STATS$DLM_MISC;
truncate table STATS$UNDOSTAT;
truncate table STATS$SQL_PLAN;
truncate table STATS$SQL_PLAN_USAGE;
truncate table STATS$SEG_STAT;
truncate table STATS$SEG_STAT_OBJ;
truncate table STATS$DB_CACHE_ADVICE;
truncate table STATS$PGASTAT;
truncate table STATS$INSTANCE_RECOVERY;
truncate table STATS$JAVA_POOL_ADVICE;
truncate table STATS$THREAD;
truncate table STATS$CR_BLOCK_SERVER;
truncate table STATS$CURRENT_BLOCK_SERVER;
truncate table STATS$INSTANCE_CACHE_TRANSFER;
truncate table STATS$FILE_HISTOGRAM;
truncate table STATS$TEMP_HISTOGRAM;
truncate table STATS$EVENT_HISTOGRAM;
truncate table STATS$TIME_MODEL_STATNAME;
truncate table STATS$SYS_TIME_MODEL;
truncate table STATS$SESS_TIME_MODEL;
truncate table STATS$STREAMS_CAPTURE;
truncate table STATS$STREAMS_APPLY_SUM;
truncate table STATS$PROPAGATION_SENDER;
truncate table STATS$PROPAGATION_RECEIVER;
truncate table STATS$BUFFERED_QUEUES;
truncate table STATS$BUFFERED_SUBSCRIBERS;
truncate table STATS$RULE_SET;
truncate table STATS$OSSTAT;
truncate table STATS$OSSTATNAME;
truncate table STATS$PROCESS_ROLLUP;
truncate table STATS$PROCESS_MEMORY_ROLLUP;
truncate table STATS$STREAMS_POOL_ADVICE;
truncate table STATS$SGA_TARGET_ADVICE;
truncate table STATS$MUTEX_SLEEP;
truncate table STATS$DYNAMIC_REMASTER_STATS;
delete from STATS$SNAPSHOT;
delete from STATS$DATABASE_INSTANCE;
commit;
当存在大量的数据时，使用delete是比较慢的，可以尝试使用sptrunc.sql脚本
-The End-