Re: Missing rows under dba_hist_sqlstat

Hello Maris, Jonathan,
I have worked on many cases where "wall clock time" >> real execution time of the query.
( file generation taking time par example) . In that case , sum(DBA_HIST_SQLSTAT.ELAPSED_TIME_DELTA) =MAX(DBA_HIST_SQLSTAT.ELAPSED_TIME_TOTAL) and MAX(DBA_HIST_SQLSTAT.ELAPSED_TIME_TOTAL) << "wall clock time" I assumed that this query was executed one time.

In my case , the query is executed via PL/SQL function.

Another finding, the insertion into wrh$_sqlstat (DBA_HIST_SQLSTAT) is based on V$SQLAREA_PLAN_HASH . When the query is under execution , there is no row into V$SQLAREA_PLAN_HASH.

I have shared a simple test case
https://livesql.oracle.com/apex/livesql/s/kv21hp90yyy8j142r41w00n3s

set optimizer_adaptive_plan = false => I dont have this behavior.

Thanks
Cherif

Le mer. 25 nov. 2020 à 11:50, Jonathan Lewis <jlewisoracle_at_gmail.com> a écrit :

> In the absence of further information I think Maris' observation is the
> correct one, and you would get a better handle on this by taking a snapshot
> of the session activity stats and session events for your test case.
>
> However, I would be interested in seeing the justification Oracle support
> gave for their 'not a bug' conclusion.
>
> Regards
> Jonathan Lewis
>
>
> On Wed, 25 Nov 2020 at 10:17, Cherif Ben Henda <cherif.benhenda_at_gmail.com>
> wrote:
>
>> Hi All,
>> During monitoring of PROD long running reporting...
>> Oracle version 12.1/ optimizer_adaptive_features =false and optimizer_adaptive_plans
>> =true, I identified a query taking more than 17 000 sec. The next day
>> when I generated AWR covering its execution period, I found that this query
>> had taken 2600 sec. I have checked dba_hist_sqlstat , there is only one
>> row / one snap. The delta elapsed time was 2600 but the total elapsed time
>> was exact / AWR report is based on delta columns. Under
>> dba_hist_active_sess_history , I get the exact number of snap_id, in fact
>> , there are many missing rows under dba_hist_sqlstat .
>> I have identified the root cause. During these snapshots , we don't have
>> a final execution plan ( it was a complex query with more than 14 tables),
>> it was executed with an adaptive execution plan.
>> I identified that this behavior happened with dozens of queries.
>>
>> I can not share with you the real issue , I succeed in reproducing it.
>> I need to know if you have encountered a similar issue ? It seems like a
>> bug
>> Please find a test case, I have changed AWR snap duration to 5 min.
>> https://livesql.oracle.com/apex/livesql/s/kv21hp90yyy8j142r41w00n3s
>>
>> I have opened an SR but they think it is not a bug ....
>> --
>> Cordialement,
>> Cherif Ben Henda
>>
>>
>>

-- 
Cordialement,
Cherif Ben Henda

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