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'));
instead.

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

* 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>);

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