Oracle-L: Re: Statspack archiving

From: Tim Gorman <Tim_at_SageLogix.com>
Date: Sat, 10 Aug 2002 11:53:18 -0800
Message-ID: <F001.004B16CF.20020810115318@fatcity.com>

The original question on this thread was for an automated "purge" for STATSPACK. I wrote this stored procedure based on the v8.1.7 version of the standard "sppurge.sql" script. I'd use that script, except I don't like the way it is called (i.e. range of SNAP_IDs). This stored procedure figures out the range of SNAP_IDs based on the parameter indicating the number of days of data to retain...

Hope this helps -- as always, no warranties!

begin included SQL*Plus script ------------------------------ /**********************************************************************
- File: sppurpkg.sql
- Type: SQL*Plus script
- Author: Tim Gorman (Evergreen Database Technologies, Inc.)
- Date: 18Oct01
  *
- Description:
- SQL*Plus script containing DDL commands to create the package
- SPPURPKG, intended for use with STATSPACK from Oracle database
- versions 8.1.7 and above. Adapted from the "sppurge.sql" script
- which is included with standard STATSPACK v8.1.7, it is easier
- to use because it can be called automatedly from the DBMS_JOB
- package (instead of interactively as with "sppurge.sql") and it
- takes only the number of days of STATSPACK data to retain
- (instead of prompting for a begin/end range of SNAP_IDs, like
- "sppurge.sql")
  *
- After the package is created, then this script will submit the
- procedure "SPPURPKG.RUN(14)" (i.e. purge data older than 14
- days) to run once per day. You may want to modify this,
- depending on the volume of activity on the database(s) being
- monitored by STATSPACK and the amount of storage you are
- prepared to allocate to the PERFSTAT schema...
  *
- Modifications:
  *********************************************************************/
  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

variable jobno number;
begin
dbms_job.submit(:jobno, 'sppurpkg.purge(14);', sysdate+(1/1440), 'SYSDATE+1', TRUE);
commit;
end;
/

set pages 100
select * from user_jobs where job = :jobno;

spool off

----------------------- end included SQL*Plus
script ------------------------------

Original Message ----- To: "Multiple recipients of list ORACLE-L" <ORACLE-L_at_fatcity.com> Sent: Wednesday, August 07, 2002 4:16 PM

> > Hey Jared,
> > Did you do anything with statspack or the wait interface in your
> > toolset/book? Gotta get a copy for inspiration.
>
> No, no wait interface stuff, at least, I don't remember any.
>
> You're correct about writing code for others to see.  It's one
> thing to write code for internal use, but just try packaging
> it and writing coherent documentation.  The amount of effort
> shoots *wayyyy* up.
>
> I have ideas for new iterations of the toolkit that is
> included in the book, but I'll wait and see what others
> like and dislike, and what they think is missing.
>
> Probably a lot. Can't really pack too much into a couple
> hundred pages.
>
> Jared
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author:
>   INET: Jared.Still_at_radisys.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).

-- 
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 Sat Aug 10 2002 - 14:53:18 CDT