Re: RE: Missing SQL in DBA_HIST_SQLSTAT
Date: Tue, 2 Nov 2010 19:23:24 +0000
Message-ID: <AANLkTinKr=3PmpPw4yuGfwVh87tj=U8q+cG8_MH_KmuH_at_mail.gmail.com>
hey well "trust but verify" is an excellent watchword...
Probably my chief resource is Graham Wood's "Sifting through the Ashes"
presentation from when 10g arrived.
http://www.oracle.com/technetwork/database/features/manageability/ppt-active-session-history-129612.pdf
specifically
p13.
http://oracledoug.com/serendipity/index.php?/archives/1402-MMON-Sampling-ASH-Data.html
was
also useful. Regrettably the white paper the latter references appears to
have disappeared (and I don't have a copy grr oracle, at least archive this
stuff.).
On Tue, Nov 2, 2010 at 6:23 PM, Herring Dave - dherri < Dave.Herring_at_acxiom.com> wrote:
> 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_at_DNB.com
>
>
> From: Niall Litchfield [mailto:niall.litchfield_at_gmail.com]
> 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_at_acxiom.com>
> 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.
>
> ****************************************************************************
>
>
-- Niall Litchfield Oracle DBA http://www.orawin.info -- http://www.freelists.org/webpage/oracle-lReceived on Tue Nov 02 2010 - 14:23:24 CDT