Re: Oracle Monitoring Tool

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
>
>
>
>

Other related posts: