Re: Script to automate AWR reports (not snapshots)
Date: Thu, 4 Mar 2010 06:59:57 +0800
What information are you trying to track in AWR report? What metrics do you use or does your management use to evaluate performance? Performance can be evaluated many different ways with AWR but the biggest issue is that AWR report just averages out the metrics in the report period, which by default is an hour. Running AWR faster than once an hour was deemed too resource intensive to release as the default.
I find it much easier to use ASH for performance reporting. ASH tracks
the load in the database and can be rolled up in an aggregated metric called
Average Active Sessions (AAS). AAS can be broken down into it's components
such as CPU demand, IO wait time or other wait times such as application
(locking) , configuration (that kind of stuff the DBA is responsible for) or
concurrency bottlenecks to easily see not only what the load was but what
kind of load was on the database.
ASH and AAS can most easily seen in OEM's performance page. The performance page works best for viewing the current activity including the last hour as well but can be difficult to use for viewing a time frame over the past day or week. For that reason I've been working on DB Optimizer<http://sites.google.com/site/embtdbo/wait-event-documentation/database-tuning> as well has putting together some SQL
scripts<http://sites.google.com/site/embtdbo/wait-event-documentation/ash---active-session-history#TOC-Data-Mining-Scripts>to help quickly browse the ASH data and see any periods that might have had
On Thu, Mar 4, 2010 at 6:02 AM, Dan Norris <dannorris_at_dannorris.com> wrote:
> Read the comments in the top of the $OH/rdbms/admin/awrrpti.sql script. It
> tells you exactly how to set the proper variables so that you can call it
> without prompting. From there, you can automate it as needed.
> I've used this method to run AWR reports for each instance of a RAC
> environment from a single location (instead of having to login to each
> instance) because instance id is a variable you can pass in.
> On Wed, Mar 3, 2010 at 2:54 PM, Sandra Becker <sbecker6925_at_gmail.com>wrote:
>> Platform: IBM z9
>> OS: SUSE10
>> Oracle: 10gR2
>> Management is under the mistaken impression that the performance problems
>> we are experiencing on a daily basis ALWAYS happen at the same time. To
>> that end, they want me to set up a shell script that will run an AWR report
>> daily for each of the two time periods they believe the problems are
>> occuring. I haven't been able to convince them we should run reports for
>> the times we KNOW we had problems. I also haven't been able to locate a
>> script to automatically run reports for the specific time periods--I'm
>> probably not entering the right search criteria. Does anyone have such a
>> script or can point me in the right direction?
>> Transzap, Inc.