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: Post, Ethan <epost_at_kcc.com>
Date: Mon, 24 Sep 2001 09:17:40 -0700
Message-ID: <F001.003965E1.20010924090552@fatcity.com> 






I also found out that 8.1.7 statspack works fine on 8.1.6 which also solves
the problem.  Thanks for the script!



Thanks,


Ethan



>-----Original Message-----

>From: A. Bardeen [mailto:abardeen1_at_yahoo.com]

>Sent: Monday, September 24, 2001 6:25 AM

>To: Multiple recipients of list ORACLE-L

>Subject: Re: Remove Statspack Snapshots

>

>

>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,


>

>-- Anita

>

>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).

>






This e-mail is intended for the use of the addressee(s) only and may contain 
privileged, confidential, or proprietary information that is exempt from disclosure 
under law.  If you have received this message in error, please inform us promptly by 
reply e-mail, then delete the e-mail and destroy any printed copy.   Thank you.




-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Post, Ethan
  INET: epost_at_kcc.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 - 11:17:40 CDT












Original text of this message













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