We know for a fact that if some wait events appear on an Oracle database, it
will slow down and users will call. These are the root cause of almost every
performance issues I have tackled and this is how we approach Oracle
performance tuning anyway.
Database performance degrades if these events appear or have high elapsed
times. Events such as mutex/latch contention, severe enqueuer waits, log file
syncs exceeding 300 ms and db file sequential reads over 600 ms.
People do not typically examine AWR/ASH reports routinely to see these
anomalies and certainly not for proactive purposes. We are only talking a
subset of ‘nasty’ events.
There are third party tools that have this feature built it. It utilizes AWR
data to construct its monitors.
----------------------------------------
Thanks
From: Kellyn Pot'Vin-Gorman
Sent: Wednesday, February 24, 2016 2:09 PM
To: fmhabash@xxxxxxxxx
Cc: Courtney Llamas; Oracle-L Group
Subject: Re: How to Monitor Wait Event Average Elapsed Time Using EMCC12c
The Enterprise Manager product is a self-service product. There are a ton of
features and products that Oracle works very hard to keep as "light" as
possible. The important monitoring features may differ drastically from one
customer to another. What may be a top ten for you, even if it were to be
disabled, may be far down on the list for another customer, so we've ensured
that you have the functionality available to ensure you can monitor almost
anything imaginable.
Hope this helps,
Kellyn Pot'Vin-Gorman
Consulting Member of Technical Staff, SCP
Oracle
Kellyn Pot'Vin-Gorman
about.me/dbakevlar
On Wed, Feb 24, 2016 at 12:02 PM, <fmhabash@xxxxxxxxx> wrote:
Since it is not default, how are you handling scenarios where your database
latch/mutex contention goes 2000% at 150 ms proactively?
Even though possible to do through home-grown scripts, but if adaptive monitors
are suited the most for anything, it is for wait event monitoring. Having to
engineer such adaptive feature is not something clients are willing to pay for.
Do you rely on v$system_event/v$session_event?
----------------------------------------
Thanks
From: Courtney Llamas
Sent: Wednesday, February 24, 2016 1:36 PM
To: fmhabash@xxxxxxxxx; Kellyn Pot'Vin-Gorman
Cc: Oracle-L Group
Subject: RE: How to Monitor Wait Event Average Elapsed Time Using EMCC12c
You can see the available metrics in the DB Reference guide -
http://docs.oracle.com/cd/E24628_01/em.121/e25160/oracle_database.htm#EMDBM3179.
These correspond to the DB wait event classes
http://docs.oracle.com/cd/E11882_01/server.112/e40402/waitevents001.htm#BGGHJGII
Setting thresholds on each specific wait would be very time consuming and error
prone when new versions/waits are introduced.
--
- Courtney
Courtney Llamas | Consulting Member of Technical Staff
Phone: +2814108258 | Mobile: +8324720596
Oracle Strategic Customer Program
Oracle
Oracle is committed to developing practices and products that help protect the
environment
From: fmhabash@xxxxxxxxx [mailto:fmhabash@xxxxxxxxx] ;
Sent: Wednesday, February 24, 2016 12:27 PM
To: Kellyn Pot'Vin-Gorman
Cc: Oracle-L Group
Subject: RE: How to Monitor Wait Event Average Elapsed Time Using EMCC12c
I’m a bit surprised why did Oracle not include such metrics in the default
template, even if disabled.
----------------------------------------
Thanks
From: Kellyn Pot'Vin-Gorman
Sent: Wednesday, February 24, 2016 1:24 PM
To: fmhabash@xxxxxxxxx
Cc: Oracle-L Group
Subject: Re: How to Monitor Wait Event Average Elapsed Time Using EMCC12c
By the data you've provided, elapsed time per a specific wait event isn't a
default metric, but yes, you could easily create this with a metric extension.
Once you add it to a target, then use that target as the base for a template
and then you can use it elsewhere.
Hope this helps,
Kellyn
Kellyn Pot'Vin-Gorman
about.me/dbakevlar
On Wed, Feb 24, 2016 at 11:02 AM, <fmhabash@xxxxxxxxx> wrote:
I had a need to create EM monitors/alerts when a particular wait event average
elapsed time exceeds a threshold. Looking at the default template, I see wait
classes (by waiter count) and no wait event metrics.
Are wait event metrics not available in anywhere in a template?
Do I have to create a metric extension for this purpose?
----------------------------------------
Thanks