Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
![]() |
![]() |
Home -> Community -> Mailing Lists -> Oracle-L -> RE: Stats Pack
Kewl,
And thanks.
FYI, there is a file, sppurge.sql that will clean up old stats that will be included in version 8.1.7.
Susan
----Original Message Follows----
From: Diana Duncan <Diana_at_fileFRENZY.com>
Reply-To: ORACLE-L_at_fatcity.com
To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
Subject: RE: Stats Pack
Date: Fri, 22 Sep 2000 07:20:51 -0800
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.
spool statsclean.lis
--
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:
prompt
prompt Job queue process
prompt ~~~~~~~~~~~~~~~~~
prompt
prompt Next scheduled run
prompt ~~~~~~~~~~~~~~~~~~
from user_jobs
where job = :jobno;
spool off;
-----Original Message-----
Sent: Friday, September 22, 2000 10:20 AM
To: Multiple recipients of list ORACLE-L
Hi,
I will be giving a presentation on STATSPACK at OOW. The utility is pretty easy to use. There doesn't seem to be a lot of documentation on it, but 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 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 old records, or snapshot. You really have to monitor your datafile growth 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: Παρασκευή, 22 Σεπτεμβρίου 2000 11:42 πμ > 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 > INET: turner_at_tellme.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). > > << File: part1.pdf >> << File: ReadMe.txt >> << File: part2.pdf > >>
INET: j.hatzistavrou_at_telesoft.gr
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Liststo: 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).
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
Share information about yourself, create your own public profile at http://profiles.msn.com.
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
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 Liststo: 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).
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
INET: Diana_at_fileFRENZY.com
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Liststo: 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).
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
![]() |
![]() |