oracle9i staspack

境：
            OS:window Xp
            DBracle9.2.0.1
一、Statspack 的安装
  1、从sqlplus登陆数据库
SQL>conn / as sysdba

connect perfstat/&&perfstat_password@veiwjj@spctab 改参数：

　2、创建一个Statspack表空间，要求80M以上或者使用已经存在的表空间，但必须有80M以上的空闲空间　　
　　SQL>create tablespace statspack datafile '' size 100M AUTOEXTEND ON EXTENT MANAGEMENT LOCAL UNIFORM. SIZE 128;

　3、执行安装脚本，在Oracle_Home\dbms\admin下：
　  SQL>@D:\oracle\rdbms\admin\spcreate.sql
     注：创建过程中会提示输入perfstat的密码、默认表空间和默认临时表空间。perfstat密码任意设置，默认表空间为上一步创建的表空间：                staspack，默认临时表空间为temp。

  4、检查当前用户是否为perfstat:
     SQL>show user
       USER 为 "perfstat"
     注：如果不是perfstat,则手动用perfstsat登陆。
     SQL>conn perfstat/****（上一步设置的perfstat的密码）

二、如果安装过程出错，怎么纠正
  1、必须先用SYS用户登陆数据库
SQL>conn / as sysdba

2、先删除之前创建的对象，然后再重新创建
  　SQL>@D:\oracle\rdbms\admin\spdrop.sql
   SQL>@D:\oracle\rdbms\admin\spcreate.sql
三、手工采样生成报告
  1、手工抓取快照，必须2次或更过：
     SQL>exec statspack.snap;  
        ---间隔一段时间
     SQL>exec statspack.snap;                        

  2、生成报告
      SQL>@D:\oracle\rdbms\admin\spreport.sql
      输入 begin_snap 的值:1
      输入 end_snap 的值:100
      输入 report_name 的值:\report.txt
      ---以上输入内容根据自己的需要输入。begin_snap开始快照点，end_snap 终止快找点，report_name报告名(最好包括目录和文件名，便于以后查看生成报告)
   
  3、根据report_name输入的报告地址，用文本编辑器等打开生成的报告，具体情况具体分析。
        
四、系统自动采样数据
1、修改spauto.sql内容，定义定时任务 ，定义采样数据的时间间隔 ：          bms_job.submit(:jobno,’statspack.snap;’,trunc(sysdate+1/24,”HH”),’trunc(sysdate+1/24,”HH”),TRUE,:instno);
      
     一天24小时，1440分钟，则：
     每小时一次：   1/24 (建议使用)
     每30分钟一次：1/48
     每10分钟一次：1/144
     每5分钟一次： 1/288

2、运行自动执行脚本
   SQL>@D:\oracle\rdbms\admin\spauto.sql
   
3、运行生成报告脚本
   SQL>@D:\oracle\rdbms\admin\spreport.sql
　　
五、删除系统自动采job
1、检查任务中是否有这个任务，并记下该job的ID
　　SQL>select job,interval from user_jobs; ；

2、根据查到的job_id删除任务
　　SQL>conn perfstat/oracle
　　SQL>exec dbms_job.remove(job_id)；

3、删除历史数据
　  SQL>delete from stats$snapshot where snap_id??

4、删除全部数据
　 SQL>@D:\oracle\rdbms\admin\sptrunc.sql

无论是删除STATS$SNAPSHOT表的时候，还是使用SPPURGE.SQL脚本，都无法清除这张表的记录。

SQL> SELECT SNAP_ID FROM STATS$SNAPSHOT;

SNAP_ID
----------
1

SQL> SELECT COUNT(*) FROM STATS$UNDOSTAT;

COUNT(*)
----------
55

SQL> SELECT COUNT(*) FROM STATS$SGASTAT;

COUNT(*)
----------
41

SQL> DELETE STATS$SNAPSHOT;

已删除 1 行。

SQL> SELECT COUNT(*) FROM STATS$SGASTAT;

COUNT(*)
----------
0

SQL> SELECT COUNT(*) FROM STATS$UNDOSTAT;

COUNT(*)
----------
55

使用SPPURGE.SQL脚本也是一样：

SQL> ROLLBACK;

回退已完成。

SQL> @?/rdbms/admin/sppurge.sql
SQL> Rem
SQL> Rem $Header: sppurge.sql 20-mar-2002.18:02:43 vbarrier Exp $
SQL> Rem
SQL> Rem sppurge.sql
SQL> Rem
SQL> Rem Copyright (c) 2000, 2002, Oracle Corporation. All rights reserved.
SQL> Rem
SQL> Rem NAME
SQL> Rem sppurge.sql - STATSPACK Purge
SQL> Rem
SQL> Rem DESCRIPTION
SQL> Rem Purge a range of Snapshot Id's between the specified
SQL> Rem begin and end Snap Id's
SQL> Rem
SQL> Rem NOTES
SQL> Rem Should be run as STATSPACK user, PERFSTAT.
SQL> Rem
SQL> Rem Running purge may require the use of a large rollback
SQL> Rem segment; to avoid rollback segment related errors
SQL> Rem explicitly specify a large rollback segment before running
SQL> Rem this script. by using the 'set transaction use rollback segment..'
SQL> Rem command, or alternatively specify a smaller range of
SQL> Rem Snapshot Id's to purge.
SQL> Rem
SQL> Rem
SQL> Rem MODIFIED (MM/DD/YY)
SQL> Rem vbarrier 03/20/02 - Optional stats$seg_stat_obj purge
SQL> Rem cdialeri 04/12/01 - 9.0
SQL> Rem cdialeri 04/11/00 - 1261813
SQL> Rem cdialeri 03/15/00 - Conform. to new structure
SQL> Rem densor.uk 05/00/94 - Allow purge of range of snaps
SQL> Rem gwood.uk 10/12/92 - Use RI for deletes to most tables
SQL> Rem cellis.uk 11/15/89 - Created
SQL> Rem
SQL>
SQL> set feedback off verify off pages 999
SQL> undefine dbid inst_num losnapid hisnapid
SQL> whenever sqlerror exit rollback
SQL>
SQL> spool sppurge.lis
SQL>
SQL>
SQL> /* ------------------------------------------------------------------------- */
SQL>
SQL> --
SQL> -- Get the current database/instance information - this will be used
SQL> -- later in the report along with bid, eid to lookup snapshots
SQL>
SQL> prompt

SQL> prompt

SQL> prompt Database Instance currently connected to
Database Instance currently connected to
SQL> prompt ========================================
========================================
SQL>
SQL> column inst_num heading "Inst Num" new_value inst_num format 99999;
SQL> column inst_name heading "Instance|Name" new_value inst_name format a10;
SQL> column db_name heading "DB Name" new_value db_name format a10;
SQL> column dbid heading "DB Id" new_value dbid format 9999999999 just c;
SQL> select d.dbid dbid
2 , d.name db_name
3 , i.instance_number inst_num
4 , i.instance_name inst_name
5 from v$database d,
6 v$instance i;

Instance
DB Id DB Name Inst Num Name
----------- ---------- -------- ----------
2270762593 TESTDATA 1 testdata
SQL>
SQL> variable dbid number;
SQL> variable inst_num number;
SQL> variable inst_name varchar2(20);
SQL> variable db_name varchar2(20);
SQL> begin
2 :dbid := &dbid;
3 :inst_num := &inst_num;
4 :inst_name := '&inst_name';
5 :db_name := '&db_name';
6 end;
7 /
SQL>
SQL>
SQL> --
SQL> -- List Snapshots
SQL>
SQL> column snap_id format 9999990 heading 'Snap Id'
SQL> column snap_date format a21 heading 'Snapshot Started'
SQL> column host_name format a15 heading 'Host'
SQL> column parallel format a3 heading 'OPS' trunc
SQL> column level format 99 heading 'Snap|Level'
SQL> column versn format a7 heading 'Release'
SQL> column ucomment heading 'Comment' format a25;
SQL>
SQL> prompt

SQL> prompt

SQL> prompt Snapshots for this database instance
Snapshots for this database instance
SQL> prompt ====================================
====================================
SQL>
SQL> select s.snap_id
2 , s.snap_level "level"
3 , to_char(s.snap_time,' dd Mon YYYY HH24:mi:ss') snap_date
4 , di.host_name host_name
5 , s.ucomment
6 from stats$snapshot s
7 , stats$database_instance di
8 where s.dbid = :dbid
9 and di.dbid = :dbid
10 and s.instance_number = :inst_num
11 and di.instance_number = :inst_num
12 and di.startup_time = s.startup_time
13 order by db_name, instance_name, snap_id;

Snap
Snap Id Level Snapshot Started Host Comment
-------- ----- --------------------- --------------- -------------------------
1 5 07 7月 2008 16:35:4 localhost.local
1 domain

SQL>
SQL>
SQL>
SQL> --
SQL> -- Post warning
SQL>
SQL> prompt

SQL> prompt

SQL> prompt Warning
Warning
SQL> prompt ~~~~~~~
~~~~~~~
SQL> prompt sppurge.sql deletes all snapshots ranging between the lower and
sppurge.sql deletes all snapshots ranging between the lower and
SQL> prompt upper bound Snapshot Id's specified, for the database instance
upper bound Snapshot Id's specified, for the database instance
SQL> prompt you are connected to.
you are connected to.
SQL> prompt

SQL> prompt You may wish to export this data before continuing.
You may wish to export this data before continuing.
SQL> prompt

SQL>
SQL>
SQL>
SQL> --
SQL> -- Obtain snapshot ranges
SQL>
SQL> prompt

SQL> prompt Specify the Lo Snap Id and Hi Snap Id range to purge
Specify the Lo Snap Id and Hi Snap Id range to purge
SQL> prompt ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
SQL> prompt Using &&LoSnapId for lower bound.输入 losnapid 的值: 1
Using 1 for lower bound.
SQL> prompt

SQL> prompt Using &&HiSnapId for upper bound.输入 hisnapid 的值: 1
Using 1 for upper bound.
SQL>
SQL>
SQL> variable lo_snap number;
SQL> variable hi_snap number;
SQL> begin
2 :lo_snap := &losnapid;
3 :hi_snap := &hisnapid;
4 end;
5 /
SQL>
SQL> set termout off
SQL>
SQL> set heading off
SQL>
SQL> select 'WARNING: LoSnapId or HiSnapId specified does not exist in STATS$SNAPSHOT'
2 from dual
3 where not exists
4 (select null
5 from stats$snapshot
6 where instance_number = :inst_num
7 and dbid = :dbid
8 and snap_id = :lo_snap)
9 or not exists
10 (select null
11 from stats$snapshot
12 where instance_number = :inst_num
13 and dbid = :dbid
14 and snap_id = :hi_snap);
SQL>
SQL> set heading on
SQL>
SQL>
SQL>
SQL> --
SQL> -- Delete all data for the specified ranges
SQL>
SQL> /* Use RI to delete parent snapshot and all child records */
SQL>
SQL> prompt

SQL> prompt Deleting snapshots &&losnapid - &&hisnapid..
Deleting snapshots 1 - 1.
SQL> delete from stats$snapshot
2 where instance_number = :inst_num
3 and dbid = :dbid
4 and snap_id between :lo_snap and :hi_snap;
SQL>
SQL>
SQL> set termout off;
SQL>
SQL>
SQL>
SQL> /* Delete any undostat rows that cover the snap times */
SQL>
SQL> delete from stats$undostat us
2 where dbid = :dbid
3 and instance_number = :inst_num
4 and begin_time < to_date(:btime, 'YYYYMMDD HH24:MI:SS')
5 and end_time > to_date(:etime, 'YYYYMMDD HH24:MI:SS');
SQL>
SQL>
SQL>
SQL> /* Delete any dangling database instance rows for that startup time */
SQL>
SQL> delete from stats$database_instance di
2 where instance_number = :inst_num
3 and dbid = :dbid
4 and not exists (select 1
5 from stats$snapshot s
6 where s.dbid = di.dbid
7 and s.instance_number = di.instance_number
8 and s.startup_time = di.startup_time);
SQL>
SQL>
SQL>
SQL> /* Delete any dangling statspack parameter rows for the database instance */
SQL>
SQL> delete from stats$statspack_parameter sp
2 where instance_number = :inst_num
3 and dbid = :dbid
4 and not exists (select 1
5 from stats$snapshot s
6 where s.dbid = sp.dbid
7 and s.instance_number = sp.instance_number);
SQL>
SQL>
SQL> --
SQL> --
SQL>
SQL> prompt

SQL> prompt

SQL> prompt Purge of specified Snapshot range complete. If you wish to ROLLBACK
Purge of specified Snapshot range complete. If you wish to ROLLBACK
SQL> prompt the purge, it is still possible to do so. Exitting from SQL*Plus will
the purge, it is still possible to do so. Exitting from SQL*Plus will
SQL> prompt automatically commit the purge.
automatically commit the purge.
SQL> prompt

SQL>
SQL> --
SQL> --
SQL>
SQL> spool off
SQL> set feedback on termout on
SQL> whenever sqlerror continue
SQL> SELECT COUNT(*) FROM STATS$SGASTAT;

COUNT(*)
----------
0

已选择 1 行。

SQL> SELECT COUNT(*) FROM STATS$UNDOSTAT;

COUNT(*)
----------
55

已选择 1 行。

Metalink的bug描述：Bug No. 4046866中进行了说明，Oracle的sppurge.sql脚本中删除STATS$UNDOSTAT表的SQL出现了错误，里面的大于号和小于号写反了。

而删除STATS$SNAPSHOT时没有删除STATS$UNDOSTAT，可能是Oracle漏掉了这个表。

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