Re: Automated Statspack Report

From: Paul Drake <drak0nian_at_yahoo.com>
Date: 21 Jan 2004 19:49:39 -0800
Message-ID: <1ac7c7b3.0401211949.48ea925a@posting.google.com>

david.register_at_eds.com (Dave) wrote in message news:<2fc017e4.0401211204.38d74cde_at_posting.google.com>...
> Does anyone know of a script to automatically find the latest snapshot
> id's and generate a statspack report from?
>
> Thanks in advance!

finding the latest snapshot taken is quite simple. select max(snap_id) from perfstat.stats$snapshot;

here's a hint - alter the sequence owned by perfstat to NOCACHE to get sequential numbers. that allows you to make an assumption or two ...

as Brian mentions, this does not guarantee that the instance has not been cycled.

Hmm - a very good use for analytic functions in 9.2 (as they were not available in 8.1.7 Std Ed) - use the lag or lead function. I'll leave that as an exercise, here is a good old join to an inner query. Its not the most efficient method (2 full scans of stats$snapshot) but it does meet the OPs requirements, I believe.

so at minimum, you want to have 2 snapshots that have occurred since the last instance startup. grab the max(startup_time) from stats$snapshot having count(snap_id)>1.

Okay - 8.1.7.4.12 db on w2k pro:

  1 select max(st.startup_time) max_startup_time,   2 max(snap_id)-1 begin_snap, max(snap_id) end_snap   3 from (

  4         select startup_time, max(snap_id) max_snap_id
  5           from stats$snapshot
  6         having count(snap_id)>1
  7          group by startup_time) st,
  8         stats$snapshot snap

  9* where snap.startup_time = st.startup_time perfstat_at_DEV> /

MAX_START BEGIN_SNAP END_SNAP
--------- ---------- ----------
31-DEC-03 5998 5999 1 row selected.

perfstat_at_DEV> set autotrace on explain stats SP2-0158: unknown SET option "stats"
perfstat_at_DEV> set autotrace on explain statistics perfstat_at_DEV> /

MAX_START BEGIN_SNAP END_SNAP
--------- ---------- ----------
31-DEC-03 5998 5999 1 row selected.

Execution Plan

---

   0 SELECT STATEMENT Optimizer=CHOOSE (Cost=5 Card=1 Bytes=19)    1 0 SORT (AGGREGATE)

   2    1     HASH JOIN (Cost=5 Card=60 Bytes=1140)
   3    2       VIEW (Cost=3 Card=1 Bytes=9)
   4    3         FILTER
   5    4           SORT (GROUP BY) (Cost=3 Card=1 Bytes=10)
   6    5             TABLE ACCESS (FULL) OF 'STATS$SNAPSHOT' (Cost=1
Card=181 Bytes=1810)
   7    2       TABLE ACCESS (FULL) OF 'STATS$SNAPSHOT' (Cost=1

Card=181 Bytes=1810)

Statistics

---

          0  recursive calls
          8  db block gets
         80  consistent gets
          0  physical reads
          0  redo size
        502  bytes sent via SQL*Net to client
        153  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
          1  rows processed

now, if you run that from sqlplus and select those values into variables, you should be able to pass them into the spreport.sql script. You'll also want to provide a statspack report name to it. when you're all done, mail it to yourself with blat or some other utility.

hth.

Pd Received on Wed Jan 21 2004 - 21:49:39 CST