Re: Execute Count in AWR Report

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


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