Re: Querying awr to hunt down source of sql executions
- From: "Jeffrey Beckstrom" <JBECKSTROM@xxxxxxxxx>
- To: <Chris.Stephens@xxxxxxx>, "oracle-l@xxxxxxxxxxxxx" <oracle-l@xxxxxxxxxxxxx>
- Date: Wed, 02 Mar 2011 10:32:11 -0500
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: