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). - Kyle http://dboptimizer.com On Sat, Jun 11, 2011 at 8:08 AM, Wolfgang Breitling <breitliw@xxxxxxxxxxxxx>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 > > > >