Re: Oracle Monitoring Tool
- From: Denis <denis.sun@xxxxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx
- Date: Sat, 11 Jun 2011 06:15:37 -0700 (PDT)
>> 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
Other related posts:
