RE: An app runs a query near 2k times/hr, yet not showing in AWR?

  • From: FMHabash <fmhabash@xxxxxxxxx>
  • To: Yavor Ivanov <Yavor_Ivanov@xxxxxxxx>, Oracle-L Group <oracle-l@xxxxxxxxxxxxx>
  • Date: Wed, 25 Feb 2009 07:14:54 -0500

We ended up with 2 scenarios...
1) feedback we originally got from apps was this query must appear 2k/hr. For 
this db, such rate should've qualified it to show in awr. At some point in 
time, we chose to verify this claim. Looked at executions and first-load-time, 
we saw this query was loaded on 2/15 and executed 30k times. With simple math, 
this is about 130/hr. Does not match the app reports. With this rate, it would 
probably not make it to the awr.

2) We wrote a acript to run on v$sqlarea looking for the sql-text. Turned out 
that different sql-id's showed same text which lead us to think this query is 
parsed with every execution. We then checked to see if there is any ddl run on 
these tables to invalidate the queries. Found none.  We are checking with apps 
to see if any ddl or prepapre statements is being issued along with the quries. 
No answer, yet.


-----Original Message-----
From: Yavor Ivanov <Yavor_Ivanov@xxxxxxxx>
Sent: Wednesday, February 25, 2009 1:48 AM
To: fmhabash@xxxxxxxxx <fmhabash@xxxxxxxxx>; Oracle-L Group 
Subject: RE: An app runs a query near 2k times/hr, yet not showing in AWR?

        What about dba_hist_sql* views? Do you see your SQL there, and how many 
times is it executed for the period you are looking at?
        Also, do you see it in v$sql?

Yavor Ivanov

-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On 
Behalf Of FMHabash
Sent: Tuesday, February 24, 2009 9:52 PM
To: Oracle-L Group
Subject: An app runs a query near 2k times/hr, yet not showing in AWR?

This is a query that app teams claims to be running at least 2k/hr, yet we are 
not seeing it to show in AWR. These are potential causes we came up with...
1- query changed sql-id as a result of a parse. Searching by its text, did not 
show query running at all.

2- query is parsed with every execution, therefore will post with exec=1 and 
never show in qwr report.

What further causes or dianostics can help in diagnosing this issue?

Thank you 



Other related posts: