RE: Stats Pack

From: Diana Duncan <Diana_at_fileFRENZY.com>
Date: Fri, 22 Sep 2000 10:16:10 -0400
Message-Id: <10627.117662@fatcity.com>

Just the other day, I ran out of room, so I just copied the = statsauto.sql
and created a statsclean.sql script to remove old stats (I chose older = than
three days, but that could be changed easily). This is not a great = script,
but may be useful...

Rem
Rem statsclean.sql
Rem
Rem NAME
Rem statsclean.sql
Rem
Rem DESCRIPTION
Rem SQL*PLUS command file to cleanup the STATPACK statistics. It removes all statistics
Rem prior to three days before it is run. Rem
Rem NOTES

Rem      Should be run as the STATSPACK owner, PERFSTAT.
Rem      Requires job_queue_processes init.ora parameter to be
Rem      set to a number >0 before statistics cleaning
Rem      will run.

Rem
Rem MODIFIED (MM/DD/YY)
Rem dduncan 09/20/2000 - Created
Rem

spool statsclean.lis

Create a procedure to remove the statistics over 3 days old
--
create or replace procedure statsclean is begin delete from stats$snapshot where snap_time < sysdate - 3; commit; end statsclean; /

Schedule the cleanup to be run on this instance every day at 6:00 = a.m.

variable jobno number;
variable instno number;
begin
select instance_number into :instno from v$instance; dbms_job.submit(:jobno, 'statsclean;', trunc(sysdate)+6/24+1, = 'sysdate+1',
TRUE, :instno);
commit;
end;
/

prompt
prompt Job number for cleanup of statistics for this instance prompt =

prompt Note that this job number is needed when modifying or removing prompt the job:
print jobno

prompt
prompt Job queue process

prompt  ~~~~~~~~~~~~~~~~~

prompt Below is the current setting of the job_queue_processes = init.ora
prompt parameter - the value for this parameter must be greater prompt than 0 to clean up statistics:
show parameter job_queue_processes
prompt

prompt
prompt Next scheduled run

prompt  ~~~~~~~~~~~~~~~~~~

prompt The next scheduled run for this job is: select job, next_date, next_sec
from user_jobs
where job =3D :jobno;

spool off;

-----Original Message-----

From: susan mcclain [mailto:susie8529_at_hotmail.com] Sent: Friday, September 22, 2000 10:20 AM To: Multiple recipients of list ORACLE-L Subject: RE: Stats Pack

Hi,

I will be giving a presentation on STATSPACK at OOW. The utility is = pretty=20
easy to use. There doesn't seem to be a lot of documentation on it, = but=20
there are some good papers out on metalink (check technet also).

I personally like it, and have also talked with some DBA's that have = been=20
using it in a production environment for months and swear by it...

I agree with Vincent though, Oracle did not provide an good way to = delete=20
old records, or snapshot. You really have to monitor your datafile = growth=20
depending on how much you use the utility.

Susan McClain
Manager, Oracle DBA
marchFIRST

----Original Message Follows----

Reply-To: ORACLE-L_at_fatcity.com
To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com> Date: Fri, 22 Sep 2000 05:05:51 -0800

Dear Vincent,

May you please send to mt personnal email Part1 and Part2.pdf files.

Thanks

 > -----Original Message-----
 > From:	Vincent Ruger [SMTP:Vincent.Ruger_at_nos.nl]
 > Sent:	=D0=E1=F1=E1=F3=EA=E5=F5=DE, 22 =
=D3=E5=F0=F4=E5=EC=E2=F1=DF=EF=F5 2000 11:42 =F0=EC
 > To:	Multiple recipients of list ORACLE-L
 > Subject:	RE: Stats Pack
 >
 > Dave,
 >
 > A few articals on statspack.
 > Unfortunatly there's no script yet to get rid of old snapshots.
 >
 > hth
 >
 > Vincent Ruger
 > (Oracle DBA)
 >

> -----Oorspronkelijk bericht-----
> Van: root_at_fatcity.com [ <mailto:root_at_fatcity.com>]Namens David = Turner

 > Verzonden: vrijdag 22 september 2000 0:16
 > Aan: Multiple recipients of list ORACLE-L
 > Onderwerp: Stats Pack
 >
 >
 > Anyone have a good link to info on the Stats Pack?
 >
 >
 > Thanks, Dave Turner
 > --
 > Please see the official ORACLE-L FAQ: <http://www.orafaq.com>
 > --
 > Author: David Turner
 > =A0 INET: turner_at_tellme.com
 >
 > Fat City Network Services=A0=A0=A0 -- (858) 538-5051=A0 FAX: (858) =

538-5051
> San Diego, California=A0=A0=A0=A0=A0=A0=A0 -- 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).=A0 You =

may

 > also send the HELP command for other information (like subscribing).
 >
 > =A0  << File: part1.pdf >>  << File: ReadMe.txt >>  << File: =

part2.pdf
> >>
--

Please see the official ORACLE-L FAQ: http://www.orafaq.com
--

Author: Hatzistavrou Giannis

INET: j.hatzistavrou_at_telesoft.gr

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

________________________________________________________________________=

_
Get Your Private, Free E-mail from MSN Hotmail at = http://www.hotmail.com.

Share information about yourself, create your own public profile at=20 http://profiles.msn.com.

--=20

Please see the official ORACLE-L FAQ: http://www.orafaq.com
--=20

Author: susan mcclain
INET: susie8529_at_hotmail.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 Received on Fri Sep 22 2000 - 09:16:10 CDT