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