Re: Missing rows under dba_hist_sqlstat

Hi,

I understand that the 17000 seconds is the "wall clock time". The elapsed time in the AWR is the time the query was actively executing in the database.
These two measurements can be different, for example, if the client process on the application server probably takes time to process the data between fetches, the session is idle at that time, and thus - unaccounted in elapsed time.
In your case, the wallclock time and the elapsed time difference is large, so it's likely your performance bottleneck is not the DB server, but the client process/server/workstation running the query.

Maris

On Wed, Nov 25, 2020 at 12:17 PM 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
>
>
>

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