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