I noticed your livesql link the first time around - but I prefer
eliminating the unstated easy options and collecting relevant collateral
information before trying to run up a matching database to do a test, so
I'll repeat the question: what was the justification offered by Oracle
support? We've got your explanation for the anomaly, did they agree with
that then use that to dismiss the anomaly as expected behaviour, or did
they say something else?
Regards
Jonathan Lewis
On Wed, 25 Nov 2020 at 11:10, Cherif Ben Henda <cherif.benhenda@xxxxxxxxx>
wrote:
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
--
Cordialement,
Cherif Ben Henda
