Re: RE: Missing SQL in DBA_HIST_SQLSTAT

From: Niall Litchfield <niall.litchfield_at_gmail.com>
Date: Tue, 2 Nov 2010 17:20:46 +0000
Message-ID: <AANLkTik56HRsARt-9vKtgcZnhgBBDa1KSLaej+d81G=m_at_mail.gmail.com>

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:...

--
http://www.freelists.org/webpage/oracle-l

Received on Tue Nov 02 2010 - 12:20:46 CDT