V$sql_monitor for Oracle

Hi All,

Oracle 11 g has this awesome feature called Sql monitor. What it does it , It allows you to see stepwise execution of explain plan . You can check currently which statement in your explain plan is running. and what is the % of completion of that step. Amount of CPU used. What are the waits on .

The issue is access to this table is not there by default and you need special access to get this. Currently i do not have access to this in my office. We can use v$session, Active_session_history, V$sql_plan, Autotrace to get all the required information. But this view is kind of fun . You can do perfomrance tuning without this view . But this view makes it very very easy.

Below is the script for generating activ monitor 

Open sqlplus and type

@C:/Users/ADMIN/Desktop/sql_script.sql

set trimspool on
set trim on
set pages 0
set linesize 1000
set long 1000000
set longchunksize 1000000
spool C:\Users\ADMIN\Desktop\sqlmon_active.html
select dbms_sqltune.report_sql_monitor(type=>'active') from dual;
spool off