Parse CPU to Parse Elapsd非常低

aide4667

一个UPDATE语句，用info执行，一种两张表，一个数据两千万，一个数据就几十万，从凌晨6点执行到中午12点都执行不完。麻烦帮忙看下，应该是等待，但是我怎么去查看在等待什么。

xifenfei

Plan Statistics

• % Total DB Time is the Elapsed Time of the SQL statement divided into the Total Database Time multiplied by 100
  

Stat Name	Statement Total	Per Execution	% Snap Total
Elapsed Time (ms)	9,525,408		5.21
CPU Time (ms)	8,729,471		18.37
Executions	0
Buffer Gets	209,999,326		12.90
Disk Reads	1,105,467		0.85
Parse Calls	1		0.00
Rows	0
User I/O Wait Time (ms)	397,107
Cluster Wait Time (ms)	0
Application Wait Time (ms)	0
Concurrency Wait Time (ms)	0
Invalidations	0
Version Count	3
Sharable Mem(KB)	79

Back to Plan 1(PHV: 1618931016)
Back to Top

Execution Plan

Id	Operation	Name	Rows	Bytes	Cost (%CPU)	Time
0	UPDATE STATEMENT				56512 (100)
1	UPDATE	DWB_DELIV_DOC_NUM
2	HASH JOIN RIGHT SEMI		1	83	56512 (2)	00:11:19
3	TABLE ACCESS FULL	TMP_DELIV_DOC_NUM	8122	404K	10 (0)	00:00:01
4	TABLE ACCESS FULL	DWB_DELIV_DOC_NUM	7338K	223M	56430 (2)	00:11:18
5	TABLE ACCESS FULL	TMP_DELIV_DOC_NUM	1	64	10 (0)	00:00:01

• dynamic sampling used for this statement
  

Back to Plan 1(PHV: 1618931016)
Back to Top

Full SQL Text

SQL Id	SQL Text
ddrfr4nrshvv3	--???? update DWB_DELIV_DOC_NUM t set t.struck_status='??', t.efficient_time = (select efficient_time from tmp_DELIV_DOC_NUM t1 where t1.unit_id = t.disp_unit_id and t1.gdn_num=t.gdn_num) where (t.disp_unit_id, t.gdn_num) in (select unit_id, gdn_num from tmp_DELIV_DOC_NUM)

从感觉上，该sql 可以优化，建议使用sqlhc收集信息，然后上传，进一步分析