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

From: Mikhail Velikikh <mvelikikh_at_gmail.com>
Date: Thu, 24 Aug 2023 10:59:43 +0100
Message-ID: <CALe4Hp=_8jfH2GRR_gjHi5QthicPTU=E5dC+=dTkbW9cgvbkKA_at_mail.gmail.com>

Proper implemented timeouts should result in ORA-1013. For example, using an example from How to Use the JDBC setQueryTimeout Call to Cancel a Long Running Query and Raise an ORA-1013 (Doc ID 412171.1)

[oracle_at_rac1 ~]$
CLASSPATH=/u01/app/oracle/product/19.3.0/dbhome_1/jdbc/lib/ojdbc8.jar:. /u01/app/oracle/product/19.3.0/dbhome_1/jdk/bin/java TestQueryTimeout Connected to : Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.19.0.0.0
JDBC Driver: 19.19.0.0.0
jdbc:oracle:thin:_at_rac1:1522/pdb
start query
java.sql.SQLTimeoutException: *ORA-01013: user requested cancel of current operation*

end query - Elapsed : 10 seconds

As you are talking about a sufficiently long query, that could be captured by SQL Monitoring:
SQL> select sql_id, sql_text, error_number, error_message from v$sql_monitor where sql_text like 'select slow%';

SQL_ID        SQL_TEXT                                 ERROR_NUMBER

ERROR_MESSAGE

------------- ---------------------------------------- ------------
--------------------------------------------------------------------------------
2hgjhrkwuy68n select slow_query(20) from dual          1013

ORA-01013: user requested cancel of current operation

From 12c onwards, SQL monitoring data is also captured in AWR snapshots. You can try to find your query in DBA_HIST_REPORTS and see if ORA-1013 was thrown for it, e.g.:

SQL> select dbms_auto_report.report_repository_detail(rid=> 39, type=> 'text') from dual;

DBMS_AUTO_REPORT.REPORT_REPOSITORY_DETAIL(RID=>39,TYPE=>'TEXT')

---

SQL Monitoring Report

SQL Text

---

select slow_query(20) from dual

*Error: ORA-1013------------------------------ORA-01013: user requested

cancel of current operation*

DBMS_AUTO_REPORT.REPORT_REPOSITORY_DETAIL(RID=>39,TYPE=>'TEXT')

---

Global Information

---

 Status              :  DONE (ERROR)
 Instance ID         :  1
 Session             :  TC (481:5701)
 SQL ID              :  2hgjhrkwuy68n
 SQL Execution ID    :  16777216
 Execution Started   :  08/24/2023 09:41:26
 First Refresh Time  :  08/24/2023 09:41:32
 Last Refresh Time   :  08/24/2023 09:41:36
 Duration            :  10s

DBMS_AUTO_REPORT.REPORT_REPOSITORY_DETAIL(RID=>39,TYPE=>'TEXT')

---

 Module/Action       :  JDBC Thin Client/-
 Service             :  pdb
 Program             :  JDBC Thin Client
 Fetch Calls         :  1

Global Stats

---

| Elapsed | Cpu | IO | Cluster | PL/SQL | Other | Fetch | Buffer | Read | Read |
| Time(s) | Time(s) | Waits(s) | Waits(s) | Time(s) | Waits(s) | Calls |  Gets | Reqs | Bytes |

---

| 10 | 10 | 0.00 | 0.00 | 1.79 | 0.05 | 1 |  227 | 1 | 8192 |

DBMS_AUTO_REPORT.REPORT_REPOSITORY_DETAIL(RID=>39,TYPE=>'TEXT')

---

---

SQL Plan Monitoring Details (Plan Hash Value=1388734953)

---

| Id | Operation | Name | Rows | Cost | Time | Start | Execs | Rows | Activity | Activity Detail |

|    |                  |      | (Estim) |      | Active(s) | Active |

  | (Actual) | (%) | (# samples) |

---

|  0 | SELECT STATEMENT |      |         |      |         1 |     +9 |
1 |        0 |          |                 |
|  1 |   FAST DUAL      |      |       1 |    2 |         5 |     +6 |
1 |        0 |          |                 |


======================================================================================================================

On Wed, 23 Aug 2023 at 21:38, 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

Received on Thu Aug 24 2023 - 11:59:43 CEST