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

On 8/23/23 16:35, Sanjay Mishra (smishra_97) 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
>
>
I would say that there is no way to detect timeout from DBA_HIST_*  tables. Personally, I would use DBA_AUDIT_TRAIL and AUDIT NETWORK commands.

-- 
Mladen Gogala
Database Consultant
Tel: (347) 321-1217
https://dbwhisperer.wordpress.com

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