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@xxxxxxxxx>
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
