话说V$SQL_MONITOR


在Oracle Enterprise Edition - Version: 11.1.0.6之后，有一个新特性允许我们监控long running sql。
real time sql monitoring，这个特性只有设置一下参数：
statisitcs_level = TYPICAL or ALL
control_management_pack_access = DIAGNOSTIC+TUNING
才生效，并且long running sql处于并行执行或者消耗CPU资源超过5s，就会自动开启。

一，hint
MONITOR : 强制监视sql执行
NO_MONITOR :防止sql被监视

二，V$SQL_MONITOR and V$SQL_PLAN_MONITOR
V$SQL_MONITOR的信息每s刷新，当sql执行完毕，信息并不立即删除而是保持至少一分钟。
KEY NUMBER:可以和存储在V$SQL_PLAN_MONITOR的相关执行计划信息关联 .
STATUS : SQL执行状态：
EXECUTING - SQL is 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)

三，测试

  1. SELECT p.prod_id, p.prod_name, t.time_id, t.week_ending_day,  
  2. s.channel_id, s.promo_id, s.cust_id, s.amount_sold  
  3. FROM sales s, products p, times t  
  4.      ,(SELECT 1 FROM dual CONNECT BY LEVEL <= 1000000) a  
  5.      ,(SELECT 1 FROM dual CONNECT BY LEVEL <= 1000000) b  
  6. WHERE s.time_id=t.time_id AND s.prod_id = p.prod_id;  
通过V$SQL_MONITOR.BIND_XML列查询绑定信息：
  1. select xmltype(binds_xml) from v$sql_monitor where status = 'EXECUTING';  
  2. XMLTYPE(BINDS_XML)  
  3. -----------------------------------------------------------------------------------------------------------  
  4. <binds>  
  5.   <bind name=":SYS_B_1" pos="2" dty="2" dtystr="NUMBER" maxlen="22" len="2">1000000</bind>  
  6.   <bind name=":SYS_B_3" pos="4" dty="2" dtystr="NUMBER" maxlen="22" len="2">1000000</bind>  
  7. </binds>  
查找当前long running sql：
  1. set lines 200 pages 200  
  2. col sql_text for a50  
  3. col username for a12  
  4. col sid for 9999  
  5. col key for 99999999999999  
  6.   
  7. select key, sid, username,   
  8.        sql_id,   
  9.        sql_plan_hash_value plan_hash,   
  10.        elapsed_time,   
  11.        cpu_time,   
  12.        buffer_gets,   
  13.        disk_reads,   
  14.        substr(sql_text,1,50) sql_text  
  15. from v$sql_monitor  
  16. where status = 'EXECUTING';  
  17. KEY           SID  USERNAME  SQL_ID         PLAN_HASH ELAPSED_TIME   CPU_TIME  BUFFER_GETS DISK_READS SQL_TEXT  
  18. ------------ ----- -------- ------------- ----------  ------------  ---------- ----------- ---------- --------------------  
  19. 347892351063  73   SH        063m86shttbzb  2040875342     75496107   13784861      1140       3037   SELECT p.prod_id,   
查看执行计划：
  1. ID OPERATION                        PLAN_OPTIONS         PLAN_OBJECT_NAME               CARD  PLAN_COST  
  2. ---- -------------------------------- -------------------- ------------------------ ---------- ----------  
  3.    0 SELECT STATEMENT  
  4.    1  HASH JOIN                                                                        1837686       2929  
  5.    2   PART JOIN FILTER               CREATE               :BF0000                        1826         18  
  6.    3    TABLE ACCESS                  FULL                 TIMES                          1826         18  
  7.    4   HASH JOIN                                                                       1837686       2905  
  8.    5    MERGE JOIN                    CARTESIAN                                             72          7  
  9.    6     MERGE JOIN                   CARTESIAN                                              1          4  
  10.    7      VIEW                                                                               1          2  
  11.    8       CONNECT BY                 WITHOUT FILTERING  
  12.    9        FAST DUAL                                                                        1          2  
  13.   10      BUFFER                      SORT                                                   1          4  
  14.   11       VIEW                                                                              1          2  
  15.   12        CONNECT BY                WITHOUT FILTERING  
  16.   13         FAST DUAL                                                                       1          2  
  17.   14     BUFFER                       SORT                                                  72          5  
  18.   15      TABLE ACCESS                FULL                 PRODUCTS                         72          3  
  19.   16    PARTITION RANGE               JOIN-FILTER                                      1837686       2892  
  20.   17     TABLE ACCESS                 FULL                 SALES                       1837686       2892  
其实我还是喜欢用DBMS_XPLAN包来查看，格式化，信息全面;
  1. SQL_ID  063m86shttbzb, child number 0  
  2. -------------------------------------  
  3. SELECT p.prod_id, p.prod_name, t.time_id, t.week_ending_day,  
  4. s.channel_id, s.promo_id, s.cust_id, s.amount_sold FROM sales s,  
  5. products p, times t      ,(SELECT :"SYS_B_0" FROM dual CONNECT BY LEVEL  
  6. <= :"SYS_B_1") a      ,(SELECT :"SYS_B_2" FROM dual CONNECT BY LEVEL <=  
  7. :"SYS_B_3") b WHERE s.time_id=t.time_id AND s.prod_id = p.prod_id  
  8.   
  9. Plan hash value: 2040875342  
  10.   
  11. ---------------------------------------------------------------------------------------------------------------  
  12. | Id  | Operation                          | Name     | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |  
  13. ---------------------------------------------------------------------------------------------------------------  
  14. |   0 | SELECT STATEMENT                   |          |       |       |  2929 (100)|          |       |       |  
  15. |*  1 |  HASH JOIN                         |          |  1837K|   131M|  2929   (2)| 00:00:36 |       |       |  
  16. |   2 |   PART JOIN FILTER CREATE          | :BF0000  |  1826 | 29216 |    18   (0)| 00:00:01 |       |       |  
  17. |   3 |    TABLE ACCESS FULL               | TIMES    |  1826 | 29216 |    18   (0)| 00:00:01 |       |       |  
  18. |*  4 |   HASH JOIN                        |          |  1837K|   103M|  2905   (1)| 00:00:35 |       |       |  
  19. |   5 |    MERGE JOIN CARTESIAN            |          |    72 |  2160 |     7   (0)| 00:00:01 |       |       |  
  20. |   6 |     MERGE JOIN CARTESIAN           |          |     1 |       |     4   (0)| 00:00:01 |       |       |  
  21. |   7 |      VIEW                          |          |     1 |       |     2   (0)| 00:00:01 |       |       |  
  22. |   8 |       CONNECT BY WITHOUT FILTERING |          |       |       |            |          |       |       |  
  23. |   9 |        FAST DUAL                   |          |     1 |       |     2   (0)| 00:00:01 |       |       |  
  24. |  10 |      BUFFER SORT                   |          |     1 |       |     4   (0)| 00:00:01 |       |       |  
  25. |  11 |       VIEW                         |          |     1 |       |     2   (0)| 00:00:01 |       |       |  
  26. |  12 |        CONNECT BY WITHOUT FILTERING|          |       |       |            |          |       |       |  
  27. |  13 |         FAST DUAL                  |          |     1 |       |     2   (0)| 00:00:01 |       |       |  
  28. |  14 |     BUFFER SORT                    |          |    72 |  2160 |     5   (0)| 00:00:01 |       |       |  
  29. |  15 |      TABLE ACCESS FULL             | PRODUCTS |    72 |  2160 |     3   (0)| 00:00:01 |       |       |  
  30. |  16 |    PARTITION RANGE JOIN-FILTER     |          |  1837K|    50M|  2892   (1)| 00:00:35 |:BF0000|:BF0000|  
  31. |  17 |     TABLE ACCESS FULL              | SALES    |  1837K|    50M|  2892   (1)| 00:00:35 |:BF0000|:BF0000|  
  32. ---------------------------------------------------------------------------------------------------------------  
  33.   
  34. Query Block Name / Object Alias (identified by operation id):  
  35. -------------------------------------------------------------  
  36.   
  37.    1 - SEL$1  
  38.    3 - SEL$1 / T@SEL$1  
  39.    7 - SEL$2 / A@SEL$1  
  40.    8 - SEL$2  
  41.    9 - SEL$2 / DUAL@SEL$2  
  42.   11 - SEL$3 / B@SEL$1  
  43.   12 - SEL$3  
  44.   13 - SEL$3 / DUAL@SEL$3  
  45.   15 - SEL$1 / P@SEL$1  
  46.   17 - SEL$1 / S@SEL$1  
  47.   
  48. Predicate Information (identified by operation id):  
  49. ---------------------------------------------------  
  50.   
  51.    1 - access("S"."TIME_ID"="T"."TIME_ID")  
  52.    4 - access("S"."PROD_ID"="P"."PROD_ID")  
  53.   
  54. Column Projection Information (identified by operation id):  
  55. -----------------------------------------------------------  
  56.   
  57.    1 - (#keys=1) "T"."TIME_ID"[DATE,7], "T"."WEEK_ENDING_DAY"[DATE,7], "P"."PROD_ID"[NUMBER,22],  
  58.        "P"."PROD_NAME"[VARCHAR2,50], "S"."AMOUNT_SOLD"[NUMBER,22], "S"."CUST_ID"[NUMBER,22],  
  59.        "S"."PROMO_ID"[NUMBER,22], "S"."CHANNEL_ID"[NUMBER,22]  
  60.    2 - "T"."TIME_ID"[DATE,7], "T"."TIME_ID"[DATE,7], "T"."WEEK_ENDING_DAY"[DATE,7]  
  61.    3 - "T"."TIME_ID"[DATE,7], "T"."WEEK_ENDING_DAY"[DATE,7]  
  62.    4 - (#keys=1) "P"."PROD_ID"[NUMBER,22], "P"."PROD_NAME"[VARCHAR2,50], "S"."AMOUNT_SOLD"[NUMBER,22],  
  63.        "S"."CUST_ID"[NUMBER,22], "S"."TIME_ID"[DATE,7], "S"."CHANNEL_ID"[NUMBER,22], "S"."PROMO_ID"[NUMBER,22]  
  64.    5 - "P"."PROD_ID"[NUMBER,22], "P"."PROD_NAME"[VARCHAR2,50]  
  65.    8 - LEVEL[4]  
  66.   10 - (#keys=0)  
  67.   12 - LEVEL[4]  
  68.   14 - (#keys=0) "P"."PROD_ID"[NUMBER,22], "P"."PROD_NAME"[VARCHAR2,50]  
  69.   15 - "P"."PROD_ID"[NUMBER,22], "P"."PROD_NAME"[VARCHAR2,50]  
  70.   16 - "S"."PROD_ID"[NUMBER,22], "S"."CUST_ID"[NUMBER,22], "S"."TIME_ID"[DATE,7],  
  71.        "S"."CHANNEL_ID"[NUMBER,22], "S"."PROMO_ID"[NUMBER,22], "S"."AMOUNT_SOLD"[NUMBER,22]  
  72.   17 - "S"."PROD_ID"[NUMBER,22], "S"."CUST_ID"[NUMBER,22], "S"."TIME_ID"[DATE,7],  
  73.        "S"."CHANNEL_ID"[NUMBER,22], "S"."PROMO_ID"[NUMBER,22], "S"."AMOUNT_SOLD"[NUMBER,22]  

相关内容