RE: Lost PERFSTAT bridge scripts Found it

From: Wolfson Larry - lwolfs <lawrence.wolfson_at_acxiom.com>
Date: Thu, 29 Jul 2004 14:49:35 -0500
Message-ID: <433A07749711884D8032B6A0AB115262C2BCD1@conmsx07.corp.acxiom.net>

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_at_mdsi.bc.ca)

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.

Grant CREATE TRIGGER and ADMINISTER DATABASE TRIGGER privileges to the Statspack user (PERFSTAT).
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_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org]On Behalf Of Wolfson Larry - lwolfs
Sent: Thursday, July 29, 2004 1:31 PM
To: 'oracle-l_at_freelists.org'
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_at_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


-----------------------------------------------------------------

----------------------------------------------------------------

Please see the official ORACLE-L FAQ: http://www.orafaq.com


----------------------------------------------------------------

To unsubscribe send email to:  oracle-l-request_at_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

Received on Thu Jul 29 2004 - 14:50:22 CDT