Oracle空闲等待事件SQL*Net message from dblink不一定没问题

大佬教程收集整理的这篇文章主要介绍了Oracle空闲等待事件SQL*Net message from dblink不一定没问题，大佬教程大佬觉得挺不错的，现在分享给大家，也给大家做个参考。

@H_301_1@ 开发找到我说一个数据库定时任务一直没有执行，因为JOB下一次执行时间一直不动。遇到这种问题，首先要看数据库报告，因为有可能是JOB hang住了，特别是有DB link的业务。

@H_301_1@ 果然，排在第一的就是这个JOB执行的存储过程。

@H_301_1@

@H_301_47@ a46qtf7mcfw2x @H_301_47@ @H_301_47@ DECLARE job BINARY_IntegeR := ...

Elapsed Time (s)@H_874_10@	EXECUTIONS@H_874_10@	Elapsed Time per Exec (s)@H_874_10@	%@R_614_10586@l@H_874_10@	%cpu@H_874_10@	%IO@H_874_10@	sql ID@H_874_10@	sql Module@H_874_10@	sql Text@H_874_10@
28,676.95	0		39.39	3.09	5.63

@H_301_1@ sql执行的时间= 消耗cpu的时间 + 消耗IO的时间 + 其他的等待。

@H_301_1@ 可以看到这个JOB执行了2万8千多秒，cpu和IO消耗的时间比较少，那等待时间在哪儿呢？可以看到就是在等待事件sql*Net message from dblink，这个JOB里面有使用到dblink。就是这个功能。

@H_301_1@

Event @H_301_47@ sql*Net message from dblink @H_301_47@ Network @H_301_47@ sql*Net more data to clIEnt @H_301_47@ Network

Waits@H_874_10@	Time(s)@H_874_10@	Avg wait (ms)@H_874_10@	% DB time@H_874_10@	Wait Class@H_874_10@
7,070,925	37,633	5	51.69
DB cpu		14,958	20.54
4,428,973	3,156	1	4.33

@H_301_1@ 看看这些会话在做什么，卡在一个insert语句上：

@H_301_1@SELEct s.INST_ID,'kill -9 ' || p.SPID,ss.sql_TEXT,s.program
from gv$session s,gv$sql ss,gv$process p
where s.EVENT = 'sql*Net message from dblink'
and s.sql_ID = ss.sql_ID
and s.INST_ID = ss.INST_ID
and p.INST_ID = s.INST_ID
and p.ADDR = s.paddr;

@H_301_1@

@H_301_1@问题sql如下，很明显走错了执行计划，@H_301_1@IP_PLAN_EXTENDS有上百万的数据，如果上一个结果集返回1万条数据，那这张表会被循环全表扫描1万次：

@H_301_1@INSERT INTO GdplAN_EXTENDS
(...........)
(SELECT ...........
FROM PLAN_EXTENDS
WHERE NOT EXISTS (SELECT 1
FROM GdplAN_EXTENDS
WHERE PLAN_EXTENDs.PLAN_PROjeCT_ID =
GdplAN_EXTENDs.PLAN_PROjeCT_ID)
AND EXISTS
(SELECT 1
FROM PLAN_PROjeCT
WHERE PLAN_PROjeCT.PLAN_PROjeCT_ID =
PLAN_EXTENDs.PLAN_PROjeCT_ID
AND PLAN_PROjeCT.DEL_FLAG <> 1
AND PLAN_PROjeCT.PROjeCT_STATUS = 'IP60'
AND PLAN_PROjeCT.AUDIT_STATUS = 3
AND PLAN_PROjeCT.PLAN_PROjeCT_TYPE = 1
AND PLAN_PROjeCT.PROjeCT_TYPE_CODE = 'Info'
AND PLAN_PROjeCT.DATA_OWN_AREA liKE '00%'))

执行计划
----------------------------------------------------------
Plan hash value: 1538111555


-----------------------------------------------------------------------------------------------------------
| ID | Operation | name | Rows | Bytes | Cost (%cpu)| Time |
-----------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 169 | 8 (0)| 00:00:01 |
|* 1 | FILTER | | | | | |
| 2 | nested loops SEMI | | 1 | 169 | 7 (0)| 00:00:01 |
| 3 | table ACCESS FulL | PLAN_EXTENDS | 925 | 74000 | 7 (0)| 00:00:01 |
|* 4 | table ACCESS BY GLOBAL INDEX ROWID| PLAN_PROjeCT | 1 | 89 | 0 (0)| 00:00:01 |
|* 5 | INDEX UNIQUE SCAN | PK_PLAN_PROjeCT | 1 | | 0 (0)| 00:00:01 |
| 6 | REMOTE | PLAN_EXTENDS | 1 | 27 | 1 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------------

@H_301_1@sql优化： mergE into GdplAN_EXTENDS using (SELECT ........... FROM PLAN_EXTENDS,PLAN_PROjeCT WHERE PLAN_PROjeCT.PLAN_PROjeCT_ID = PLAN_EXTENDs.PLAN_PROjeCT_ID AND PLAN_PROjeCT.DEL_FLAG <> 1 AND PLAN_PROjeCT.PROjeCT_STATUS = 'IP60' AND PLAN_PROjeCT.AUDIT_STATUS = 3 AND PLAN_PROjeCT.PLAN_PROjeCT_TYPE = 1 AND PLAN_PROjeCT.PROjeCT_TYPE_CODE = 'Info' AND PLAN_PROjeCT.DATA_OWN_AREA liKE '00%') IP on(Ip.pLAN_PROjeCT_ID = GdplAN_EXTENDs.PLAN_PROjeCT_ID) when not matched then INSERT (...........) values ( ...........); 执行计划 ---------------------------------------------------------- Plan hash value: 1333954672 ----------------------------------------------------------------------------------------------------- | ID | Operation | name | Rows | Bytes | Cost (%cpu)| Time | ----------------------------------------------------------------------------------------------------- | 0 | MERGE STATEMENT REMOTE | | 1 | 18650 | 23 (9)| 00:00:01 | | 1 | MERGE | PLAN_EXTENDS | | | | | | 2 | VIEW | | | | | | | 3 | nested loops OUTER | | 1 | 18650 | 23 (9)| 00:00:01 | | 4 | VIEW | | 1 | 18493 | 22 (10)| 00:00:01 | | 5 | REMOTE | | | | | | | 6 | table ACCESS BY INDEX ROWID| PLAN_EXTENDS | 1 | 157 | 1 (0)| 00:00:01 | |* 7 | INDEX UNIQUE SCAN | PK_PLAN_EXTENDS | 1 | | 0 (0)| 00:00:01 | -----------------------------------------------------------------------------------------------------

大佬总结

以上是大佬教程为你收集整理的Oracle空闲等待事件SQL*Net message from dblink不一定没问题全部内容，希望文章能够帮你解决Oracle空闲等待事件SQL*Net message from dblink不一定没问题所遇到的程序开发问题。

如果觉得大佬教程网站内容还不错，欢迎将大佬教程推荐给程序员好友。

本图文内容来源于网友网络收集整理提供，作为学习参考使用，版权属于原作者。
如您有任何意见或建议可联系处理。小编QQ：384754419，请注明来意。