Re: Oracle Monitoring Tool

From: Wolfgang Breitling <breitliw_at_centrexcc.com>
Date: Sat, 11 Jun 2011 09:08:59 -0600
Message-Id: <68E70077-BE77-43D3-9EC0-B2DB164B4F52_at_centrexcc.com>

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:08:59 CDT