Re: STATSPACK PURGE

From: Tim Gorman <Tim_at_SageLogix.com>
Date: Thu, 04 Apr 2002 19:58:19 -0800
Message-ID: <F001.0043C4CB.20020404195819@fatcity.com>

This was adapted from SPPURGE.SQL for this very purpose...

---

set echo on feedback on timing on verify on

spool sppurpkg

connect perfstat

show user
show release

set termout off
create or replace package SPPURPKG
is
 --
 procedure PURGE(in_days_older_than IN INTEGER);  --
end SPPURPKG;
/
set termout on
show errors

set termout off
create or replace package body SPPURPKG
is
 --
 procedure PURGE(in_days_older_than IN INTEGER)  is
--

  cursor get_snaps(in_days IN INTEGER)
  is
  select s.rowid,

   s.snap_id,
   s.dbid,
   s.instance_number

  from stats$snapshot s,
   sys.v_$database d,
   sys.v_$instance i
  where s.dbid = d.dbid
  and s.instance_number = i.instance_number   and s.snap_time < trunc(sysdate) - in_days;
--

  errcontext VARCHAR2(100);
  errmsg VARCHAR2(1000);
  save_module VARCHAR2(48);
  save_action VARCHAR2(32);
--

 begin
--

  errcontext := 'save settings of DBMS_APPLICATION_INFO';   dbms_application_info.read_module(save_module, save_action);   dbms_application_info.set_module('SPPURPKG.PURGE', 'begin');
--

  errcontext := 'open/fetch get_snaps';
  dbms_application_info.set_action(errcontext);   for x in get_snaps(in_days_older_than) loop    --
   errcontext := 'delete (cascade) STATS$SNAPSHOT';    dbms_application_info.set_action(errcontext);    delete
   from stats$snapshot
   where rowid = x.rowid;
   --
   errcontext := 'delete "dangling" STATS$SQLTEXT rows';    dbms_application_info.set_action(errcontext);    delete
   from stats$sqltext
   where (hash_value, text_subset) not in     (select /*+ hash_aj(ss) */ hash_value, text_subset      from stats$sql_summary ss
    );
   --
   errcontext := 'delete "dangling" STATS$DATABASE_INSTANCE rows';    dbms_application_info.set_action(errcontext);    delete
   from stats$database_instance i
   where i.instance_number = x.instance_number    and i.dbid = x.dbid
   and not exists
    (select 1

     from stats$snapshot s
     where s.dbid            = i.dbid
     and s.instance_number = i.instance_number
     and s.startup_time    = i.startup_time

    );
   --
   errcontext := 'delete "dangling" STATS$STATSPACK_PARAMETER rows';    dbms_application_info.set_action(errcontext);    delete
   from stats$statspack_parameter p
   where p.instance_number = x.instance_number    and p.dbid = x.dbid
   and not exists
    (select 1

     from stats$snapshot s
     where s.dbid            = p.dbid
     and s.instance_number = p.instance_number

    );
   --
   errcontext := 'fetch/close get_snaps';    dbms_application_info.set_action(errcontext);    --
  end loop;
--

  errcontext := 'restore saved settings of DBMS_APPLICATION_INFO';   dbms_application_info.set_module(save_module, save_action);
--

 exception
--

  when OTHERS then
   errmsg := sqlerrm;
   dbms_application_info.set_module(save_module, save_action);    raise_application_error(-20000, errcontext || ': ' || errmsg);
--

 end PURGE;
 --
end SPPURPKG;
/
set termout on
show errors

spool off

• Original Message ----- From: Reddy, Madhusudana To: Multiple recipients of list ORACLE-L Sent: Thursday, April 04, 2002 6:48 PM Subject: STATSPACK PURGE

  Hello All,
  I have a need to purge the old statistics accumulated by statspack and I am manually using the 'SPPURGE" ( I pass 'losnapid' and 'hisnapid' ) utility , instead I would like have a PL/SQL program which can be automated through DBMS_JOB . Would somebody help me in this .

  Thanks in advance,
  Madhu

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Tim Gorman
  INET: Tim_at_SageLogix.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Received on Thu Apr 04 2002 - 21:58:19 CST