Re: Oracle Monitoring Tool
From: Denis <denis.sun_at_yahoo.com>
Date: Sat, 11 Jun 2011 06:15:37 -0700 (PDT)
Message-ID: <389143.62432.qm_at_web161812.mail.bf1.yahoo.com>
>> 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
Date: Sat, 11 Jun 2011 06:15:37 -0700 (PDT)
Message-ID: <389143.62432.qm_at_web161812.mail.bf1.yahoo.com>
>> 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 - 08:15:37 CDT