Missing SQL in DBA_HIST_SQLSTAT
Date: Mon, 25 Oct 2010 17:07:39 -0500
Message-ID: <7ED53A68952D3B4C9540B4EFA5C76E360872A51E_at_CWYMSX04.Corp.Acxiom.net>
Has anyone checked into the accuracy and reliability of AWR SQL stats? From checks I've done it appears that a lot of SQL statements are not getting captured in AWR snapshots.
Here's our config: 4-node RAC, Oracle 10.2.0.2, RH 4.x, 30-minute AWR snapshots.
We've left TOPNSQL as "DEFAULT", which in our case should be enough:
SELECT topnsql
FROM sys.wrm$_wr_control;
TOPNSQL
2000000000
This means that the first 2 BILLION SQL statements per category will be selected (seems rather excessive!). Yet I ran a few checks where I compared (GV$SQL.LAST_ACTIVE_TIME - snapshot interval) to the MAX(end_interval_time) for snapshots (all by SQL_ID) and found over 1600 statements that are executed yet no updates are captured by AWR.
As a double check, I reviewed data in DBA_HIST_ACTIVE_SESS_HISTORY and found that 1 of the 1600 statements from above is executed multiple times per day, yet the last DBA_HIST_SQLSTAT snapshot it's in is from 4 months ago.
Has anyone else seen this type of thing and/or done detailed checking on the accuracy of AWR SQL data?
BTW, I tried tracing the snapshot activity and can't get past what's done to populate internal objects X$KEWRSQLIDTAB and X$KEWRSQLCRIT.
Dave Herring | DBA
Acxiom Global Technology Solutions
630-944-4762 office | 630-430-5988 cell | 630-944-4989 fax 1501 Opus Pl | Downers Grove, IL, 60515 | U.S.A. | www.acxiom.com Service Desk: 888-243-4566, https://servicedesk.acxiom.com, GSCA_at_DNB.com
The information contained in this communication is confidential, is intended only for the use of the recipient named above, and may be legally privileged.
If the reader of this message is not 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 resend this communication to the sender and delete the original message or any copy of it from your computer system.
Thank You.
--
http://www.freelists.org/webpage/oracle-l
Received on Mon Oct 25 2010 - 17:07:39 CDT