对STATSPACK包中的一些函数进行总结

STATSPACK是通过下面这些函数进行数据收集的。有时DBA可以直接使用这些函数。
Stats$database_instance:STATSPACE的主要锚点。包含了数据库ID，实例编号，及正在度量的数据库所处的服务器的名字。另处还有数据库的启动时间，快照ID和并行缺省。
Stats$statspack_parameter:数据库实例的快照级别。
Stats$level_description:描述STATSPACK集合中各个级别的数据收集。级别有0,5和10.0级收集会填充stats$ sql_summaryt stats$latch_children之外的所有表。5级收集会为stats$sql_summary填充数据。10级为stats$ latch_children填充数据。
Stats$snapshot:对于每个数据库来说都有好多此表。表中有快照ID，数据库ID，实例编号及快照产生的时间。另处还有共享内存的一些值。
事件表：
stats$system_event:一种为每一个快照生成多行的表。以获取系统事件的信息。
Stats$session_event:会话事件表。获取快照活动的那些会话中的信息。
Stats$idle_event:空闲事件信息，没有特殊用途。
Stats$rowcache_summary:
Stats$sgaxs用于对单独RAC环境中使用的多个SAG进行交叉引用。此表只在RAC环境中存在。
Stats$parameter:
Stats$latch_misses_summary:
Stats$sgastat_summary:
Stats$sgastat_summary
Stats$bg_event_summary:汇总了所有数据库后台事件信息。
Stats$enqueuestat:描述了队列等待的信息。只有发现特定进程有过多的队列等待才需要考虑调整进程。表中描述了26种类型的锁，但只有几个是对调整有意义的。
Stats$waitstat：描述了系统等待事件统计。
Stats$latch锁存是用来串行化事务处理的。表中包含了锁存的一些信息。
Stats$latch_chileren：只有在级别10时才会填充表中的数据。
Stats$sysstat:与v$sysstat的结构一致。具有226个独立的统计名称。分析选定条件下数据库的整体负载。
Stats$sesstat获取快照执行时刻的活动会话信息。
Stats$librarycache：表中的每个值都代表了库高速缓存的一个选定事件。库高速缓存是SQL语句进行解析和执行的内存空间。
Stats$rollstat：表记录ORACLE回滚活动的信息。表中的数据对于回滚段大小和初始化参数最初调整很有用。
Stats$buffer_pool_statistics
Stats$filestatxs：IO在任何数据库中都是一个开销最大的操作。这个表提供了各个数据库的详细信息。包括IO数量。写入IO数量及处理过程中经历的争用。
Stats$sql_summary:提供了所有SQL语句的内容，以及各个语句所使用资源的详细描述。
Stats$sql_statistics:
Stats$sqltext:
自动清理统计的脚本。

set feedback off verify off pages 999
undefine dbid inst_num losnapid hisnapid
whenever sqlerror exit rollback

spool sppurge.lis


/* ------------------------------------------------------------------------- */

--
-- Get the current database/instance information - this will be used
-- later in the report along with bid, eid to lookup snapshots

prompt
prompt
prompt Database Instance currently connected to
prompt ========================================

column inst_num  heading "Inst Num"  new_value inst_num  format 99999;
column inst_name heading "Instance|Name"  new_value inst_name format a10;
column db_name   heading "DB Name"   new_value db_name   format a10;
column dbid      heading "DB Id"     new_value dbid      format 9999999999 just c;
select d.dbid            dbid
     , d.name            db_name
     , i.instance_number inst_num
     , i.instance_name   inst_name
  from v$database d,
       v$instance i;

variable dbid       number;
variable inst_num   number;
variable inst_name  varchar2(20);
variable db_name    varchar2(20);
begin
  :dbid      :=  &dbid;
  :inst_num  :=  &inst_num;
  :inst_name := '&inst_name';
  :db_name   := '&db_name';
end;
/


--
--  List Snapshots

column snap_id       format 9999990 heading 'Snap Id'
column snap_date     format a21   heading 'Snapshot Started'
column host_name     format a15   heading 'Host'
column parallel      format a3    heading 'OPS' trunc
column level         format 99    heading 'Snap|Level'
column versn         format a7    heading 'Release'
column ucomment          heading 'Comment' format a25;

prompt
prompt
prompt Snapshots for this database instance
prompt ====================================

select s.snap_id
     , s.snap_level                                      "level"
     , to_char(s.snap_time,' dd Mon YYYY HH24:mi:ss')    snap_date
     , di.host_name                                      host_name
     , s.ucomment
  from stats$snapshot s
     , stats$database_instance di
 where s.dbid              = :dbid
   and di.dbid             = :dbid
   and s.instance_number   = :inst_num
   and di.instance_number  = :inst_num
   and di.startup_time     = s.startup_time
 order by db_name, instance_name, snap_id;



--
--  Post warning

prompt
prompt
prompt Warning
prompt ~~~~~~~
prompt sppurge.sql deletes all snapshots ranging between the lower and
prompt upper bound Snapshot Id's specified, for the database instance
prompt you are connected to.
prompt
prompt You may wish to export this data before continuing.
prompt



--
--  Obtain snapshot ranges

prompt
prompt Specify the Lo Snap Id and Hi Snap Id range to purge
prompt ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
--prompt Using &&LoSnapId for lower bound.
prompt
--prompt Using &&HiSnapId for upper bound.


variable lo_snap   number;
variable hi_snap   number;
column losnapid new_value losnapid
column hisnapid new_value hisnapid
select (min(snap_id)) losnapid,(max(snap_id)) hisnapid from stats$snapshot where snap_time < sysdate -90;
begin
  :lo_snap   :=  '&losnapid';
  :hi_snap   :=  '&hisnapid';
end;
/

set termout off
--
-- Get begin and end snapshot times - these are used to delete undostat
column btime new_value btime
column etime new_value etime
select to_char(snap_time, 'YYYYMMDD HH24:MI:SS') btime
  from stats$snapshot b
 where b.snap_id         = :lo_snap
   and b.dbid            = :dbid
   and b.instance_number = :inst_num;
select to_char(snap_time, 'YYYYMMDD HH24:MI:SS') etime
  from stats$snapshot e
 where e.snap_id         = :hi_snap
   and e.dbid            = :dbid
   and e.instance_number = :inst_num;

variable btime     varchar2(25);
variable etime     varchar2(25);
begin
  :btime     :=  '&btime';
  :etime     :=  '&etime';
end;
/
set termout on

set heading off

select 'WARNING: LoSnapId or HiSnapId specified does not exist in STATS$SNAPSHOT'
  from dual
 where not exists
      (select null
         from stats$snapshot
        where instance_number = :inst_num
          and dbid            = :dbid
          and snap_id         = :lo_snap)
    or not exists
      (select null
         from stats$snapshot
        where instance_number = :inst_num
          and dbid            = :dbid
          and snap_id         = :hi_snap);

set heading on



--
--  Delete all data for the specified ranges

/*  Use RI to delete parent snapshot and all child records  */

prompt
prompt Deleting snapshots &&losnapid - &&hisnapid..
delete from stats$snapshot
 where instance_number = :inst_num
   and dbid            = :dbid
   and snap_id between :lo_snap and :hi_snap;


set termout off;
/*  Delete any dangling SQLtext  */
/*
Rem  The following statement deletes any dangling SQL statements which
Rem  are no longer referred to by ANY snapshots.  This statment has been
Rem  commented out as it can be very resource intensive.

alter session set hash_area_size=1048576;
delete --+ index_ffs(st)
  from stats$sqltext st
 where (hash_value, text_subset) not in
       (select --+ hash_aj full(ss) no_expand
               hash_value, text_subset
          from stats$sql_summary ss
         where (   (   snap_id     < :lo_snap
                    or snap_id     > :hi_snap
                   )
                   and dbid            = :dbid
                   and instance_number = :inst_num
               )
            or (   dbid            != :dbid
                or instance_number != :inst_num)
        );

Rem Adding an optional STATS$SEG_STAT_OBJ delete statement

delete --+ index_ffs(sso)
  from stats$seg_stat_obj sso
 where (dbid, dataobj#, obj#) not in
       (select --+ hash_aj full(ss) no_expand
              dbid, dataobj#, obj#
          from stats$seg_stat ss
         where ( (   snap_id     < :lo_snap
                  or snap_id     > :hi_snap
                 )
                 and dbid            = :dbid
                 and instance_number = :inst_num
               )
            or (   dbid            != :dbid
                or instance_number != :inst_num)
        );
*/
set termout on;



/*  Delete any undostat rows that cover the snap times  */

delete from stats$undostat us
 where dbid            = :dbid
   and instance_number = :inst_num
   and begin_time      <  to_date(:btime, 'YYYYMMDD HH24:MI:SS')
   and end_time        >  to_date(:etime, 'YYYYMMDD HH24:MI:SS');



/*  Delete any dangling database instance rows for that startup time  */

delete from stats$database_instance di
 where instance_number = :inst_num
   and dbid            = :dbid
   and not exists (select 1
                     from stats$snapshot s
                    where s.dbid            = di.dbid
                      and s.instance_number = di.instance_number
                      and s.startup_time    = di.startup_time);



/*  Delete any dangling statspack parameter rows for the database instance  */

delete from stats$statspack_parameter sp
 where instance_number = :inst_num
   and dbid            = :dbid
   and not exists (select 1
                     from stats$snapshot s
                    where s.dbid            = sp.dbid
                      and s.instance_number = sp.instance_number);


--
--

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

--
--
commit;
spool off
set feedback on termout on
whenever sqlerror continue