In this post I would like to share a quick way of identifying the timed-out SQLs from a SQL tuning task report.
Usually you would do that from EM Express (12c) or Cloud control. But, having an access to EM/Cloud control is not always possible and you might be stuck in identifying the correct statements.
Here is an example, the portion of the SQL Auto Tuning Task report which shows how many statements have timed-out.
... ----------------------------------------------------------- Global SQL Tuning Result Statistics ----------------------------------------------------------- Number of SQLs Analyzed : 130 Number of SQLs in the Report : 23 Number of SQLs with Findings : 22 Number of SQLs with Statistic Findings : 2 Number of SQLs with Alternative Plan Findings: 5 Number of SQLs with SQL profiles recommended : 14 Number of SQLs with Index Findings : 11 Number of SQLs with SQL Restructure Findings : 3 Number of SQLs with Timeouts : 2 Number of SQLs with Errors : 1 ...
I’ve extracted the following SQL from the EM Express – SQL Tuning Advisor report (page).
The original statement is much bigger, but the following portion should be good enough to give you the statements which have timed-out.
All you need to do is to specify the execution name. It has been tested on 11.2 and 12.1.
SELECT oe.*, f.id finding_id, f.type finding_type, f.flags finding_flags FROM (SELECT o.exec_name , o.id object_id, o.attr1 sql_id, o.attr3 parsing_schema, to_number(NVL(o.attr5, '0')) phv, NVL(o.attr8,0) obj_attr8 FROM wri$_adv_objects o WHERE o.exec_name = 'exectuion_name' AND o.type = 7 ) oe, wri$_adv_findings f WHERE f.exec_name = oe.exec_name AND f.obj_id = oe.object_id AND type = 3 AND bitand(flags,2) <> 0 /