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

    开发找到我说一个数据库定时任务一直没有执行，因为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 |
-----------------------------------------------------------------------------------------------------