From oracle-l-bounce@freelists.org Thu Jul 29 15:22:46 2004 Return-Path: Received: from air189.startdedicated.com (root@localhost) by orafaq.com (8.11.6/8.11.6) with ESMTP id i6TKMVr15961 for ; Thu, 29 Jul 2004 15:22:41 -0500 X-ClientAddr: 206.53.239.180 Received: from turing.freelists.org (freelists-180.iquest.net [206.53.239.180]) by air189.startdedicated.com (8.11.6/8.11.6) with ESMTP id i6TKMK615924 for ; Thu, 29 Jul 2004 15:22:30 -0500 Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 7BB4B72EE62; Thu, 29 Jul 2004 14:57:22 -0500 (EST) Received: from turing.freelists.org ([127.0.0.1]) by localhost (turing [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id 23401-92; Thu, 29 Jul 2004 14:57:22 -0500 (EST) Received: from turing (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id F0E5272D8AD; Thu, 29 Jul 2004 14:54:19 -0500 (EST) Received: with ECARTIS (v1.0.0; list oracle-l); Thu, 29 Jul 2004 14:52:37 -0500 (EST) X-Original-To: oracle-l@freelists.org Delivered-To: oracle-l@freelists.org Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id A9B4872EC1F for ; Thu, 29 Jul 2004 14:49:10 -0500 (EST) Received: from turing.freelists.org ([127.0.0.1]) by localhost (turing [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id 22478-35 for ; Thu, 29 Jul 2004 14:49:10 -0500 (EST) Received: from cwysmtp02.acxiom.com (cwysmtp02.acxiom.com [198.160.100.129]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 330C072CBEF for ; Thu, 29 Jul 2004 14:48:13 -0500 (EST) Received: from relay.Corp.Acxiom.net (unverified [139.61.199.171]) by cwysmtp02.acxiom.com (Content Technologies SMTPRS 4.3.12) with ESMTP id for ; Thu, 29 Jul 2004 15:15:30 -0500 Received: from dngmsc01.Corp.Acxiom.net (unverified [10.76.40.237]) by relay.Corp.Acxiom.net (Content Technologies SMTPRS 4.3.12) with ESMTP id for ; Thu, 29 Jul 2004 15:15:29 -0500 Received: by dngmsc01.dg.acxiom.com with Internet Mail Service (5.5.2657.72) id ; Thu, 29 Jul 2004 15:15:29 -0500 Message-ID: <433A07749711884D8032B6A0AB115262C2BCD2@conmsx07.corp.acxiom.net> From: Wolfson Larry - lwolfs To: "'oracle-l@freelists.org'" Subject: RE: Lost PERFSTAT bridge scripts Date: Thu, 29 Jul 2004 15:12:29 -0500 MIME-Version: 1.0 X-Mailer: Internet Mail Service (5.5.2657.72) Content-type: text/plain X-Virus-Scanned: by amavisd-new at freelists.org Content-Transfer-Encoding: 8bit X-archive-position: 6467 X-ecartis-version: Ecartis v1.0.0 Sender: oracle-l-bounce@freelists.org Errors-To: oracle-l-bounce@freelists.org X-original-sender: lawrence.wolfson@acxiom.com Precedence: normal Reply-To: oracle-l@freelists.org X-list: oracle-l X-Virus-Scanned: by amavisd-new at freelists.org Jared I already did but I must have been half asleep(or more) because he does say "its impossible to report across a shutdown, but it is possible to reduce the lost periods of time (10:00 to 10:15 and 10:20 to 11:00 in our example) by automatically performing snapshots before shutdown and after startup. Its easily done with BEFORE-SHUTDOWN and AFTER-STARTUP triggers." I do use a script from Tim Gorman that works very well (below) Larry COL NAME FOR A30 /********************************************************************** * File: sptrends.sql * Type: SQL*Plus script * Author: Tim Gorman (SageLogix, Inc.) * Date: 15-Jul-2003 * * Description: * Query to display "trends" for specific statistics captured by *the STATSPACK package, and display summarized totals daily and *hourly as a deviation from average. The intent is to find the *readings with the greatest positive deviation from the average *value, as these are likely to be "periods of interest" for *further research... * * Modifications: *********************************************************************/ set echo off feedback off timing off pagesize 200 lines 130 trimout on trimspool on verify off recsep off col sort0 noprint col day heading "Day" col hr heading "Hour" col value format 999,999,999,990 heading "Value" col deviation format a100 heading "Relative Deviation from Average Value" accept V_NBR_DAYS prompt "How many days of data to examine? " prompt Some useful database statistics to search upon: select rpad(' ',4,' ')||name name from v$statname where name like '%logical%' or name like '%physical%' or name like '%redo%' or name like '%sort%' or lower(name) like '%cpu%' order by 1; accept V_STATNAME prompt "What statistic? " col spoolname new_value V_SPOOLNAME noprint select replace(replace(replace(lower('&&V_STATNAME'),' ','_'),'(',''),')','') spoolname from dual; spool sptrends_&&V_SPOOLNAME clear breaks computes col value format 999,999,999,990 heading "Value" prompt prompt Daily trends for "&&V_STATNAME"... select sort0, day, value, rpad('*', decode(greatest(round(((value - (avg(value) over ()))/(avg(value) over ()))*100,-1),0), 0, 0, round(((value - (avg(value) over ()))/(avg(value) over ()))*100,-1))/2,'*') deviation from (select sort0, day, sum(value) value from (select to_char(ss.snap_time, 'YYYYMMDD') sort0, to_char(ss.snap_time, 'DD-MON') day, s.snap_id, s.name, nvl(decode(greatest(s.value, nvl(lag(s.value) over (order by s.dbid, s.instance_number, s.snap_id),0)), s.value, s.value - lag(s.value) over (order by s.dbid, s.instance_number, s.snap_id), s.value), 0) value from stats$sysstat s, stats$snapshot ss where ss.snap_id = s.snap_id and ss.dbid = s.dbid and ss.instance_number = s.instance_number and ss.snap_time between (sysdate - &&V_NBR_DAYS) and sysdate and s.name = '&&V_STATNAME') group by sort0, day) order by sort0; clear breaks computes break on day skip 1 on report col value format 999,999,990 heading "Value" prompt prompt Daily/hourly trends for "&&V_STATNAME"... select sort0, day, hr, value, rpad('*', decode(greatest(round(((value - (avg(value) over ()))/(avg(value) over ()))*100,-1),0), 0, 0, round(((value - (avg(value) over ()))/(avg(value) over ()))*100,-1))/2,'*') deviation from(select sort0, day, hr, name, sum(value) value from (select to_char(ss.snap_time, 'YYYYMMDDHH24') sort0, to_char(ss.snap_time, 'DD-MON') day, to_char(ss.snap_time, 'HH24')||':00' hr, s.snap_id, s.name, nvl(decode(greatest(s.value, nvl(lag(s.value) over (order by s.dbid, s.instance_number, s.snap_id),0)), s.value, s.value - lag(s.value) over (order by s.dbid, s.instance_number, s.snap_id), s.value), 0) value from stats$sysstat s, stats$snapshot ss where ss.snap_id = s.snap_id and ss.dbid = s.dbid and ss.instance_number = s.instance_number and ss.snap_time between (sysdate - &&V_NBR_DAYS) and sysdate and s.name = '&&V_STATNAME') group by sort0, day, hr, name) order by sort0; spool off set verify on recsep each -----Original Message----- From: oracle-l-bounce@freelists.org [mailto:oracle-l-bounce@freelists.org]On Behalf Of Jared Still Sent: Thursday, July 29, 2004 3:04 PM To: Oracle-L Freelists Subject: Re: Lost PERFSTAT bridge scripts On Thu, 2004-07-29 at 11:30, Wolfson Larry - lwolfs wrote: > Last week I found a link to startup and shutdown triggers that generated > snaps so Perfstat could keep track of info over a shutdown. > Nice idea. Please share when you find it again. DB bounces mess up my pretty response time charts. :) Jared ---------------------------------------------------------------- Please see the official ORACLE-L FAQ: http://www.orafaq.com ---------------------------------------------------------------- To unsubscribe send email to: oracle-l-request@freelists.org put 'unsubscribe' in the subject line. -- Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html ----------------------------------------------------------------- ********************************************************************** The information contained in this communication is confidential, is intended only for the use of the recipient named above, and may be legally privileged. If the reader of this message is not the intended recipient, you are hereby notified that any dissemination, distribution, or copying of this communication is strictly prohibited. If you have received this communication in error, please re-send this communication to the sender and delete the original message or any copy of it from your computer system. Thank You. ---------------------------------------------------------------- Please see the official ORACLE-L FAQ: http://www.orafaq.com ---------------------------------------------------------------- To unsubscribe send email to: oracle-l-request@freelists.org put 'unsubscribe' in the subject line. -- Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html -----------------------------------------------------------------