create statspack

              spdrop.sql

               Drop PERFSTAT's tables 、 indexes、synonym

               Drop PERFSTAT user

              spcreate.sql

                create PERFSTAT user

                create PERFSTAT's tables 、 indexes、synonym

                create procedure

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

              使用Statspack:

              sqlplus perfstat/perfstat

              exec statspack.snap; -- 进行信息收集统计，每次运行都将产生一个快照号

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

              -- 获得快照号，必须要有两个以上的快照，才能生成报表

              select SNAP_ID, SNAP_TIME from STATS$SNAPSHOT;

              @spreport.sql -- 输入需要查看的开始快照号与结束快照号

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

              spauto.sql - 利用dbms_job提交一个作业，自动的进行STATPACK的信息收集统计

              spool spauto.lis

              --

              --  Schedule a snapshot to be run on this instance every hour, on the hour

              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;

              /

              SQL> desc dbms_job.submit

                     Parameter Type           Mode Default?

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

                     JOB       BINARY_INTEGER OUT          

                     WHAT      VARCHAR2       IN           

                     NEXT_DATE DATE           IN   Y       

                     INTERVAL  VARCHAR2       IN   Y        

                     NO_PARSE  BOOLEAN        IN   Y       

                     INSTANCE  BINARY_INTEGER IN   Y       

                     FORCE     BOOLEAN        IN   Y       

              select job, next_date, next_sec

                from user_jobs

               where job = &&jobno;

              spool off;

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

              sppurge.sql - 清除一段范围内的统计信息，需要提供开始快照与结束快照号

              引用statspack.purge清除统计信息

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

              sptrunc.sql - 清除(truncate)所有统计信息