ITPub博客

首页 > Linux操作系统 > Linux操作系统 > ORACLE配置STATSPACK步骤

ORACLE配置STATSPACK步骤

原创 Linux操作系统 作者:babylonia 时间:2008-01-31 17:12:23 0 删除 编辑
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

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/12712263/viewspace-166589/,如需转载,请注明出处,否则将追究法律责任。

上一篇: OLTP和OLAP的区别
下一篇: Oracle数据库命令
请登录后发表评论 登录
全部评论

注册时间:2008-01-31

  • 博文量
    44
  • 访问量
    41038