RE: Oracle Monitoring Tool

From: Michael Dinh <mdinh_at_XIFIN.Com>
Date: Sat, 11 Jun 2011 08:14:14 -0700
Message-ID: <D29F9902E534D5478F2E83FD6A44B3063B038A7A3B_at_mail02.mba.xifin.com>

Thank you for your response.

This is my no means to self promote my blog - http://mdinh.wordpress.com/2011/06/11/instrumentation-versus-monitoring/

But would instrumentation be better than monitoring?

---

From: Wolfgang Breitling [breitliw_at_centrexcc.com] Sent: Saturday, June 11, 2011 8:08 AM
To: denis.sun_at_yahoo.com
Cc: oracle-l_at_freelists.org; Michael Dinh Subject: Re: Oracle Monitoring Tool

You don't need v$actice_session_history - and the cost of the diagnostics pack license - to do that. Simply monitoring v$session will get you almost there. You can see there for how long a sql has been running. I said "almost" because, as Dian Cho ( http://dioncho.wordpress.com ) in one of his blogs ( I think it was Dioan, couldni't find ir just now ) showed, v$session.last_call_et gets reset for each fetch so the sql could be running much longer than this number ( in seconds ) indicates. I have not checked if v$sql ( or v$sqlstats in 10g+ ) gets updated continuously or only at the end of the sql. In any case, it will only give you averages if executions i > 0. v$sql_plan_statistics.last_elapsed_time is only updated if rowsource_statistics are enabled so that is not useful.

Then you only need an array with accepted sql elapsed times by sql_id to compare against.

All that can easily be set up with a simple perl script.

On 2011-06-11, at 7:15 AM, Denis wrote:

>> What monitoring tools are you using to detect performance degradation and to alert in real time for an SQL execution?

In 11g, we may be able to query v$active_session_history to get some info, I wrote a sql inspired from the blog: http://dboptimizer.com/2011/05/04/sql-execution-times-from-ash/

Below sql check whether there are any sqls with execution time > 10s from a particuler user. It includes sql already finished, but compare tm and ela_tm may indicate a still running sql:

SELECT sql_id,

               sql_exec_id,
               sql_exec_start,
               MAX(tm) tm,
               (sysdate-sql_exec_start) * 3600*24 ela_tm
        FROM   (SELECT sql_id,
                       sql_exec_id,
                       sql_exec_start,
                       ( ( Cast(sample_time AS DATE) ) -
                         ( Cast(sql_exec_start AS DATE) ) ) * ( 3600 * 24 ) tm
                FROM   v$active_session_history
                WHERE  sql_exec_id IS NOT NULL
                  -- and sql_id='5m4rcp6r2xpp2'
                   and user_id=93
               )
        GROUP  BY sql_id,
                  sql_exec_id,
                  sql_exec_start
        having max(tm)  > 10

order by sql_exec_start;

Sampel output:
SQL_ID SQL_EXEC_ID SQL_EXEC_START TM ELA_TM

------------- ----------- ------------------- ---------- ----------
7v6j289tr19j5    16777216 2011-06-10 21:00:42         84      57424
drpvgw9qkkutx    16777216 2011-06-11 12:39:02         41       1124
drpvgw9qkkutx    16777217 2011-06-11 12:45:01         40        765
0x147cyxmt15d    16777216 2011-06-11 12:49:02         39        524
0x147cyxmt15d    16777217 2011-06-11 12:50:20         40        446
0x147cyxmt15d    16777218 2011-06-11 12:57:10         36         36

Yu (Denis) Sun
Oracle DBA

--
http://www.freelists.org/webpage/oracle-l

Received on Sat Jun 11 2011 - 10:14:14 CDT