|
开发找到我说一个数据库定时任务一直没有执行，因为JOB下一次执行时间一直不动。遇到这种问题，首先要看数据库报告，因为有可能是JOB hang住了，特别是有DB link的业务。
果然，排在第一的就是这个JOB执行的存储过程。
|
Elapsed Time (s) |
Executions |
Elapsed Time per Exec (s) |
%Total |
%CPU |
%IO |
SQL Id |
SQL Module |
SQL Text |
28,676.95
0
39.39
3.09
5.63
a46qtf7mcfw2x
DECLARE job BINARY_INTEGER := ...
SQL执行的时间= 消耗CPU的时间 + 消耗IO的时间 + 其他的等待。
可以看到这个JOB执行了2万8千多秒，CPU和IO消耗的时间比较少，那等待时间在哪儿呢？可以看到就是在等待事件SQL*Net message from dblink，这个JOB里面有使用到dblink。就是这个功能。
Event
Waits |
Time(s) |
Avg wait (ms) |
% DB time |
Wait Class |
SQL*Net message from dblink
7,070,925
37,633
5
51.69
Network
DB CPU
14,958
20.54
SQL*Net more data to client
4,428,973
3,156
1
4.33
Network
看看这些会话在做什么，卡在一个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 | -----------------------------------------------------------------------------------------------------
（编辑：李大同）
【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!
