2012-05-07

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.

No comments:

Post a Comment

About Me

My photo
I am Timo Raitalaakso. I have been working since 2001 at Solita Oy as a Senior Database Specialist. I have received Oracle ACE nomination. My main focus is on projects involving Oracle database. In this Rafu on db blog I write some interesting issues that evolves from my interaction with databases. Mainly Oracle.