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時被啟用。
?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
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
 
/* cancel掉之前的查詢語句 */
 
/* 針對那些我們希望特別監視的SQL語句，可以直接使用monitor提示，強制監視 */
 
SQL> select /*+ monitor */ *  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
 
/* 可以看到這裡原語句的CPU_TIME不到1ms*/
 
/* 以下為SQL MONITOR的相關的幾個隱藏引數 */
 
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:
?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
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!