Re: RE: Missing SQL in DBA_HIST_SQLSTAT
- From: Niall Litchfield <niall.litchfield@xxxxxxxxx>
- To: Dave.Herring@xxxxxxxxxx
- Date: Tue, 2 Nov 2010 17:20:46 +0000
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:...
Other related posts:
