• From: "Herring Dave - dherri" <Dave.Herring@xxxxxxxxxx>
  • To: "ORACLE-L" <oracle-l@xxxxxxxxxxxxx>
  • Date: Mon, 25 Oct 2010 17:07:39 -0500

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, 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;


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@xxxxxxx

The information contained in this communication is confidential, is
intended only for the use of the recipient named above, and may be legally

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.


Other related posts: