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: Stats Pack

RE: Stats Pack

From: susan mcclain <susie8529_at_hotmail.com>
Date: Fri, 22 Sep 2000 17:30:06 GMT
Message-Id: <10627.117687@fatcity.com>


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.

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

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:
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 = :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
  > >>

--

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 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 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: Diana Duncan

   INET: Diana_at_fileFRENZY.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 Fri Sep 22 2000 - 12:30:06 CDT

Original text of this message

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