A long plan to monitor

The day came yet again that I need to monitor a long plan with tuning pack sql monitor. Thanks to a blog post from Doug Burns I get those visible. The long plans have more than 300 lines.

The undescore parameter _sqlmon_max_planlines is also session modifiable.

alter session set "_sqlmon_max_planlines" = 3000;

SELECT /*+monitor*/ ... and the rest of your query
;

select dbms_sqltune.report_sql_monitor(mo.sql_id,mo.sid,mo.session_serial#),mo.*
  from v$sql_monitor mo 
 where sid = SYS_CONTEXT('userenv', 'SID') 
 order by mo.sql_exec_start desc
;

And you get the result. Compared to dbms_xplan.display_cursor(format=>'allstats last')) monitoring is giving results even while the query is running and so before you have read the whole result.