Re: DBA_HIST_SQLSTAT
From: Andy Klock <andy_at_oracledepot.com>
Date: Tue, 20 Nov 2012 14:24:46 -0500
Message-ID: <CADo_RaPfXf+hzeGge_t1Hfvxq9+HF_JzGHiB6w860+7a_aD3GA_at_mail.gmail.com>
On Tue, Nov 20, 2012 at 12:24 PM, David Fitzjarrell <oratune_at_yahoo.com>wrote:
> They don't, actually. The executions_total is the total executions since
> the database was started, the elapsed_time_total is the total since the db
> was started and the deltas are for each snapshot and don't roll up. I
> came into a shop where a script was running to generate execution time
> reports for long-running queries but it didn't take into account that the
> deltas don't roll up. I rewrote it to provide total execution times across
> snapshots for a given sql_id:
>
Date: Tue, 20 Nov 2012 14:24:46 -0500
Message-ID: <CADo_RaPfXf+hzeGge_t1Hfvxq9+HF_JzGHiB6w860+7a_aD3GA_at_mail.gmail.com>
On Tue, Nov 20, 2012 at 12:24 PM, David Fitzjarrell <oratune_at_yahoo.com>wrote:
> They don't, actually. The executions_total is the total executions since
> the database was started, the elapsed_time_total is the total since the db
> was started and the deltas are for each snapshot and don't roll up. I
> came into a shop where a script was running to generate execution time
> reports for long-running queries but it didn't take into account that the
> deltas don't roll up. I rewrote it to provide total execution times across
> snapshots for a given sql_id:
>
My experience is hit and miss when making calculations with executions_total and executions_delta. The reason being these numbers aren't cumulative to the start of the instance but rather since the time they've been in the library cache. (same as v$sqlstats). So, sometimes for long running queries (that span across multiple snapshots) it's possible that they will never show an execution in AWR.
-- http://www.freelists.org/webpage/oracle-lReceived on Tue Nov 20 2012 - 20:24:46 CET