alter system set job_queue_processes=6;---必设参数,执行数据收集

alter system set timed_statistics=true;---收集os的计时信息,存储在v$sesstats and v$sysstats


install statspack
run as sysdba
be better to switch path $ORACLE_HOME/RDBMS/admin
select file_name from dba_data_files;---determine location of newly_created

---构建的表空间大小和采样间隔有关
create tablespace perfstat datafile 'dracleoradatazxyperstat.dbf' size 500m extent management local;

list script like sp%

@spcreate
note:default_tablespace perfstat
      temporary_tablespace temp
       perfstat_password

---查看相关日志,确认安装成功
host dir *.lis

in linux or unix find err
ls *.lis
grep ORA- *.lis
grep err *.lis

if err of installation
@spdrop.sql


test statspack:
execute statspack.snap  ---genarate snapshot
execute statspack.snap  ---genarate snapshot
@spreport.sql           ---produce time_based report


plan automatic task about timely job for collection data
@spauto.sql
  ---detailed information
  dbms_job.submit(:jobno,'statspack.snap;',trunc(sysdate+1/24,'HH'),'trunc(sysdate+1/24','HH')',TRUE,:instno);
             24hours    1440 minutes
             1/24 hh    every 1 hour
you can modify spauto.sql

---about sample interval,commonly mainly 1 hours runing,


---remove timely job
select job,log_user,priv_user,last_date,next_date,interval from user_jobs;
execute dbms_job.remove('job');----note,complete sample report,must remove timely job!!!!

---remove historical data
select max(snap_id) from stats$snapshot;
delete from stats$snapshot where snap_id<=166;

of course,you can speed up delete speed,
@sptruc.sql


---exp staspack data
spuexp.par
detail information
file=spuexp.dmp log=spuexp.log compress=y grants=y indexes=y rows=y constraints=y owner=perfstat consistent=y
exp userid=perfstat/pwd parfile=spuexp.par