Real Time SQL Monitoring in Oracle 11g

Do you want to know, 

Can i see the plan while the SQL is running ?

Where is my SQL is in its Execution Plan ?

Need statistics at each step of the plan and what are the resources consumed for the just completed step ?

What is the Actual Cardinality at each step ?

       There are many ways to retrieve the above data and they are costly in terms of impacting the system performance. Oracle 11g introduces Real Time SQL Monitoring which would answer the above question with  almost no impact on the system.

Lets try these,  Below is the query written with the only intention to run for longer period of time :)

SQL> create table tbl_parallel as select rownum id,'DOD'||rownum id_val,sysdate
id_dt from dual connect by level < 10000;
SQL>
SQL>
SQL> with tp as (
select * from tbl_parallel),
tl as (
select length(id_val) ld from tbl_parallel where length(id_val)> (select min(length(id_val)) from tbl_parallel))
select * from tp t1,tp t2,tl where t1.id > t2.id and length(t1.id_val)>tl.ld;
SQL>

Lets take a look at the Execution Plan and check what is the step it is currently executing 

 The report is so cool, the SQL is now performing SORT JOIN. Take a look at the statistics - Elapsed time, Buffer gets and writes and I/O wait time being provided. We could also see the actual cardinality for each step. It also provides the SQL Bind info, Session id etc

will see how SQL Monitoring can help in analyzing SQL's in the next part