Oracle空闲等待事件SQL*Net message from dblink不一定没问题
来源:互联网 发布:启凡网络是培训的吗 编辑:程序博客网 时间:2024/06/13 19:33
开发找到我说一个数据库定时任务一直没有执行,因为JOB下一次执行时间一直不动。遇到这种问题,首先要看数据库报告,因为有可能是JOB hang住了,特别是有DB link的业务。
果然,排在第一的就是这个JOB执行的存储过程。
SQL执行的时间= 消耗CPU的时间 + 消耗IO的时间 + 其他的等待。
可以看到这个JOB执行了2万8千多秒,CPU和IO消耗的时间比较少,那等待时间在哪儿呢?可以看到就是在等待事件SQL*Net message from dblink,这个JOB里面有使用到dblink。就是这个功能。
看看这些会话在做什么,卡在一个insert语句上:
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;
问题SQL如下,很明显走错了执行计划,IP_PLAN_EXTENDS有上百万的数据,如果上一个结果集返回1万条数据,那这张表会被循环全表扫描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 |
-----------------------------------------------------------------------------------------------------------
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不一定没问题
- 空闲等待事件SQL*Net message from dblink等待时间长到底要不要管?
- 网络上的等待事件 —— SQL*Net message from client/dblink
- SQL*Net message from client 等待事件
- 监控SQL*Net message from client等待事件
- 网络上的等待事件 —— SQL*Net message to client/dblink
- 网络上的等待事件 —— SQL*Net more data from client/dblink
- 转:Oracle系统非空闲等待事件
- 非空闲等待事件
- SQL*Net message from client 事件产生的原因分析
- 网络上的等待事件 —— SQL*Net more data to client/dblink
- 网络上的等待事件 —— SQL*Net break/reset to client & SQL*Net break/reset to dblink
- sql net message from client
- SQL*Net message from client
- Oracle等待事件之五——SQL*NET相关等待事件
- 一个session已经ACTIVE20多小时,等待事件SQL*Net more data from client
- 等待事件:reliable message
- SQL*Net message from client 事件产生的原因分析 (转)
- Linux下redis的安装以及使用
- 数据库事务
- 自媒体之百家号领域分类以及领域单价排序
- WFST 语音识别
- Java堆、栈和常量池以及相关String的详细讲解(经典中的经典)
- Oracle空闲等待事件SQL*Net message from dblink不一定没问题
- ArrayBlockingQueue和LinkedBlockingQueue
- html5 常用 标签
- HTML02—表格、图像、表单、超链接
- “玲珑杯”线上赛 Round #15 河南专场 A -- Reverse the lights DP
- vue2.0 语法
- 日常语无伦次,今天来总结table
- TCP的特点及报文格式
- netty 项目打包发布至liunx