FAQ
I've got a report I email myself everyday that is a list of the top 10 most frequently executed sql from the previous day according to awr. This has turned up a piece of sql that, according to dba_hist_sqlstat, was/is being executed millions and millions of times each day. I tracked this sql down to user defined function that, until recently was used in several views. I've factored out those function calls and everyone is enjoying the performance benefits. However, the SQL is still showing up in the daily report. The odd thing is that when I go looking for entries in dba_hist_active_sess_history I don't see a single entry with the same module. Given my lack of sanity last Friday with execution plans, I'm hesitant to ask for help before I stare at this for a few more hours but I really don't understand how this can be. I actually trust dba_hist_active_session_history because there is on view left that needs to be re-written but I really don't understand the discrepancy in data between the various awr tables and v$ views.

select sql_id, module, sum(executions_delta)

from dba_hist_sqlstat
where module = 'MV Refresh process' and

action = 'VW_PROJECT_STATUS' and
snap_id IN (select snap_id from dba_hist_snapshot where end_interval_time BETWEEN TRUNC(SYSDATE -1) AND TRUNC(SYSDATE))

group by sql_id, module;

SQL_ID MODULE SUM(EXECUTIONS_DELTA)
------------- ---------------------------- ---------------------
3r6urw0m13rbn MV Refresh process 6692677

select distinct program, module, action
from DBA_HIST_ACTIVE_SESS_HISTORY
where sql_id = '3r6urw0m13rbn' and

snap_id IN (select snap_id from dba_hist_snapshot where end_interval_time BETWEEN TRUNC(SYSDATE -1) AND TRUNC(SYSDATE));

MODULE SQL_ID
------------------------------ -------------
Disco10, PPS_QUERY 3r6urw0m13rbn
MSACCESS.EXE 3r6urw0m13rbn
3r6urw0m13rbn
DBMS_SCHEDULER 3r6urw0m13rbn

v$active_session_history doesn't turn up anything either and that goes back to yesterday as of 11:45am but according to awr this sql has been executed more than 2.7 million times in the last 90 minutes or so. (snapshots taken every 30 minutes).

select module, sum(executions_delta)

from dba_hist_sqlstat
where sql_id = '3r6urw0m13rbn' and

snap_id IN (select snap_id from dba_hist_snapshot where end_interval_time >= SYSDATE - 1/24)
group by sql_id, module;

MODULE SUM(EXECUTIONS_DELTA)
------------------------- ---------------------

MV Refresh process 2727010

Anybody got anything?

CONFIDENTIALITY NOTICE:

This message 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 law. If the reader of this message is not the intended recipient or the employee or agent responsible for delivering this message to the intended recipient, you are hereby notified that any dissemination, distribution or copying of this communication is strictly prohibited. If you have received this communication in error, please notify us immediately by email reply.

Search Discussions

  • Jeffrey Beckstrom at Mar 2, 2011 at 3:32 pm
    Could it be that it executes so fast that it does not go into the History views?


    Jeffrey Beckstrom
    Database Administrator
    Greater Cleveland Regional Transit Authority
    1240 W. 6th Street
    Cleveland, Ohio 44113
    "Stephens, Chris" 3/2/11 10:26 AM >>>
    I’ve got a report I email myself everyday that is a list of the top 10 most frequently executed sql from the previous day according to awr. This has turned up a piece of sql that, according to dba_hist_sqlstat, was/is being executed millions and millions of times each day. I tracked this sql down to user defined function that, until recently was used in several views. I’ve factored out those function calls and everyone is enjoying the performance benefits. However, the SQL is still showing up in the daily report. The odd thing is that when I go looking for entries in dba_hist_active_sess_history I don’t see a single entry with the same module. Given my lack of sanity last Friday with execution plans, I’m hesitant to ask for help before I stare at this for a few more hours but I really don’t understand how this can be. I actually trust dba_hist_active_session_history because there is on view left that needs to be re-written but I really don’t understand the discrepancy in data between the various awr tables and v$ views.


    select sql_id, module, sum(executions_delta)

    from dba_hist_sqlstat
    where module = 'MV Refresh process' and

    action = 'VW_PROJECT_STATUS' and
    snap_id IN (select snap_id from dba_hist_snapshot where end_interval_time BETWEEN TRUNC(SYSDATE -1) AND TRUNC(SYSDATE))

    group by sql_id, module;


    SQL_ID MODULE SUM(EXECUTIONS_DELTA)
    ------------- ---------------------------- ---------------------
    3r6urw0m13rbn MV Refresh process 6692677

    select distinct program, module, action
    from DBA_HIST_ACTIVE_SESS_HISTORY
    where sql_id = '3r6urw0m13rbn' and

    snap_id IN (select snap_id from dba_hist_snapshot where end_interval_time BETWEEN TRUNC(SYSDATE -1) AND TRUNC(SYSDATE));


    MODULE SQL_ID
    ------------------------------ -------------
    Disco10, PPS_QUERY 3r6urw0m13rbn
    MSACCESS.EXE 3r6urw0m13rbn
    3r6urw0m13rbn
    DBMS_SCHEDULER 3r6urw0m13rbn

    v$active_session_history doesn’t turn up anything either and that goes back to yesterday as of 11:45am but according to awr this sql has been executed more than 2.7 million times in the last 90 minutes or so. (snapshots taken every 30 minutes).


    select module, sum(executions_delta)

    from dba_hist_sqlstat
    where sql_id = '3r6urw0m13rbn' and

    snap_id IN (select snap_id from dba_hist_snapshot where end_interval_time >= SYSDATE - 1/24)
    group by sql_id, module;


    MODULE SUM(EXECUTIONS_DELTA)
    ------------------------- ---------------------

    MV Refresh process 2727010


    Anybody got anything?

    CONFIDENTIALITY NOTICE:

    This message 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 law. If the reader of this message is not the intended recipient or the employee or agent responsible for delivering this message to the intended recipient, you are hereby notified that any dissemination, distribution or copying of this communication is strictly prohibited. If you have received this communication in error, please notify us immediately by email reply.
  • Stephens, Chris at Mar 2, 2011 at 3:57 pm
    Absolutely but why don't the modules sync up between different awr tables and v$active_session_history?

    From: Dominic.Brooks_at_barclayscapital.com
    Sent: Wednesday, March 02, 2011 9:56 AM
    To: Stephens, Chris; oracle-l@freelists.org
    Subject: RE: Querying awr to hunt down source of sql executions

    Isn't it that this recording of module comes from v$sql, ultimately, and so it's whatever module was executing when the statement was parsed?

    From: oracle-l-bounce_at_freelists.org On Behalf Of Stephens, Chris
    Sent: 02 March 2011 15:26
    To: oracle-l@freelists.org
    Subject: Querying awr to hunt down source of sql executions

    I've got a report I email myself everyday that is a list of the top 10 most frequently executed sql from the previous day according to awr. This has turned up a piece of sql that, according to dba_hist_sqlstat, was/is being executed millions and millions of times each day. I tracked this sql down to user defined function that, until recently was used in several views. I've factored out those function calls and everyone is enjoying the performance benefits. However, the SQL is still showing up in the daily report. The odd thing is that when I go looking for entries in dba_hist_active_sess_history I don't see a single entry with the same module. Given my lack of sanity last Friday with execution plans, I'm hesitant to ask for help before I stare at this for a few more hours but I really don't understand how this can be. I actually trust dba_hist_active_session_history because there is on view left that needs to be re-written but I really don't understand the discrepancy in data between the various awr tables and v$ views.

    select sql_id, module, sum(executions_delta)

    from dba_hist_sqlstat
    where module = 'MV Refresh process' and

    action = 'VW_PROJECT_STATUS' and
    snap_id IN (select snap_id from dba_hist_snapshot where end_interval_time BETWEEN TRUNC(SYSDATE -1) AND TRUNC(SYSDATE))

    group by sql_id, module;

    SQL_ID MODULE SUM(EXECUTIONS_DELTA)
    ------------- ---------------------------- ---------------------
    3r6urw0m13rbn MV Refresh process 6692677

    select distinct program, module, action
    from DBA_HIST_ACTIVE_SESS_HISTORY
    where sql_id = '3r6urw0m13rbn' and

    snap_id IN (select snap_id from dba_hist_snapshot where end_interval_time BETWEEN TRUNC(SYSDATE -1) AND TRUNC(SYSDATE));

    MODULE SQL_ID
    ------------------------------ -------------
    Disco10, PPS_QUERY 3r6urw0m13rbn
    MSACCESS.EXE 3r6urw0m13rbn
    3r6urw0m13rbn
    DBMS_SCHEDULER 3r6urw0m13rbn

    v$active_session_history doesn't turn up anything either and that goes back to yesterday as of 11:45am but according to awr this sql has been executed more than 2.7 million times in the last 90 minutes or so. (snapshots taken every 30 minutes).

    select module, sum(executions_delta)

    from dba_hist_sqlstat
    where sql_id = '3r6urw0m13rbn' and

    snap_id IN (select snap_id from dba_hist_snapshot where end_interval_time >= SYSDATE - 1/24)
    group by sql_id, module;

    MODULE SUM(EXECUTIONS_DELTA)
    ------------------------- ---------------------

    MV Refresh process 2727010

    Anybody got anything?

    CONFIDENTIALITY NOTICE:

    This message 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 law. If the reader of this message is not the intended recipient or the employee or agent responsible for delivering this message to the intended recipient, you are hereby notified that any dissemination, distribution or copying of this communication is strictly prohibited. If you have received this communication in error, please notify us immediately by email reply.

    This e-mail may contain information that is confidential, privileged or otherwise protected from disclosure. If you are not an intended recipient of this e-mail, do not duplicate or redistribute it by any means. Please delete it and any attachments and notify the sender that you have received it in error. Unless specifically indicated, this e-mail is not an offer to buy or sell or a solicitation to buy or sell any securities, investment products or other financial product or service, an official confirmation of any transaction, or an official statement of Barclays. Any views or opinions presented are solely those of the author and do not necessarily represent those of Barclays. This e-mail is subject to terms available at the following link: www.barcap.com/emaildisclaimer<http://www.barcap.com/emaildisclaimer>. By messaging with Barclays you consent to the foregoing. Barclays Capital is the investment banking division of Barclays Bank PLC, a company registered in England (number 1026167) with its registered office at 1 Churchill Place, London, E14 5HP. This email may relate to or be sent from other members of the Barclays Group.

    CONFIDENTIALITY NOTICE:

    This message 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 law. If the reader of this message is not the intended recipient or the employee or agent responsible for delivering this message to the intended recipient, you are hereby notified that any dissemination, distribution or copying of this communication is strictly prohibited. If you have received this communication in error, please notify us immediately by email reply.
  • Tanel Poder at Mar 2, 2011 at 4:31 pm
    V$SQL% views show the MODULE and ACTION of that session who *hard parsed* the
    query. And it's stored only once - for parent cursor and not for children.

    So all subsequent executions of any child of that parent cursor will still
    show the original parsing session's module & action, regardless of what
    these attributes are for executing sessions....

    ASH doesn't have that problem as it doesn't take the module/action from
    v$sql cursor but from executing session's attributes.

    That's only one of the reasons... Another may be that recursive calls
    (Calling pl/sql from sql etc) may not set or clear their module/action and
    even sql_id attributes properly when context switching from SQL to PL/SQL
    and back... these are due to bugs or design limitations... and that's why
    there are some new columns like TOP_LEVEL_SQL_ID in addition to just SQL_ID
    to differentiate between the call which application made vs. something
    executed recursively...

    P.S. To Exadata geeks out there - our book can be purchased as an
    Alpha/Draft PDF from Apress already!
    http://blog.tanelpoder.com/2011/03/02/expert-oracle-exadata-book-alpha-chapters-available-for-purchase/

    --
    Tanel Poder
    Oracle Troubleshooting Blog - http://blog.tanelpoder.com
    Online Troubleshooting Seminars -
    http://tech.e2sn.com/oracle-training-seminars
    On Wed, Mar 2, 2011 at 5:57 PM, Stephens, Chris wrote:

    Absolutely but why don�t the modules sync up between different awr tables
    and v$active_session_history?



    *From:* Dominic.Brooks_at_barclayscapital.com [mailto:
    Dominic.Brooks@barclayscapital.com]
    *Sent:* Wednesday, March 02, 2011 9:56 AM
    *To:* Stephens, Chris; oracle-l@freelists.org
    *Subject:* RE: Querying awr to hunt down source of sql executions



    Isn�t it that this recording of module comes from v$sql, ultimately, and so
    it�s whatever module was executing when the statement was parsed?

    --
    http://www.freelists.org/webpage/oracle-l
  • Tanel Poder at Mar 2, 2011 at 5:41 pm
    Hi,

    V$SQL% views show only SQL cursors and anonymous PL/SQL cursors, but no
    other objects, you'll need to query X$KGLOB directly (SQL_ID was KGLOBT03 or
    T13 column IIRC).

    --
    Tanel Poder
    Oracle Troubleshooting Blog - http://blog.tanelpoder.com
    Online Troubleshooting Seminars -
    http://tech.e2sn.com/oracle-training-seminars
    On Wed, Mar 2, 2011 at 6:55 PM, Stephens, Chris wrote:

    Possibly this falls into the �bugs or design limitations� since I don�t
    see anything in any of the v$sql* views with that particular module/sql_id.
    I�ll keep an eye on the v$sql* views and incoming snapshots to see if
    possibly the sql just isn�t in the shared pool at the moment.



    This definitely sheds some light on my understanding of awr though.
    Thanks!

    --
    http://www.freelists.org/webpage/oracle-l

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouporacle-l @
categoriesoracle
postedMar 2, '11 at 3:26p
activeMar 2, '11 at 5:41p
posts5
users3
websiteoracle.com

People

Translate

site design / logo © 2022 Grokbase