FAQ
What monitoring tools are you using to detect performance degradation and to alert in real time for an SQL execution?

For example, if a SQL normally takes 5 minutes to run and now runs in 60 minutes, there should be a alert at some time thereafter.

Does such a tool exists?

Thanks for sharing.

Michael Dinh

NOTICE OF CONFIDENTIALITY - This material is intended for the use of the individual or entity to which it is addressed, and may contain information that is privileged, confidential and exempt from disclosure under applicable laws. BE FURTHER ADVISED THAT THIS EMAIL MAY CONTAIN PROTECTED HEALTH INFORMATION (PHI). BY ACCEPTING THIS MESSAGE, YOU ACKNOWLEDGE THE FOREGOING, AND AGREE AS FOLLOWS: YOU AGREE TO NOT DISCLOSE TO ANY THIRD PARTY ANY PHI CONTAINED HEREIN, EXCEPT AS EXPRESSLY PERMITTED AND ONLY TO THE EXTENT NECESSARY TO PERFORM YOUR OBLIGATIONS RELATING TO THE RECEIPT OF THIS MESSAGE. If the reader of this email (and attachments) is not the intended recipient, you are hereby notified that any dissemination, distribution or copying of this communication is strictly prohibited. Please notify the sender of the error and delete the e-mail you received. Thank you.

Search Discussions

  • David Roberts at Jun 11, 2011 at 11:43 am
    Obviously, there is internally, in 11g, adaptive cursor sharing.

    This (AFAIK) only tackles performance issues with SQL containing bind
    variables and doesn't raise an alert, although I suppose it would be trivial
    to monitor and alert on the basis of plans that had been stable and had
    suddenly started to generate multiple plans, assuming that the query
    contained bind variables.

    Beyond that, I can see plans changing (predominantly) due to:

    1. Upgrades
    2. Statistics changing (From Inaccurate to accurate)
    3. Real statistics changing (Table changing in size)

    Perhaps should be tackled with RAT.
    This shouldn't be happening without the DBA already being aware of a
    problem.
    Possibly this would be useful, but it's still reactive, you would have
    already had to observe a degradation in performance before this alert could
    be raised.

    So in the third scenario, there would be some short term value in a system
    warning you that a component of a batch job had changed plan before the
    Batch job had completed, but I would suggest that this would be a hard sell
    for a third party tool vendor, so if such a tool exists, I suspect that it
    would be open source.

    D.
    On Fri, Jun 10, 2011 at 9:17 PM, Michael Dinh wrote:

    What monitoring tools are you using to detect performance degradation and
    to alert in real time for an SQL execution?



    For example, if a SQL normally takes 5 minutes to run and now runs in 60
    minutes, there should be a alert at some time thereafter.



    Does such a tool exists?



    Thanks for sharing.



    Michael Dinh



    NOTICE OF CONFIDENTIALITY - This material is intended for the use of the
    individual or entity to which it is addressed, and may contain information
    that is privileged, confidential and exempt from disclosure under applicable
    laws. BE FURTHER ADVISED THAT THIS EMAIL MAY CONTAIN PROTECTED HEALTH
    INFORMATION (PHI). BY ACCEPTING THIS MESSAGE, YOU ACKNOWLEDGE THE FOREGOING,
    AND AGREE AS FOLLOWS: YOU AGREE TO NOT DISCLOSE TO ANY THIRD PARTY ANY PHI
    CONTAINED HEREIN, EXCEPT AS EXPRESSLY PERMITTED AND ONLY TO THE EXTENT
    NECESSARY TO PERFORM YOUR OBLIGATIONS RELATING TO THE RECEIPT OF THIS
    MESSAGE. If the reader of this email (and attachments) is not the intended
    recipient, you are hereby notified that any dissemination, distribution or
    copying of this communication is strictly prohibited. Please notify the
    sender of the error and delete the e-mail you received. Thank you.
    --
    http://www.freelists.org/webpage/oracle-l
  • Denis at Jun 11, 2011 at 1:15 pm

    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
  • Wolfgang Breitling at Jun 11, 2011 at 3:08 pm
    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-l
  • Michael Dinh at Jun 11, 2011 at 3:14 pm
    Thank you for your response.

    This is my no means to self promote my blog - http://mdinh.wordpress.com/2011/06/11/instrumentation-versus-monitoring/

    But would instrumentation be better than monitoring?

    From: Wolfgang Breitling [breitliw_at_centrexcc.com]
    Sent: Saturday, June 11, 2011 8:08 AM
    To: denis.sun_at_yahoo.com
    Cc: oracle-l@freelists.org; Michael Dinh
    Subject: Re: Oracle Monitoring Tool

    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
  • Wolfgang Breitling at Jun 11, 2011 at 5:47 pm
    My apologies to Dion. I managed to make two fat finger mistakes within just a few words.
    On 2011-06-11, at 9:08 AM, Wolfgang Breitling wrote:

    as Dian Cho ( http://dioncho.wordpress.com ) in one of his blogs ( I think it was Dioan,
    --
    http://www.freelists.org/webpage/oracle-l
  • kyle Hailey at Jun 11, 2011 at 6:42 pm
    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
    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-l

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouporacle-l @
categoriesoracle
postedJun 10, '11 at 8:17p
activeJun 11, '11 at 6:42p
posts7
users5
websiteoracle.com

People

Translate

site design / logo © 2022 Grokbase