STATSPACK收集统计信息-调整报告

一.SQL*PLUS中输入

SQL> conn as sysdba;

SQL> alter system set job_queue_processes = 6; --自动执行数据收集时该参数需要大于0

SQL> alter system set timed_statistics = true; 

--使用statspack收集统计信息时建议将该值设置为 TRUE，否则收集的统计信息大约只能起到10%的作用

这句话不能少，否则下面的语句不会执行成功

在创建表空间create tablespace perfstat datafile ‘d:/test_perfstat.dbf’ size 500M;

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;

 

二.查看文件夹会产生三个文件

C:\oracle\ora92\bin

spcpkg.lis

spctab.lis

spcusr.lis

 

三.手动执行STATSPACK收集统计信息

SQL> show user

USER为"PERFSTAT"

SQL> execute statspack.snap;

 

四.生成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 <return></return>to continue, otherwise enter an alternative.

输入 report_name 的值: report1.txt

End of Report