Re: Check SqlId status Failed or timedout or killed from AWR

Is this one specific SQL statement that is executed only a few times per day so that each execution can be seen individually, or could several executions be reported overlapping in several consecutive AWR snapshots.

If you can see it running "for a few hours" are you sure that it's the single execution of that statement: if the statement is active then it will show up in the active session history with an sql_id, session, serial number, exec id and exec start time. This might mean (as per Dominic's comment) that the statement is still working to generate some result set even though the front-end has disappeared. You might get some information by comparing the FETCHES and END_OF_FETCH counts with the execution counts in the SQLSTAT data

If the AWR hasn't captured the new execution plan (which is should have done if it's managed to report the statement in the AWR reports for a single interval) then you could probably construct the plan from the ASH samples - and find out which part of the plan is the one where most of the time is spent.

Regards
Jonathan Lewis

On Wed, 23 Aug 2023 at 21:36, Sanjay Mishra <dmarc-noreply_at_freelists.org> wrote:

> Is there a way we can tell from Oracle AWR history if the particular SQLID
> was successful or timeout by some Application Connection timeout setting? I
> can see that one SQL was showing avg_etime of 120s but after changing the
> app tier, I can see it is running for a few hours. I want to check if the
> SQL was timed out earlier and not completed after 120s due to some Timeout
> setting on old App Tier which is not deployed in the new App tier. SqL Plan
> is same with no change.
>
> select ss.snap_id, ss.instance_number node, begin_interval_time, sql_id,
> plan_hash_value,
> 2 nvl(executions_delta,0) execs,
> 3
> (elapsed_time_delta/decode(nvl(executions_delta,0),0,1,executions_delta))/1000000
> avg_etime,
> 4
> (buffer_gets_delta/decode(nvl(buffer_gets_delta,0),0,1,executions_delta))
> avg_lio,SQL_PROFILE
> 5 from DBA_HIST_SQLSTAT S, DBA_HIST_SNAPSHOT SS
>
>
> TIA
> Sanjay
>

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