11g中引入了新的動态性能視圖V$SQL_MONITOR，該視圖用以顯示Oracle監視的SQL語句資訊。SQL監視會對那些并行執行或者消耗5秒以上cpu時間或I/O時間的SQL語句自動啟動，同時在V$SQL_MONITOR視圖中産生一條記錄。當SQL語句正在執行，V$SQL_MONITOR視圖中的統計資訊将被實時重新整理，頻率為每秒1次。SQL語句執行完成後，監視資訊将不會被立即删除，Oracle會保證相關記錄儲存一分鐘(由參數_sqlmon_recycle_time所控制，預設為60s)，最終這些記錄都會被删除并被重用。這一新的SQL性能監視特性僅在CONTROL_MANAGEMENT_PACK_ACCESS為DIAGNOSTIC+TUNING和STATISTICS_LEVEL為ALL|TYPICAL時被啟用。
SQL> conn maclean/maclean;
Connected.
SQL> show parameter control_management_pack_access
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
control_management_pack_access string DIAGNOSTIC+TUNING
SQL> show parameter STATISTICS_LEVEL
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
statistics_level string TYPICAL
SQL> select * from v$sql_monitor where username='MACLEAN';
no rows selected
select count(*) from sys.obj$,sys.tab$,sys.col$;
........................
SQL> select key,status,sql_id,cpu_time from v$sql_monitor where username='MACLEAN';
KEY STATUS SQL_ID CPU_TIME
---------- ------------------- ------------- ----------
9191230013 EXECUTING 1tc94vh92f68b 52915539
SQL> select key,status,sql_id,cpu_time from v$sql_monitor where username='MACLEAN';
KEY STATUS SQL_ID CPU_TIME
---------- ------------------- ------------- ----------
9191230013 EXECUTING 1tc94vh92f68b 72899267
SQL> select plan_line_id, plan_operation, plan_options starts, output_rows
2 from v$sql_plan_monitor
3 where key = 919123001346;
PLAN_LINE_ID PLAN_OPERATION STARTS OUTPUT_ROWS
------------ ------------------------------ ------------------------------ -----------
0 SELECT STATEMENT 0
1 SORT AGGREGATE 0
2 MERGE JOIN CARTESIAN 4277724845
3 MERGE JOIN CARTESIAN 70256
4 TABLE ACCESS FULL 1
5 BUFFER SORT 70256
6 INDEX FAST FULL SCAN 73378
7 BUFFER SORT 4277724845
8 INDEX FAST FULL SCAN 90611
9 rows selected
SQL> select * from dual where 1=2;
no rows selected
SQL> select key, status, sql_id, cpu_time
2 from v$sql_monitor
3 where username = 'MACLEAN'
4 and sql_text like '%monitor%';
KEY STATUS SQL_ID CPU_TIME
---------- ------------------- ------------- ----------
7.2155E+11 DONE (ALL ROWS) 2fr8stwgt15mw 0
SQL> col describ for a80;
SQL> SELECT x.ksppinm NAME, y.ksppstvl VALUE, x.ksppdesc describ
2 FROM SYS.x$ksppi x, SYS.x$ksppcv y
3 WHERE x.inst_id = USERENV ('Instance')
4 AND y.inst_id = USERENV ('Instance')
5 AND x.indx = y.indx
6 AND x.ksppinm LIKE '%sqlmon%'
7 order by x.ksppinm;
NAME VALUE DESCRIB
------------------------------ ---------- --------------------------------------------------------------------------------
_sqlmon_binds_xml_format default format of column binds_xml in [G]V$SQL_MONITOR
_sqlmon_max_plan 80 Maximum number of plans entry that can be monitored. Defaults to 20 per CPU
_sqlmon_max_planlines 300 Number of plan lines beyond which a plan cannot be monitored
_sqlmon_recycle_time 60 Minimum time (in s) to wait before a plan entry can be recycled
_sqlmon_threshold 5 CPU/IO time threshold before a statement is monitored. 0 is disabled
11g中通過以上v$SQL_MONITOR和V$SQL_PLAN_MONITOR視圖，我們可以很友善地實時找出系統中可能引起性能問題的SQL語句。此外SQL監視也內建到了DBMS_AUTOTUNE包中，DBMS_SQLTUNE.REPORT_SQL_MONITOR()過程可以幫助我們高效地找出實時系統中的性能問題SQL:
SQL> set long 99999;
SQL> set linesiz 300 pagesize 2000;
SQL> select dbms_sqltune.report_sql_monitor from dual;
REPORT_SQL_MONITOR
--------------------------------------------------------------------------------
SQL Monitoring Report
SQL Text
------------------------------
select count(*) from sys.obj$,sys.tab$,sys.col$
Global Information
------------------------------
Status : EXECUTING
Instance ID : 1
Session : MACLEAN (6:255)
SQL ID : cz2bwj0f6ayr0
SQL Execution ID : 16777216
Execution Started : 09/16/2010 14:19:43
First Refresh Time : 09/16/2010 14:19:51
Last Refresh Time : 09/16/2010 14:21:57
Duration : 135s
Module/Action : SQL*Plus/-
Service : SYS$USERS
Program : sqlplus.exe
Global Stats
=========================================
| Elapsed | Cpu | Other | Buffer |
| Time(s) | Time(s) | Waits(s) | Gets |
=========================================
| 134 | 132 | 1.82 | 437 |
=========================================
SQL Plan Monitoring Details (Plan Hash Value=4003357142)
==============================================================================================
=============================================
| Id | Operation | Name | Rows | Cost | Time | Start | Execs | Rows | Mem | Act
ivity | Activity Detail |
| | | | (Estim) | | Active(s) | Active | | (
Actual) | | (%) | (# samples) |
==================================================================================================================
=========================
| 0 | SELECT STATEMENT | | | | | | 1 |
| | | |
| -> 1 | SORT AGGREGATE | | 1 | | 127 | +8 | 1 | 0 | |
2.24 | Cpu (3) |
| -> 2 | MERGE JOIN CARTESIAN | | 808G | 477M | 127 | +8 | 1 |
2G | | | |
| -> 3 | MERGE JOIN CARTESIAN | | 14M | 9809 | 127 | +8 | 1 | 27462 | |
| |
| -> 4 | TABLE ACCESS FULL | TAB$ | 1107 | 201 | 127 | +8 | 1 |
1 | | | |
| -> 5 | BUFFER SORT | | 12815 | 9607 | 127 | +8 | 1 | 27462 | 886K |
| |
| 6 | INDEX FAST FULL SCAN | I_OBJ1 | 12815 | 9 | 1 | +8 | 1 |
73378 | | | |
| -> 7 | BUFFER SORT | | 56957 | 477M | 134 | +1 | 27462 | 2G | 1M |
97.76 | Cpu (131) |
| 8 | INDEX FAST FULL SCAN | I_COL3 | 56957 | 34 | 1 | +8 | 1 |
90611 | | | |
==================================================================================================================
That's cool!