dbms_sqltune.report_sql_monitor

Use this package to see which step(s) in the execution plan consume the most time (if your database is Enterprise Edition and you have the Tuning pack license).

----------------------------------------------------------------------------------------------------

* Basic syntax

select dbms_sqltune.report_sql_monitor('<sql id>') from dual;

----------------------------------------------------------------------------------------------------

* Output is cut off

If you use sqlplus, do something like this first:
set pages 1000 long 1000000 longc 200 lin 200

----------------------------------------------------------------------------------------------------

* No output

Maybe the SQL finished too fast (using <5 seconds of CPU and I/O time). Add the monitor hint and retry, e.g
select /*+ monitor */ ...
select dbms_sqltune.report_sql_monitor('<sql id>') from dual;

You can get <sql id> of the last SQL you ran with "set feedback on sql_id" prior to run that SQL.

----------------------------------------------------------------------------------------------------

* When the SQL is still running, report_sql_monitor shows values in the Activity column. But if the SQL has finished (but has not been aged out of shared pool as I can still see it in v$sql_monitor), Activity is empty.

Change the output type, e.g.,

set trims on pages 1000 long 1000000 longc 200 lin 200
spo mysql.html
select dbms_sqltune.report_sql_monitor('<sql id>', type=>'html') from dual;
--or select dbms_sqltune.report_sql_monitor('<sql id>', type=>'active') from dual;
spo off

and open mysql.html with a web browser.

----------------------------------------------------------------------------------------------------

* How do I see the part of the where-clause corresponding to a line I'm interested in in the output of report_sql_monitor, such as the line with the highest Activity?

select * from table(dbms_xplan.display_cursor('<sql id>','',''));

If the database is RAC, this query must be run in the instance where the SQL cursor is, unlike dbms_sqltune.report_sql_monitor, which can be run in any instance.

Note: It's possible to see different plans between dbms_sqltune.report_sql_monitor and dbms_xplan.display_cursor due to "adaptive plan", which can be disabled by setting optimizer_adaptive_plans to false. Trust dbms_sqltune.report_sql_monitor. You can run the latter as
select * from table(dbms_xplan.display_cursor('<sql id>','',format=>'ADAPTIVE ALLSTATS LAST'));
to see inactive lines preceded by "-", i.e. the operations CBO at the last minute didn't choose to actually run.[note]

----------------------------------------------------------------------------------------------------

* Running dbms_sqltune.report_sql_monitor on a read-only database throws error:
ORA-14552: cannot perform a DDL, commit or rollback inside a query or DML

Run it in a PL/SQL context:

var s varchar2(32767)
exec :s:=dbms_sqltune.report_sql_monitor('<sql id>')
print s

----------------------------------------------------------------------------------------------------

* How do I show the execution plan of a specific child cursor of a SQL that has multiple child cursors?

If you know the plan hash value (available in v$sql* views):
select dbms_sqltune.report_sql_monitor('<sql id>',sql_plan_hash_value=><plan hash value>);

If you know sql_exec_start time or sql_exec_id (available in v$session):
select dbms_sqltune.report_sql_monitor('<sql id>', sql_exec_start=><date value>);
select dbms_sqltune.report_sql_monitor('<sql id>', sql_exec_id=><sql exec id>);

----------------------------------------------------------------------------------------------------




[note]
If you really want to know how
dbms_xplan.display_cursor('<sql id>','',format=>'ADAPTIVE ALLSTATS LAST')
gets the inactive lines in an execution plan from data dictionary, those lines correspond to the rows with a skp="1" attribute on the rows in v$sql_plan.other_xml, e.g., row Id 2 of the plan in the example below is inactive:

SQL> select * from table(dbms_xplan.display_cursor('<sql id>','',format=>'ADAPTIVE ALLSTATS LAST'));
...
--------------------------------------------------------------------------------------------------
|   Id  | Operation                         | Name           | E-Rows |  OMem |  1Mem | Used-Mem |
--------------------------------------------------------------------------------------------------
|     0 | SELECT STATEMENT                  |                |        |       |       |          |
|     1 |  SORT AGGREGATE                   |                |      1 |       |       |          |
|- *  2 |   HASH JOIN                       |                |   2816 |  1423K|  1423K|          |
...

SQL> select xmlserialize(document xmltype(other_xml) as clob indent size=2) from v$sql_plan where sql_id='<sql id>' and other_xml is not null;
...
  </outline_data>
  <display_map>
    <row op="1" dis="1" par="0" prt="0" dep="1" skp="0"/>
    <row op="2" dis="1" par="1" prt="0" dep="1" skp="1"/>
...
  </display_map>
</other_xml>