Just learned - Real-Time SQL Monitoring

I interest some topic in "Oracle Database 11g Performance Tuning Recipes A Problem-Solution Approach" book. Real-Time SQL Monitoring. How? This is 11g feature.

SQL monitoring is automatically started when a SQL statement runs parallel or when it has consumed at least 5 seconds of CPU or I/O time.

Before you will use this feature, your database have to have "statistics_level" = TYPICAL or ALL, "control_management_pack_access"=DIAGNOSTIC+TUNING

SQL> show parameter statistics_level

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
statistics_level string TYPICAL

SQL> show parameter control_management_pack_access

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
control_management_pack_access string DIAGNOSTIC+TUNING

When you need to test Real-Time SQL Monitoring, you can use HINTS:
MONITOR : To force real-time SQL monitoring
Or if
NO_MONITOR : To prevent the query from being monitored

How to monitor?
You can use V$SQL_MONITOR and V$SQL_PLAN_MONITOR views to monitor the statistics.
The V$SQL_MONITOR view contains a subset of the statistics available in V$SQL.
The V$SQL_PLAN_MONITOR view contains plan level monitoring statistics for each SQL statement in V$SQL_MONITOR.

What should you know from both views?
KEY NUMBER : Artificial join key to efficiently join V$SQL_MONITOR with its corresponding plan level monitoring statistics stored in V$SQL_PLAN_MONITOR.

STATUS : SQL execution status. Values are below :
■ EXECUTING - SQL statement is still executing
■ DONE (ERROR) - Execution terminated with an error
■ DONE (FIRST N ROWS) - Execution terminated by the application before all rows were fetched
■ DONE (ALL ROWS) - Execution terminated and all rows were fetched
■ DONE - Execution terminated (parallel execution)

Tested!!!
- On some session:

SQL> select /*+ MONITOR */ * from TB_TEST;

During SQL statement is still executing
- Find information from SQL statement

select key, status, username, module ,service_name, sql_text, cpu_time, buffer_gets from v$sql_monitor where status = 'EXECUTING'
/

- Find Execution Plan

select plan_line_id id, lpad (' ', plan_depth) || plan_operation operation , plan_options , plan_object_name , plan_cardinality card, plan_cost from v$sql_monitor q, v$sql_plan_monitor p where q.key = p.key and q.key = &KEY order by id
/

KEY= 158913789964
However, If You checked by "dbms_sqltune.report_sql_monitor"

SQL> select dbms_sqltune.report_sql_monitor from dual;
REPORT_SQL_MONITOR
---------------------------------------------------------------------------------------------------------------------------------
SQL Monitoring Report

SQL Text
------------------------------
select /*+ MONITOR */ * from TB_TEST

Global Information
------------------------------
Status : EXECUTING
Instance ID : 1
Session : DEMO (36:327)
SQL ID : 45fs1021jz1dg
SQL Execution ID : 16777219
Execution Started : 08/27/2011 17:38:05
First Refresh Time : 08/27/2011 17:38:05
Last Refresh Time : 08/27/2011 17:38:06
Duration : 2s
Module/Action : SQL*Plus/-
Service : SYS$USERS
Program : sqlplus@linuxtest01 (TNS V1-V3)
Fetch Calls : 244

Global Stats
=================================================
| Elapsed | Cpu | Other | Fetch | Buffer |
| Time(s) | Time(s) | Waits(s) | Calls | Gets |
=================================================
| 0.08 | 0.06 | 0.02 | 244 | 290 |
=================================================

SQL Plan Monitoring Details (Plan Hash Value=1092599453)
==============================================================================================================================
| Id | Operation | Name | Rows | Cost | Time | Start | Execs | Rows | Activity | Activity Detail |
| | | | (Estim) | | Active(s) | Active | | (Actual) | (%) | (# samples) |
==============================================================================================================================
| 0 | SELECT STATEMENT | | | | | | 1 | | | |
| -> 1 | TABLE ACCESS FULL | TB_TEST | 162K | 597 | 2 | +0 | 1 | 0 | | |
==============================================================================================================================

This is just sample for manually tuning SQL idea. If you need to monitor your SQL. Use it MONITOR hint -)