Re: Execute Count in AWR Report

  • From: Mladen Gogala <gogala.mladen@xxxxxxxxx>
  • To: oracle-l@xxxxxxxxxxxxx
  • Date: Sat, 18 Jul 2020 13:42:40 -0400


On 7/16/20 1:00 PM, MacGregor, Ian A. (Redacted sender ian for DMARC) wrote:



select sql_id, sql_plan_hash_value, min(sample_time) as first_time, max(sample_time) as last_time
from DBA_HIST_ACTIVE_SESS_HISTORY
where sql_id ='9aa87dfufsr8r'
Group by sql_id, sql_plan_hash_value
order by 3
/

SQL_ID           SQL_PLAN  FIRST_TIME    LAST_TIME
------------------- ---------------- ------------------------------------- --------------------------------------
9aa87dfufsr8r  913658610  15-JUL-20 12.04.51.402 PM 15-JUL-20 12.07.31.570 PM
9aa87dfufsr8r 3515813421 15-JUL-20 12.13.21.966 PM 15-JUL-20 12.14.52.116 PM


When I look at the AWR report for the hour from Noon to 1pm on July 15th it reports

only one execution with an elapsed time of  101.05 seconds

This seems top correspond with the

Second run (incremental):
FSTST(07/15 12:13) took 1mins 58 sec. retrieve rows 0.

My first question is why isn't the first run   counted in the AWR?



From what I remember,  AWR report gets its information from V$SQLSTATS, not from WRH$ tables, which is where DBA_HIST_ACTIVE_SESS_HISTORY gets its data from. This looks like a bug, but I can't tell whether the bug is in V$SQLSTATS or in AWR report. Please check how many executions there are in V$SQLSTATS. If there is only one, than there is a problem with Oracle internal mechanisms and creative accounting of the SQL statistics.

A bug is the only answer because you're obviously getting a wrong result from the AWR report, which has somehow missed the 1st execution. Wrong results are bugs by definition. I think that you should open a case with Oracle Support.

Regards

--
Mladen Gogala
Database Consultant
Tel: (347) 321-1217

Other related posts:

  1. Home
  2. oracle-l
  3. Archive
  4. 07-2020