RE: Missing SQL in DBA_HIST_SQLSTAT
- From: "Teehan, Mark" <mark.teehan@xxxxxxxxxxxxxxxxx>
- To: <Dave.Herring@xxxxxxxxxx>, "ORACLE-L" <oracle-l@xxxxxxxxxxxxx>
- Date: Tue, 2 Nov 2010 13:53:04 +0800
Dave,
I've noticed that the quality of AWR data collection varies a lot depending on
the application profile.
I am not an expert on database internals ("Tanel!!") but this is what I have
worked out; my problems in this area are due to flushing of the sql area. As
you can see the metrics in gv$sqlstat; then it may not be relevant.
A database hosting an application that executes most of its workload during a
batch window seems to have lower quality of AWR data collection during its
busiest period. One major reason for this is aging of single-execution
statements.
The SQL Area sizes automatically based on the workload of the entire day, so
when a large burst of statements is run (= overnight batch cycle), the SQL area
will not resize up immediately (if at all), and single-execution statements
will be aged out first before the next AWR snap occurs. I've seen this happen a
lot - the statement metrics are no longer visible in gv$sqlstat at snap-time so
it never goes into AWR.
Aging of metrics for statements is only a problem if the database needs to
flush the sql area: it can be flushed by other activity. Single-execution
statements appear to be chosen first, irrespective of how long they ran for -
AFAIK no attempt is made to classify which single-execution statements may
still have important metrics.
A flush offender is a stats gather on objects with a lot of column statistics:
it generates a large number of recursive statements for each column (select
min(), max() etc) in a short span, which can flush a lot of metrics from the
sql area.
The AWR partition rolling job can also generate a lot of statements - this
generally runs once a day.
I've raised an SR to find out if there is any way to keep statements in the SQL
area until they have been snapped into AWR, but support told me to go away, its
supposed to be like that. It is not possible to directly set the size of the
sql area to improve metric retention. Statement pinning doesn't work either:
the text is kept, but the metrics (gv$sqlstat) are flushed!
I hit a brick wall on this. It seems a design flaw to me - the sql area flush
process should assess the metrics for each statement to see if it is eligible
for AWR retention. Not trivial I know, but a lot depends on AWR quality.
Interesting that you have seen this on a 4-node RAC - the worst offender I have
is a 3-node RAC - is there any chance AWR is not picking up metrics across all
instances?
HTH
Mark Teehan
Singapore
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On
Behalf Of Herring Dave - dherri
Sent: 26 October 2010 06:08
To: ORACLE-L
Subject: Missing SQL in DBA_HIST_SQLSTAT
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@xxxxxxx
***************************************************************************
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.
****************************************************************************
--
//www.freelists.org/webpage/oracle-l
===============================================================================
Please access the attached hyperlink for an important electronic communications
disclaimer:
http://www.credit-suisse.com/legal/en/disclaimer_email_ib.html
===============================================================================
--
//www.freelists.org/webpage/oracle-l
Other related posts: