NoCOUG Third SQL Challenge

Boy that was fun. Not revealing my answer to wider audience just jet as the competition just started. Try it yourself. Breadth first seems to do less than depth first. Maybe that is why it is the default in recursive common table expression.
Update 28.5.2012 additional rules published. Made the question a bit harder than it seemed at first try. Well that is just software testing, when solving complex tasks. Test data and desired answers are required.

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.