Missing rows under dba_hist_sqlstat
From: Cherif Ben Henda <cherif.benhenda_at_gmail.com>
Date: Wed, 25 Nov 2020 11:16:47 +0100
Message-ID: <CANH7Qp9F9FOCeP6boE2TY9ovxY8fEwQ7gBMuQwgy5ZOVfg8atQ_at_mail.gmail.com>
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.
Date: Wed, 25 Nov 2020 11:16:47 +0100
Message-ID: <CANH7Qp9F9FOCeP6boE2TY9ovxY8fEwQ7gBMuQwgy5ZOVfg8atQ_at_mail.gmail.com>
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-- http://www.freelists.org/webpage/oracle-l Received on Wed Nov 25 2020 - 11:16:47 CET