Re: Oracle Monitoring Tool
Date: Sat, 11 Jun 2011 11:42:04 -0700
Message-ID: <BANLkTi=1XxxZTCHaw5RR_2f7m51WY0du0Q_at_mail.gmail.com>
you can also collect your own ASH with SASH, simulated ASH for free on Oracle 7-11g, see
http://ashmasters.com/ash-simulation/
SASH collects fixed_table_sequence. The field fixed_table_sequence as I
understand it only changes on new calls, which AFAIK, correlates to each sql
execution (though I wonder if it has the same issues with fetch as elapsed
time), thus fixed_table_sequence can serve as the SQL_EXEC_ID. Using
fixed_table_sequence for SQL_EXEC_ID you can do the same analysis as with
SQL_EXEC_ID in ASH in 11g on SASH for free on Oracle version 7-11g as well
as on standard edition.
See comment in
http://dboptimizer.com/2011/05/04/sql-execution-times-from-ash/
Using the same ideas you could mine SASH for the average execution times of each SQL_ID and then alert on any query that exceeded the average by X seconds ( combination of seconds and % slower).
On Sat, Jun 11, 2011 at 8:08 AM, Wolfgang Breitling <breitliw_at_centrexcc.com>wrote:
> 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-lReceived on Sat Jun 11 2011 - 13:42:04 CDT