【赛迪网：技术社区整理】测试安装好的statspack:

运行statspack.snap可以产生系统快照，运行两次，然后执行spreport.sql就可以生成一个基于两个时间点的报告。

如果一切正常，说明安装成功。

已 perfstat/perfstat登陆

*/

sql>execute statspack.snap

sql>execute statspack.snap

sql>@/home/newvers/product/92/rdbms/admin/spreport

sql>@/home/newvers/product/92/rdbms/admin/spauto

使statspack自动收集系统状况:

alert system set job_queue_processes = 10;

alert system set job_queue_processes = 10 scope=both;

alter system set timed_statistics=true;

[orapaid@bj37 admin]$ cat spauto.sql

rem

rem $header: spauto.sql 16-feb-00.16:49:37 cdialeri exp $

rem

rem spauto.sql

rem

rem copyright (c) oracle corporation 1999, 2000. all rights reserved.

rem

rem name

rem spauto.sql

rem

rem description

rem sql*plus command file to automate the collection of statpack

rem statistics.

rem

rem notes

rem should be run as the statspack owner, perfstat.

rem requires job_queue_processes init.ora parameter to be

rem set to a number >0 before automatic statistics gathering

rem will run.

rem

rem modified (mm/dd/yy)

rem cdialeri 02/16/00 - 1191805

rem cdialeri 12/06/99 - 1059172, 1103031

rem cdialeri 08/13/99 - created

rem

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;

/

prompt

prompt job number for automated statistics collection for this instance

prompt ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

prompt note that this job number is needed when modifying or removing

prompt the job:

print jobno

prompt

prompt job queue process

prompt ~~~~~~~~~~~~~~~~~

prompt below is the current setting of the job_queue_processes init.ora

prompt parameter - the value for this parameter must be greater

prompt than 0 to use automatic statistics gathering:

show parameter job_queue_processes

prompt

prompt

prompt next scheduled run

prompt ~~~~~~~~~~~~~~~~~~

prompt the next scheduled run for this job is:

select job, next_date, next_sec

from user_jobs

where job = :jobno;

spool off;