Re: Waiting sql

From: Tanel Poder <tanel_at_tanelpoder.com>
Date: Fri, 26 Apr 2013 20:47:12 +0300
Message-ID: <CAMHX9J+6XgG+tXN=O-br=wunvDjo=Q6zD+c=OmHRNDqmO6UpWQ_at_mail.gmail.com>



Note that SQL Monitor doesn't really kick in if all that a query (DML) does is waiting for some lock (not even when using the MONITOR hint). SQL Monitor is awesome for monitoring (slowly) progressing queries, but not so much for hangs and other lower-level conditions (waits for library cache locks).
As a starting point I usually just sample v$session.state + event a couple of times or run snapper.

P.S. SQL Monitoring infrastructure is still my top favorite feature of Oracle...

-- 
*Tanel Poder*
Enkitec (The Exadata Experts)
Training <http://blog.tanelpoder.com/seminar/> |
Troubleshooting<http://blog.tanelpoder.com/>
 | Exadata<http://www.amazon.com/Expert-Oracle-Exadata-Apress/dp/1430233923>
 | Voicee App <http://voic.ee/>



On Fri, Apr 26, 2013 at 5:01 PM, Job Miller <jobmiller_at_yahoo.com> wrote:


> You don't say which version you are using, but this is exactly what SQL
> monitor - 11g (built in) is for.
>
> Anything that runs longer than 5 seconds is tracked automatically. No
> need to bother with tracing.
>
>
> http://www.oracle.com/technetwork/database/focus-areas/manageability/sqlmonitor-084401.html
>
> Real-Time SQL Monitoring, introduced in Oracle Database 11g, provides a
> very effective way to identify run-time performance problems with resource
> intensive long-running and parallel SQL statements.
> Interactive Enterprise Manager screens display details of SQL execution
> using new, fine-grained SQL statistic that are tracked out-of-the-box
> with no performance penalty to production systems. Statistics at each
> step of the execution plan are tracked by key performance metrics,
> including elapsed time, CPU time, number of reads and writes, I/O wait
> time and various other wait times. This allows DBAs to analyze SQL
> execution more deeply than previously possible and decide on the most
> appropriate tuning strategies for monitored SQL statements.
>
> ----
>
-- http://www.freelists.org/webpage/oracle-l
Received on Fri Apr 26 2013 - 19:47:12 CEST

Original text of this message