Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> Re: Remove Statspack Snapshots

Re: Remove Statspack Snapshots

From: A. Bardeen <abardeen1_at_yahoo.com>
Date: Mon, 24 Sep 2001 05:45:41 -0700
Message-ID: <F001.0039606D.20010924052523@fatcity.com>

Ethan,

The following PL/SQL can be used. It's not tested, so use at your own risk, YMMV, etc.... It also wasn't written by me (of course anyone who knows me could have told you that!) so don't bother asking me questions about it. I imagine anyone proficient in pl/sql could probably modify the sppurge.sql that comes with 8.1.7 to do the same thing.

HTH,

The following SQL present a list of completed snapshots, it will prompt for a starting and ending snapshot id, and delete the appropriate rows:-

Rem
Rem statsdel.sql
Rem
Rem Copyright (c) Oracle Corporation 1999. All Rights Reserved.
Rem
Rem NAME
Rem statsdel.sql
Rem
Rem DESCRIPTION
Rem SQL*Plus command file to remove old snapshot values
Rem
Rem NOTES
Rem Usually run as the STATSPACK owner, PERFSTAT Rem
Rem MODIFIED (MM/DD/YY)
Rem njohnsto.uk 06/13/00 - Created Rem

clear break compute;
repfooter off;
ttitle off;
btitle off;
set timing off veri off space 1 flush on pause off termout on numwidth 10;
set echo off feedback off pagesize 60 linesize 78 newpage 2;

-- 
-- Get the current database/instance information 

column inst_num  heading "Inst Num"    new_value
inst_num  format 99999; 
column inst_name heading "Instance"  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; / -- -- Ask for the starting and ending snapshot Ids to delete between set termout on; column instart_fmt noprint; column versn noprint heading 'Release' new_value versn; column host_name noprint heading 'Host' new_value host_name; column para noprint heading 'OPS' new_value para; column level heading 'Snap Level'; column snap_id heading 'SnapId' format 9990; column snapdat heading 'Snap Started' just c format a22; break on inst_name on db_name on instart_fmt skip 1; ttitle lef 'Completed Snapshots' skip 2; select di.instance_name inst_name
, di.host_name
host_name
, di.db_name
db_name
, sga.version
versn
, sga.parallel
para
, to_char(s.startup_time,' dd Mon "at"
HH24:mi:ss') instart_fmt
, s.snap_id
, to_char(s.snap_time,' dd Mon YYYY HH24:mi:ss')
snapdat
, s.snap_level
"level" from stats$snapshot s
, stats$database_instance di
, stats$sgaxs sga
where s.dbid = :dbid and di.dbid = :dbid and sga.dbid = :dbid and s.instance_number = :inst_num and di.instance_number = :inst_num and sga.instance_number = :inst_num and sga.startup_time = s.startup_time and sga.name = 'Database Buffers' order by db_name, instance_name, snap_id; clear break; ttitle off; accept bid number prompt "Enter first Snap Id to delete: "; accept eid number prompt "Enter last Snap Id to delete: "; set termout off; variable bid number; variable eid number; variable versn varchar2(10); variable para varchar2(9); variable host_name varchar2(64); declare min_snap_id number; max_snap_id number; prev_snap_id number; next_snap_id number; prev_start_time date; next_start_time date; begin -- -- set up parameters -- :bid := &bid; :eid := &eid; :versn := '&versn'; :para := '&para'; :host_name := '&host_name'; :dbid := &dbid; :inst_num := &inst_num; :inst_name := '&inst_name'; :db_name := '&db_name'; -- -- check if values are valid if :eid > :bid then -- -- find the min and max snap_ids select max(snap_id),min(snap_id) into max_snap_id,min_snap_id from stats$snapshot s where s.dbid = :dbid and s.instance_number = :inst_num; -- -- find the startup time of the previous record -- if we've selected the first record, use 1-Jan-1900 if :bid-1 <min_snap_id then prev_start_time := to_date('01-JAN-1900','DD-MON-YYYY'); prev_snap_id:=0; else select snap_id,startup_time into prev_snap_id,prev_start_time from stats$snapshot s where snap_id = ( select max(snap_id) from stats$snapshot t where snap_id <:bid and t.dbid = :dbid and t.instance_number = :inst_num) and s.dbid = :dbid and s.instance_number = :inst_num; end if; -- -- find the startup time of the next record -- if we've selected the last record, use SYSDATE if :eid+1 > max_snap_id then next_start_time := sysdate; next_snap_id :=0; else select snap_id, startup_time into next_snap_id, next_start_time from stats$snapshot s where snap_id = ( select min(snap_id) from stats$snapshot t where snap_id >:eid and t.dbid = :dbid and t.instance_number = :inst_num) and s.dbid = :dbid and s.instance_number = :inst_num; end if; -- -- update sgaxs information to reflect the remaining snapshots update stats$sgaxs set snap_id=next_snap_id where startup_time=next_start_time and prev_start_time < startup_time and dbid = :dbid and instance_number = :inst_num; -- -- delete the snapshots - no need to attend to any of the other tables -- as the foreign key should have a cascade delete status delete from stats$snapshot s where snap_id between :bid and :eid and s.dbid = :dbid and s.instance_number = :inst_num; -- commit; end if; end; / set termout on break on inst_name on db_name on instart_fmt skip 1; ttitle lef 'Completed Snapshots' skip 2; select di.instance_name inst_name
, di.host_name
host_name
, di.db_name
db_name
, sga.version
versn
, sga.parallel
para
, to_char(s.startup_time,' dd Mon "at"
HH24:mi:ss') instart_fmt
, s.snap_id
, to_char(s.snap_time,' dd Mon YYYY HH24:mi:ss')
snapdat
, s.snap_level
"level" from stats$snapshot s
, stats$database_instance di
, stats$sgaxs sga
where s.dbid = :dbid and di.dbid = :dbid and sga.dbid = :dbid and s.instance_number = :inst_num and di.instance_number = :inst_num and sga.instance_number = :inst_num and sga.startup_time = s.startup_time and sga.name = 'Database Buffers' order by db_name, instance_name, snap_id; clear break; clear columns sql ttitle off; btitle off; repfooter off; set linesize 78 termout on feedback 6; -- -- End of script file; --- "Post, Ethan" <epost_at_kcc.com> wrote: > Anyone found a more efficient way to remove a > statspack snapshot prior to > 8.17? > > > ***METALINK SAYS*** > > 7) How do I remove StatsPack snapshots that I am no > longer interested in? > > This functionality is available in Oracle > 8.1.7 using a script called > > sppurge.sql located in > $ORACLE_HOME/rdbms/admin. > > In Oracle 8.1.6 you will need to contact > Oracle Support for > assistance. **YOU GOT TO BE KIDDING** > > Thanks, > - Ethan Post > - http://www.geocities.com/epost1 __________________________________________________ Do You Yahoo!? Get email alerts & NEW webcam video instant messaging with Yahoo! Messenger. http://im.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: A. Bardeen INET: abardeen1_at_yahoo.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 Mon Sep 24 2001 - 07:45:41 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US