RE: An app runs a query near 2k times/hr, yet not showing in AWR?
Date: Wed, 25 Feb 2009 07:14:54 -0500
Message-ID: <49a53644.1b17400a.4e8f.4beb_at_mx.google.com>
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.
Thanks
-----Original Message-----
From: Yavor Ivanov <Yavor_Ivanov_at_stemo.bg>
Sent: Wednesday, February 25, 2009 1:48 AM
To: fmhabash_at_gmail.com <fmhabash_at_gmail.com>; Oracle-L Group <oracle-l_at_freelists.org>
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?
Regards,
Yavor Ivanov
-----Original Message-----
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] 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
--
http://www.freelists.org/webpage/oracle-l
--
http://www.freelists.org/webpage/oracle-l
Received on Wed Feb 25 2009 - 06:14:54 CST