ORACLE配置STATSPACK步骤

1.SQL*PLUS中输入
SQL> connect sys/sys as sysdba;
SQL> alter system set job_queue_processes = 6; --自动执行数据收集时该参数需要大于0
System altered
SQL> alter system set timed_statistics = true;  
System altered
--使用statspack收集统计信息时建议将该值设置为 TRUE，否则收集的统计信息大约只能起到10%的作用

SQL> @C:\oracle\ora92\rdbms\admin\spcreate.sql;
输入 perfstat_password 的值:  perfstat
输入default_tablespace的值:  perfstat
输入temporary_tablespace 的值:  temp

NOTE:
SPCPKG complete. Please check spcpkg.lis for any errors.
--需要出现上述语句才算成功，否则请查看.lis文件并执行，进行重建
SQL> @C:\oracle\ora92\rdbms\admin\spdrop.sql
SQL> @C:\oracle\ora92\rdbms\admin\spcreate.sql;

2.查看文件夹会产生三个文件
C:\oracle\ora92\bin
spcpkg.lis
spctab.lis
spcusr.lis

3.手动执行STATSPACK收集统计信息
SQL> show user
USER为"PERFSTAT"
SQL> execute statspack.snap;

4.生成STATSPACK调整报告
SQL> @C:\oracle\ora92\rdbms\admin\spreport.sql;
Current Instance
~~~~~~~~~~~~~~~~
   DB Id    DB Name      Inst Num Instance
----------- ------------ -------- ------------
 357371480 COLM                1 colm
Instances in this Statspack schema
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
   DB Id    Inst Num DB Name      Instance     Host
----------- -------- ------------ ------------ ------------
 357371480        1 COLM         colm         STEVENHUANG3
Using 357371480 for database Id
Using          1 for instance number
Completed Snapshots
                           Snap                    Snap
Instance     DB Name         Id   Snap Started    Level Comment
------------ ------------ ----- ----------------- ----- ----------------------
colm         COLM           1 11 10月 2006 21:5     5
                                0
                              2 11 10月 2006 21:5     5
                                0
Specify the Begin and End Snapshot Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
输入 begin_snap 的值: 1
输入 end_snap 的值: 2
End   Snapshot Id specified: 2
Specify the Report Name
~~~~~~~~~~~~~~~~~~~~~~~
The default report file name is sp_1_2. To use this name,
press to continue, otherwise enter an alternative.
输入 report_name 的值: report1.txt
End of Report

5.查看产生的report1文档
C:\oracle\ora92\bin\report1.txt
6.自动执行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 每五分钟一次
7. 移除自动执行STATSPACK收集
SQL>select job,log_user,priv_user,last_date,next_date,interval from user_jobs; --先查看当前自动收集的jobs
SQL> execute dbms_job.remove('1');  --移除任务1
8．删除统计资料（statspack相关的所有系统表）
SQL>select max(snap_id) from stats$snapshot;
SQL> @C:\oracle\ora92\rdbms\admin\sptrunc.sql;
Warning
~~~~~~~
Running sptrunc.sql removes ALL data from Statspack tables. You may
wish to export the data before continuing.
About to Truncate Statspack Tables
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
If you would like to continue, press
输入 return 的值:
Entered - starting truncate operation