Re: Check SqlId status Failed or timedout or killed from AWR
Date: Thu, 24 Aug 2023 16:44:46 +0200
Message-ID: <CAJu8R6i1o2hT9Z2Te21fZvje2VcyereiiZ4BN_iq+JDDpg2iGw_at_mail.gmail.com>
Hello
I agree with Mikhail and would like to add a comment about looking up the error message from dba_hist_reports.
The last time I was interested in the history of slow, monitored, and error-prone queries, I noticed, unfortunately, that the view dba_hist_reports doesn't expose the contents of the *error_message i*n its *report_summary* column.
Indeed, while we can easily use gv$sql_monitor to list executions in error due to ORA-01013(for example) as shown below
col error_message for a70
select
sql_id
,report_id
,status
,error_message
from
gv$sql_monitor
where
status like '%ERROR%'
and
*error_message like '%ORA-01013%';*
SQL_ID REPORT_ID STATUS ERROR_MESSAGE
------------- ---------- ------------------- ---------------------------------------------------------------- 0uyfvfb2aq6b3 0 DONE (ERROR) ORA-01013: user requested cancel of current operation 0uyfvfb2aq6b3 0 DONE (ERROR) ORA-01013: user requestedcancel of current operation
we can't have this search finesse from dba_hist_reports. All we can do is list the executions in error as shown below
SELECT
report_id
,key1 sql_id
,key2 sql_exec_id
,key3 sql_exec_start
,xt.sid
,xt.serial
,xt.module
,xt.program
,xt.phv
,xt.duration
,xt.elapsed_time
,xt.cpu_time
,xt.status
FROM
dba_hist_reports h
INNER JOIN
xmltable('/report_repository_summary'
passing XMLType(h.report_summary)
columns
sid number path 'sql/session_id'
,serial number path 'sql/session_serial'
,module varchar2(40) path 'sql/module'
,action varchar2(40) path 'sql/action'
,status varchar2(40) path 'sql/status'
,program varchar2(40) path 'sql/program'
,phv varchar2(12) path 'sql/plan_hash'
,duration number path 'sql/stats/stat[_at_name="duration"]'
,elapsed_time number path 'sql/stats/stat[_at_name="elapsed_time"]'
,cpu_time path
'sql/stats/stat[_at_name="cpu_time"]'
) xt
on (1=1)
WHERE
component_name = 'sqlmonitor'
*AND xt.status like '%ERROR%'*
;
no rows selected
I don't know why Oracle hasn't externalized the error_message information in dba_hist_report. However, this information is certainly stored somewhere, because when we run:
SELECT
DBMS_AUTO_REPORT.REPORT_REPOSITORY_DETAIL(RID => 1022, TYPE => 'text')
FROM dual;
The error message can be found in the generated report if the execution was in error of course.
If you have any idea where this might be coming from, I'd be delighted to hear from you.
Best regards
Mohamed Houri
Le jeu. 24 août 2023 à 12:00, Mikhail Velikikh <mvelikikh_at_gmail.com> a écrit :
> 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 >> >
-- Houri Mohamed Oracle DBA-Developer-Performance & Tuning Visit My - Blog <http://www.hourim.wordpress.com/> Let's Connect - <http://fr.linkedin.com/pub/mohamed-houri/11/329/857/>*Linkedin Profile <http://fr.linkedin.com/pub/mohamed-houri/11/329/857/>* My Twitter <https://twitter.com/MohamedHouri> - MohamedHouri <https://twitter.com/MohamedHouri> -- http://www.freelists.org/webpage/oracle-lReceived on Thu Aug 24 2023 - 16:44:46 CEST