RE: Querying awr to hunt down source of sql executions

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

From: Dominic.Brooks@xxxxxxxxxxxxxxxxxxx 
[mailto:Dominic.Brooks@xxxxxxxxxxxxxxxxxxx]
Sent: Wednesday, March 02, 2011 9:56 AM
To: Stephens, Chris; oracle-l@xxxxxxxxxxxxx
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@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On 
Behalf Of Stephens, Chris
Sent: 02 March 2011 15:26
To: oracle-l@xxxxxxxxxxxxx
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.

Other related posts: