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

No, you can’t really tell apart from correlated behaviour (runs for x minutes, stops, different connection starts shortly after, repeat) Best place to look is clientside application logs.

It is not uncommon for a client to timeout and the sql statement to carry on executing up to a certain point because it’s not in a place where it can be immediately interrupted.

Such timeouts are normally wrong in my opinion and the product of misconceptions. Running a specific sql statement, auto timing out after 2 minutes, re-running the same workload with the same parameters and expecting something different to happen is one definition of insanity.

Cheers
Dominic

Sent from my iPhone

On 23 Aug 2023, at 23:47, Mladen Gogala <gogala.mladen_at_gmail.com> wrote:


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<https://dbwhisperer.wordpress.com/>

--

http://www.freelists.org/webpage/oracle-l Received on Thu Aug 24 2023 - 09:57:57 CEST