RE: Lost PERFSTAT bridge scripts Found it

  • From: Wolfson Larry - lwolfs <lawrence.wolfson@xxxxxxxxxx>
  • To: "'oracle-l@xxxxxxxxxxxxx'" <oracle-l@xxxxxxxxxxxxx>
  • Date: Thu, 29 Jul 2004 14:49:35 -0500

Guess I didn't get specific enough on Google the first time.

        Sorry

http://www.quest-pipelines.com/pipelines/dba/tips03.htm#january

January's Tip of the Month

Automatic Statspack Snapshots at Shutdown and Startup
Compliments of Darryl Hurley, Pipeline SYSOP (dhurley@xxxxxxxxxx)

Oracle?s Statspack utility provides a straightforward method of monitoring
database performance statistics. The process is simple; take interval
snapshots of performance indicators and then run reports to see how much the
indicators have changed during the interval(s).

Problems arise when intervals span an Oracle shutdown because comparing
interval values across them is illogical. Here?s an example:

    10:00 PM Statspack Snapshot #33 shows Physical Reads = 100000
    10:15 PM Database Shutdown
    10:20 PM Database Restarted
    11:00 PM Statspack Snapshot #34 shows Physical Reads = 100 

At this point a StatsPack Report comparing snapshot #33 to snapshot #34
would claim that ?99900 physical reads had occurred. Actually the report
would begin with this self-explanatory text:

ERROR: Snapshots chosen span an instance shutdown: RESULTS ARE INVALID

It?s 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.
It?s easily done with BEFORE-SHUTDOWN and AFTER-STARTUP triggers.

Here are the steps to make it happen.

   1. Grant CREATE TRIGGER and ADMINISTER DATABASE TRIGGER privileges to the
Statspack user (PERFSTAT).
       
   2. Execute the following to create the necessary triggers in the
Statspack account.

CREATE OR REPLACE TRIGGER statspack_at_startup
AFTER STARTUP
ON DATABASE
BEGIN
  STATSPACK.SNAP( I_UCOMMENT => 'Startup' );
END;
/
CREATE OR REPLACE TRIGGER statspack_at_shutdown
BEFORE SHUTDOWN
ON DATABASE
BEGIN
  STATSPACK.SNAP( I_UCOMMENT => 'Shutdown' );
END;
/

Now whenever the database is shutdown normally or started up, a Statspack
snapshot will be taken.

Update to Tip (8/15/03):

Performing StatsPack snapshots in startup and shutdown triggers in Oracle 9
may produce this error:

ORA-00604: error occurred at recursive SQL level 1
ORA-04092: cannot COMMIT in a trigger
ORA-06512: at "PERFSTAT.STATSPACK", line 2676
ORA-06512: at "PERFSTAT.STATSPACK", line 91 
ORA-06512: at line 2

This is strange because after-startup and before-shutdown triggers are
supposed to be autonomous transactions by default. Thus they should not care
if a commit occurs within.

Modifying the triggers as shown below ensures use of an autonomous
transaction to work around the problem.

CREATE OR REPLACE TRIGGER after_startup
AFTER STARTUP
ON DATABASE
DECLARE
  PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
  statspack.snap;
END;
/

-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx]On Behalf Of Wolfson Larry -
lwolfs
Sent: Thursday, July 29, 2004 1:31 PM
To: 'oracle-l@xxxxxxxxxxxxx'
Subject: Lost PERFSTAT bridge scripts


Last week I found a link to startup and shutdown triggers that generated
snaps so Perfstat could keep track of info over a shutdown.

        Thought I saved it but............... dang!

        Anyone here know what I'm referring to?

        I thought I got the link from here.


        Thanks
        Larry   


**********************************************************************
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@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to:  oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------

Other related posts: