RE: RE: Missing SQL in DBA_HIST_SQLSTAT
- From: "Herring Dave - dherri" <Dave.Herring@xxxxxxxxxx>
- To: "Niall Litchfield" <niall.litchfield@xxxxxxxxx>
- Date: Tue, 2 Nov 2010 13:23:14 -0500
Yup, that's what I'm expecting (or was expecting, before reading your reply),
that AWR SQL data is coming from the SQL cache, specifically *$SQL* views or
underlying structures.
So how did you find out or know that AWR SQL data is coming from ASH memory?
Do you have any resources about this process? As I wrote earlier, I tried
tracing my session and ended up with selects from X$KEWRSQLCRIT and
X$KEWRSQLIDTAB but couldn't find out how these were populated.
GV$ACTIVE_SESSION_HISTORY selects from X$KEWASH and X$ASH. I'm by no means
doubting you - just trying to find out more information to understand this
whole process better.
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
From: Niall Litchfield [mailto:niall.litchfield@xxxxxxxxx]
Sent: Tuesday, November 02, 2010 12:21 PM
To: Herring Dave - dherri
Cc: ORACLE-L; Teehan, Mark; Petr Novak
Subject: Re: RE: Missing SQL in DBA_HIST_SQLSTAT
Dave, I may be missing something here, but it looks like you are expecting AWR
to contain the top n sql statements from the sql cache. It won't. AWR contains
the top n sql statements from a sample of the ASH memory buffers (mostly 1 in
10). The ASH memory buffers themselves contain samples (a snapshot of *active*
sessions currently executing sql) . Bottom line AWR is a sample of significant
SQL. Not a record of all activity.
I'd also hypothesise (don't know) that ash data is collected in an inconsistent
fashion and so short duration statements may stand a really poor chance of
being captured. Not sure how to test that.
On 2 Nov 2010 16:09, "Herring Dave - dherri" <Dave.Herring@xxxxxxxxxx> wrote:
SELECT topnsql
FROM sys.wrm$_wr_control;
TOPNSQL
------------------
2000000000...
As an example of what I'm talking about:
SELECT inst_id, plan_hash_value, first_load_time, MAX(last_active_time)
FROM cgv$sql
WHERE sql_id = '8qfp7bf2tcw57'
GROUP BY inst_id, plan_hash_value, first_load_time
ORDER BY inst_id;
I PLAN_HASH_VALUE FIRST_LOAD_TIME MAX(LAST_ACTIVE_TI
- ------------------ ------------------- ------------------
1 2586770207 2010-10-10/15:09:48 02-NOV-10 11:39:49
2 2586770207 2010-10-10/15:19:15 02-NOV-10 08:24:58
SELECT sq.instance_number, sq.plan_hash_value, MAX(end_interval_time)
FROM dba_hist_sqlstat sq, dba_hist_snapshot s
WHERE sq.sql_id = '8qfp7bf2tcw57'
AND ( sq.dbid = s.dbid
AND sq.instance_number = s.instance_number
AND sq.snap_id = s.snap_id)
GROUP BY sq.instance_number, sq.plan_hash_value
ORDER BY 1, 2;
INSTANCE_NUMBER PLAN_HASH_VALUE MAX(END_INTERVAL_TIME)
------------------ ------------------ ----------------------------------------
1 690739501 22-OCT-10 04.00.17.953 AM
1 2586770207 22-OCT-10 04.00.17.953 AM
2 690739501 03-SEP-10 04.30.42.593 PM
2 2586770207 03-SEP-10 04.30.42.593 PM
Dave Herring | DBA
Acxiom Global Technology Solutions
630-944-4762 office | 630-430-5988 cell ...
Sent: Tuesday, November 02, 2010 10:32 AM
To: Herring Dave - dherri; Teehan, Mark; ORACLE-L
Subject:...
***************************************************************************
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
Other related posts:
