RE: Statspack archiving

Hi Dennis et al,

I don't know that I have that much to offer on this but here goes...

> Are you using the Oracle-supplied "statsauto" command? No I prefer cron.

> Which tables and what order to delete rows? I'll provide a script below. I haven't looked at this in quite some time and I'm sure there's room for improvement. At one time there was some reasoning as to retention times for different types of stats but I'm too lazy to try to recreate my supposed reasoning. I'm challenged to remember what I was thinking about yesterday let alone 1+ year ago. :-) Documentation? We don't need no stinkin' DBA documentation. I don't do much "archiving," I just delete because I maintain the necessary history in a round robin database with RRDTool graphs as explained below.

> How often are you generating statistics? I'm doing a level 5 snap every 15 minutes. I've found that I can generally find the bad, new bad or unknown bad SQL stuff without it getting flooded out by the normal known bad stuff. It's kinda cool to see what the problem was yesterday at 3:15 PM. (Usually a custom report.)

> What are you using to generate reports? > What are your goals for the continual monitoring? I created a GUI/HTML interface to the statspack data (among other things). Having historical data graphically presented helps to establish the norm and when there may be performance issues to investigate. This follows step 2 of Gaja's "Oracle Performance Tuning 101 Methodology" which says, "Measure and document current performance."

To do this I created a DBA monitoring HTML display tool which gets data from V$SYSSTAT and V$SYSTEM_EVENT once a minute, stores it in a round robin database and displays it with RRDTool. I've accumulated 6 months of this data and it's AMAZING how lightweight it is. With graphs it's easy to see when something is amiss. I capture expensive SQL via StatsPack every 15 minutes and I have been able to correlate a spike on a graph to specific SQL executed 2 hours earlier. I started with data from StatsPack but eventually went directly to the V$ tables to get it "real time," or at least every minute. I get physical writes, physical reads, db block and consistent gets, queries executed with soft and hard parses, and SQL*Net bytes sent and received as part of my top level graphs to maintain a pulse on activity. My next enhancement is to create a GUI/HTML drill down into the wait interface for the details- if I have sufficient brain power.

Currently my code is a hodgepodge of Perl, Python, RRDTool, PHP, CGI, HTML and the tool is piggybacking on "ORCA" stuff too. All the menus and SQL scripts are database driven (what a novel idea) so a DBA can add functionality via menus and SQL scripts without having to do any coding. My little DBA tool app has kind of grown over time and I would love to be able to pool resources with some of you real Oracle guru types. I've thought about opening it up as a framework for ongoing enhancements in some kind of SourceForge collaborative development effort within the DBA community but that means I'd have to turn the hodgepodge code into a unified set and commit to supporting it. Don't know if I'm ready for that. I've also thought about presenting it but rather than experience the glory of an over-achiever I'm complacently willing to wallow in the anonymity and obscurity of an under-achiever. ;-) My damagement is quite supportive of all this fun stuff but it's all for internal only use at the moment. (I just had an annual review session with the damager earlier this week and he brought it up in the "career advancement" section.) To put it up on SorceForge (or whatever) the damager has said that I'll have to work it out with the corporate lawyer but he's supportive of that too and thinks there shouldn't be any problem. As if there were some kind of proprietary value to all the stuff. And yeah, we have lawyers even in Montana.

Hey Jared,
Did you do anything with statspack or the wait interface in your toolset/book? Gotta get a copy for inspiration.

Anonymous in Bozeman, MT

• dumb bunny script follows --------------------- REM For snapshots older than one week just save the last REM set of SQL statements for each day... delete from stats$sql_summary where snap_id NOT in
  (select maxsnap
  from ( select trunc(snap_time), max(snap_id) maxsnap from stats$snapshot where snap_time < sysdate - 7 and snap_level >= 5 group by trunc(snap_time)) ); commit; delete from stats$sqltext where last_snap_id NOT in
  (select maxsnap
  from ( select trunc(snap_time), max(snap_id) maxsnap from stats$snapshot where snap_time < sysdate - 7 and snap_level >= 5 group by trunc(snap_time)) ); commit; REM Just save the last set of parameters for each day... delete from stats$parameter where snap_id NOT in
  (select maxsnap
  from ( select trunc(snap_time), max(snap_id) maxsnap from stats$snapshot group by trunc(snap_time)) ); commit; REM Delete all parameter values over a week old... delete from stats$parameter where snap_id in
  (select snap_id
  from stats$snapshot where snap_time < sysdate - 7); commit; REM Delete SQL statements over 60 days old... delete from stats$sql_summary where snap_id in
  (select snap_id
  from stats$snapshot where snap_time < sysdate - 60); commit; REM Delete system stats values over 3 days old REM which are not in the keep list delete from stats$sysstat where snap_id in
  (select snap_id
  from stats$snapshot where snap_time < sysdate - 3) and statistic# not in
  (select statistic# from stats$sysstat_keep);
  commit; REM Delete STATS$LATCH_MISSES_SUMMARY values... delete from STATS$LATCH_MISSES_SUMMARY where snap_id in
  (select snap_id
  from stats$snapshot where snap_time < sysdate - 7); commit; REM Delete STATS$LATCH values... delete from STATS$LATCH where snap_id in
  (select snap_id
  from stats$snapshot where snap_time < sysdate - 7); commit; REM Delete stats$system_events older than 30 days REM but keep last snap of the day... delete from stats$system_event where snap_id IN
  (select snap_id
  from stats$snapshot where snap_time < sysdate - 30) and snap_id NOT in
  (select maxsnap
  from ( select trunc(snap_time), max(snap_id) maxsnap from stats$snapshot where snap_time < sysdate - 30 group by trunc(snap_time))); commit; REM Delete EVERYTHING older than 60 days... delete from perfstat.STATS$SNAPSHOT where SNAP_TIME<sysdate-60; commit; -- How about deleting all snapshots older than 2 weeks except for -- those taken at 1 PM and 7 PM? Just a thought to myself... -- Rebuild the indexes... alter index STATS$BG_EVENT_SUMMARY_PK rebuild compute statistics compress nologging; alter index STATS$BUFFER_POOL_STATS_PK rebuild compute statistics compress nologging; alter index STATS$DATABASE_INSTANCE_PK rebuild compute statistics compress nologging; alter index STATS$ENQUEUESTAT_PK rebuild compute statistics compress nologging; alter index STATS$FILESTATXS_PK rebuild compute statistics compress nologging; alter index STATS$IDLE_EVENT_PK rebuild compute statistics compress nologging; alter index STATS$LATCH_CHILDREN_PK rebuild compute statistics compress nologging; alter index STATS$LATCH_MISSES_SUMMARY_PK rebuild compute statistics compress nologging; alter index STATS$LATCH_PARENT_PK rebuild compute statistics compress nologging; alter index STATS$LATCH_PK rebuild compute statistics compress nologging; alter index STATS$LEVEL_DESCRIPTION_PK rebuild compute statistics compress nologging; alter index STATS$LIBRARYCACHE_PK rebuild compute statistics compress nologging; alter index STATS$PARAMETER_PK rebuild compute statistics compress nologging; alter index STATS$ROLLSTAT_PK rebuild compute statistics compress nologging; alter index STATS$ROWCACHE_SUMMARY_PK rebuild compute statistics compress nologging; alter index STATS$SESSION_EVENT_PK rebuild compute statistics compress nologging; alter index STATS$SESSTAT_PK rebuild compute statistics compress nologging; alter index STATS$SGASTAT_U rebuild compute statistics compress nologging; alter index STATS$SGA_PK rebuild compute statistics compress nologging; alter index STATS$SNAPSHOT_PK rebuild compute statistics compress nologging; alter index STATS$SQLTEXT_PK rebuild compute statistics compress nologging; alter index STATS$SQL_STATISTICS_PK rebuild compute statistics compress nologging; alter index STATS$SQL_SUMMARY_PK rebuild compute statistics compress nologging; alter index STATS$STATSPACK_PARAMETER_PK rebuild compute statistics compress nologging; alter index STATS$SYSSTAT_PK rebuild compute statistics compress nologging; alter index STATS$SYSTEM_EVENT_PK rebuild compute statistics compress nologging; alter index STATS$TEMPSTATXS_PK rebuild compute statistics compress nologging; alter index STATS$WAITSTAT_PK rebuild compute statistics compress nologging;

-----Original Message-----
Sent: Tuesday, August 06, 2002 9:39 AM
To: Multiple recipients of list ORACLE-L

Steve

    So . . . are you going to share the details? Which tables and what order to delete rows? How often are you generating statistics? Are you using the Oracle-supplied "statsauto" command? What are you using to generate reports? What are your goals for the continual monitoring? Thanks for any details you are willing to share with the group.

Dennis Williams
DBA
Lifetouch, Inc.
dwilliams_at_lifetouch.com

-----Original Message-----
Sent: Tuesday, August 06, 2002 10:19 AM
To: Multiple recipients of list ORACLE-L

I reverse engineered the statspack data model. Using this I just delete rows from specific tables and maintain different retention times based some perceived need. You'll want to monitor and rebuild the indexes often. To keep statspack data maintenance from becoming a resource spike I just delete data daily and rebuild indexes weekly.

Steve Orr

-----Original Message-----
Sent: Tuesday, August 06, 2002 8:28 AM
To: Multiple recipients of list ORACLE-L

Lee - Just in case you were recalling a comment that I made, I'll repeat it. To clear some or all STATSPACK statistics, I have had good luck deleting from STATS$SNAPSHOT. It appears that the other STATSPACK tables populated by taking snapshots are connected by relations such that deleting from STATS$SNAPSHOT also cleans up those tables. I have also had success deleting a range of snapshots. You might want to do this to keep the more recent snapshots while clearing older snapshots. A word of warning: Because so many tables are related to STATS$SNAPSHOT, deleting can be VERY slow. On my system it seems to average about a row/minute. Dennis Williams
DBA
Lifetouch, Inc.
dwilliams_at_lifetouch.com

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Orr, Steve
  INET: sorr_at_rightnow.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).