话说V$SQL_MONITOR

最新推荐文章于 2023-01-11 09:50:20 发布

出埃及

最新推荐文章于 2023-01-11 09:50:20 发布

阅读量1.3k

点赞数

分类专栏： Oracle 11g New Feature 文章标签： sql access join date buffer table

本文链接：https://blog.csdn.net/coast_lichao/article/details/7581720

版权

Oracle 11g New Feature 专栏收录该内容

6 篇文章 0 订阅

订阅专栏

在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)

三，测试

SELECT p.prod_id, p.prod_name, t.time_id, t.week_ending_day,
s.channel_id, s.promo_id, s.cust_id, s.amount_sold
FROM sales s, products p, times t
     ,(SELECT 1 FROM dual CONNECT BY LEVEL <= 1000000) a
     ,(SELECT 1 FROM dual CONNECT BY LEVEL <= 1000000) b
WHERE s.time_id=t.time_id AND s.prod_id = p.prod_id;

通过V$SQL_MONITOR.BIND_XML列查询绑定信息：

select xmltype(binds_xml) from v$sql_monitor where status = 'EXECUTING';
XMLTYPE(BINDS_XML)
-----------------------------------------------------------------------------------------------------------
<binds>
  <bind name=":SYS_B_1" pos="2" dty="2" dtystr="NUMBER" maxlen="22" len="2">1000000</bind>
  <bind name=":SYS_B_3" pos="4" dty="2" dtystr="NUMBER" maxlen="22" len="2">1000000</bind>
</binds>

查找当前long running sql：

set lines 200 pages 200
col sql_text for a50
col username for a12
col sid for 9999
col key for 99999999999999

select key, sid, username, 
       sql_id, 
       sql_plan_hash_value plan_hash, 
       elapsed_time, 
       cpu_time, 
       buffer_gets, 
       disk_reads, 
       substr(sql_text,1,50) sql_text
from v$sql_monitor
where status = 'EXECUTING';
KEY           SID  USERNAME  SQL_ID         PLAN_HASH ELAPSED_TIME   CPU_TIME  BUFFER_GETS DISK_READS SQL_TEXT
------------ ----- -------- ------------- ----------  ------------  ---------- ----------- ---------- --------------------
347892351063  73   SH        063m86shttbzb  2040875342     75496107   13784861      1140       3037   SELECT p.prod_id,

查看执行计划：

ID OPERATION                        PLAN_OPTIONS         PLAN_OBJECT_NAME               CARD  PLAN_COST
---- -------------------------------- -------------------- ------------------------ ---------- ----------
   0 SELECT STATEMENT
   1  HASH JOIN                                                                        1837686       2929
   2   PART JOIN FILTER               CREATE               :BF0000                        1826         18
   3    TABLE ACCESS                  FULL                 TIMES                          1826         18
   4   HASH JOIN                                                                       1837686       2905
   5    MERGE JOIN                    CARTESIAN                                             72          7
   6     MERGE JOIN                   CARTESIAN                                              1          4
   7      VIEW                                                                               1          2
   8       CONNECT BY                 WITHOUT FILTERING
   9        FAST DUAL                                                                        1          2
  10      BUFFER                      SORT                                                   1          4
  11       VIEW                                                                              1          2
  12        CONNECT BY                WITHOUT FILTERING
  13         FAST DUAL                                                                       1          2
  14     BUFFER                       SORT                                                  72          5
  15      TABLE ACCESS                FULL                 PRODUCTS                         72          3
  16    PARTITION RANGE               JOIN-FILTER                                      1837686       2892
  17     TABLE ACCESS                 FULL                 SALES                       1837686       2892

其实我还是喜欢用DBMS_XPLAN包来查看，格式化，信息全面;

SQL_ID  063m86shttbzb, child number 0
-------------------------------------
SELECT p.prod_id, p.prod_name, t.time_id, t.week_ending_day,
s.channel_id, s.promo_id, s.cust_id, s.amount_sold FROM sales s,
products p, times t      ,(SELECT :"SYS_B_0" FROM dual CONNECT BY LEVEL
<= :"SYS_B_1") a      ,(SELECT :"SYS_B_2" FROM dual CONNECT BY LEVEL <=
:"SYS_B_3") b WHERE s.time_id=t.time_id AND s.prod_id = p.prod_id

Plan hash value: 2040875342

---------------------------------------------------------------------------------------------------------------
| Id  | Operation                          | Name     | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
---------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                   |          |       |       |  2929 (100)|          |       |       |
|*  1 |  HASH JOIN                         |          |  1837K|   131M|  2929   (2)| 00:00:36 |       |       |
|   2 |   PART JOIN FILTER CREATE          | :BF0000  |  1826 | 29216 |    18   (0)| 00:00:01 |       |       |
|   3 |    TABLE ACCESS FULL               | TIMES    |  1826 | 29216 |    18   (0)| 00:00:01 |       |       |
|*  4 |   HASH JOIN                        |          |  1837K|   103M|  2905   (1)| 00:00:35 |       |       |
|   5 |    MERGE JOIN CARTESIAN            |          |    72 |  2160 |     7   (0)| 00:00:01 |       |       |
|   6 |     MERGE JOIN CARTESIAN           |          |     1 |       |     4   (0)| 00:00:01 |       |       |
|   7 |      VIEW                          |          |     1 |       |     2   (0)| 00:00:01 |       |       |
|   8 |       CONNECT BY WITHOUT FILTERING |          |       |       |            |          |       |       |
|   9 |        FAST DUAL                   |          |     1 |       |     2   (0)| 00:00:01 |       |       |
|  10 |      BUFFER SORT                   |          |     1 |       |     4   (0)| 00:00:01 |       |       |
|  11 |       VIEW                         |          |     1 |       |     2   (0)| 00:00:01 |       |       |
|  12 |        CONNECT BY WITHOUT FILTERING|          |       |       |            |          |       |       |
|  13 |         FAST DUAL                  |          |     1 |       |     2   (0)| 00:00:01 |       |       |
|  14 |     BUFFER SORT                    |          |    72 |  2160 |     5   (0)| 00:00:01 |       |       |
|  15 |      TABLE ACCESS FULL             | PRODUCTS |    72 |  2160 |     3   (0)| 00:00:01 |       |       |
|  16 |    PARTITION RANGE JOIN-FILTER     |          |  1837K|    50M|  2892   (1)| 00:00:35 |:BF0000|:BF0000|
|  17 |     TABLE ACCESS FULL              | SALES    |  1837K|    50M|  2892   (1)| 00:00:35 |:BF0000|:BF0000|
---------------------------------------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

   1 - SEL$1
   3 - SEL$1 / T@SEL$1
   7 - SEL$2 / A@SEL$1
   8 - SEL$2
   9 - SEL$2 / DUAL@SEL$2
  11 - SEL$3 / B@SEL$1
  12 - SEL$3
  13 - SEL$3 / DUAL@SEL$3
  15 - SEL$1 / P@SEL$1
  17 - SEL$1 / S@SEL$1

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("S"."TIME_ID"="T"."TIME_ID")
   4 - access("S"."PROD_ID"="P"."PROD_ID")

Column Projection Information (identified by operation id):
-----------------------------------------------------------

   1 - (#keys=1) "T"."TIME_ID"[DATE,7], "T"."WEEK_ENDING_DAY"[DATE,7], "P"."PROD_ID"[NUMBER,22],
       "P"."PROD_NAME"[VARCHAR2,50], "S"."AMOUNT_SOLD"[NUMBER,22], "S"."CUST_ID"[NUMBER,22],
       "S"."PROMO_ID"[NUMBER,22], "S"."CHANNEL_ID"[NUMBER,22]
   2 - "T"."TIME_ID"[DATE,7], "T"."TIME_ID"[DATE,7], "T"."WEEK_ENDING_DAY"[DATE,7]
   3 - "T"."TIME_ID"[DATE,7], "T"."WEEK_ENDING_DAY"[DATE,7]
   4 - (#keys=1) "P"."PROD_ID"[NUMBER,22], "P"."PROD_NAME"[VARCHAR2,50], "S"."AMOUNT_SOLD"[NUMBER,22],
       "S"."CUST_ID"[NUMBER,22], "S"."TIME_ID"[DATE,7], "S"."CHANNEL_ID"[NUMBER,22], "S"."PROMO_ID"[NUMBER,22]
   5 - "P"."PROD_ID"[NUMBER,22], "P"."PROD_NAME"[VARCHAR2,50]
   8 - LEVEL[4]
  10 - (#keys=0)
  12 - LEVEL[4]
  14 - (#keys=0) "P"."PROD_ID"[NUMBER,22], "P"."PROD_NAME"[VARCHAR2,50]
  15 - "P"."PROD_ID"[NUMBER,22], "P"."PROD_NAME"[VARCHAR2,50]
  16 - "S"."PROD_ID"[NUMBER,22], "S"."CUST_ID"[NUMBER,22], "S"."TIME_ID"[DATE,7],
       "S"."CHANNEL_ID"[NUMBER,22], "S"."PROMO_ID"[NUMBER,22], "S"."AMOUNT_SOLD"[NUMBER,22]
  17 - "S"."PROD_ID"[NUMBER,22], "S"."CUST_ID"[NUMBER,22], "S"."TIME_ID"[DATE,7],
       "S"."CHANNEL_ID"[NUMBER,22], "S"."PROMO_ID"[NUMBER,22], "S"."AMOUNT_SOLD"[NUMBER,22]

出埃及

关注

0
点赞
踩
2

收藏

觉得还不错? 一键收藏
0
评论
话说V$SQL_MONITOR

在oracle Enterprise Edition - Version: 11.1.0.6之后，有一个新特性允许我们监控long running sql。real time sql monitoring，这个特性只有设置一下参数：statisitcs_level = TYPICAL or ALLcontrol_management_pack_access = DIAGNOSTIC+T
复制链接

扫一扫