測試安裝好的Statspack以及使statspack自動收集

　　測試安裝好的Statspack 
運行statspacksnap可以產生系統快照運行兩次然後執行spreportsql就可以生成一個基於兩個時間點的報告 
如果一切正常說明安裝成功

　　已 perfstat/perfstat登陸
*/

　　SQL>execute statspacksnap

　　SQL>execute statspacksnap

　　SQL>@/home/newvers/product//rdbms/admin/spreport

　　SQL>@/home/newvers/product//rdbms/admin/spauto

　　使statspack自動收集系統狀況

　　alert system set job_queue_processes = ;

　　alert system set job_queue_processes =   scope=both;

　　alter system set Timed_statistics=true;

　　[orapaid@bj admin]$ cat spautosql
Rem
Rem $Header: spautosql feb:: cdialeri Exp $
Rem
Rem spautosql
Rem
Rem  Copyright (c) Oracle Corporation All Rights Reserved
Rem
Rem    NAME
Rem      spautosql
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 initora parameter to be
Rem      set to a number > before automatic statistics gathering
Rem      will run
Rem
Rem    MODIFIED   (MM/DD/YY)
Rem    cdialeri    //
Rem    cdialeri    //
Rem    cdialeri    // Created
Rem

　　spool spautolis

　　
  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_jobsubmit(:jobno statspacksnap; trunc(sysdate+/HH) trunc(SYSDATE+/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 initora
prompt  parameter the value for this parameter must be greater
prompt  than 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;

From:http://tw.wingwit.com/Article/program/Oracle/201311/17354.html