Re: Querying awr to hunt down source of sql executions

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" <Chris.Stephens@xxxxxxx> 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.

Other related posts: