Automatic Statspack Snapshots at Shutdown and Startup
Compliments of Darryl Hurley, Pipeline SYSOP (dhurley@(protected))
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:
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:
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@(protected)
[mailto:oracle-l-bounce@(protected)]On Behalf Of Wolfson Larry -
lwolfs
Sent: Thursday, July 29, 2004 1:31 PM
To: 'oracle-l@(protected) '
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@(protected)
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@(protected)
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
-- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- --
