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

ゞ浴缸里的玫瑰 2022-06-13 04:23 1487阅读 0赞

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

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

<table style="font-family:Arial,Helvetica,Geneva,sans-serif"> 
 <tbody> 
  <tr> 
   <th style="font-size:8pt; font-family:Arial,Helvetica,Geneva,sans-serif; color:white; padding-left:4px; padding-right:4px; padding-bottom:2px; background:rgb(0,102,204)"> Elapsed Time (s)</th> 
   <th style="font-size:8pt; font-family:Arial,Helvetica,Geneva,sans-serif; color:white; padding-left:4px; padding-right:4px; padding-bottom:2px; background:rgb(0,102,204)"> Executions</th> 
   <th style="font-size:8pt; font-family:Arial,Helvetica,Geneva,sans-serif; color:white; padding-left:4px; padding-right:4px; padding-bottom:2px; background:rgb(0,102,204)"> Elapsed Time per Exec (s)</th> 
   <th style="font-size:8pt; font-family:Arial,Helvetica,Geneva,sans-serif; color:white; padding-left:4px; padding-right:4px; padding-bottom:2px; background:rgb(0,102,204)"> %Total</th> 
   <th style="font-size:8pt; font-family:Arial,Helvetica,Geneva,sans-serif; color:white; padding-left:4px; padding-right:4px; padding-bottom:2px; background:rgb(0,102,204)"> %CPU</th> 
   <th style="font-size:8pt; font-family:Arial,Helvetica,Geneva,sans-serif; color:white; padding-left:4px; padding-right:4px; padding-bottom:2px; background:rgb(0,102,204)"> %IO</th> 
   <th style="font-size:8pt; font-family:Arial,Helvetica,Geneva,sans-serif; color:white; padding-left:4px; padding-right:4px; padding-bottom:2px; background:rgb(0,102,204)"> SQL Id</th> 
   <th style="font-size:8pt; font-family:Arial,Helvetica,Geneva,sans-serif; color:white; padding-left:4px; padding-right:4px; padding-bottom:2px; background:rgb(0,102,204)"> SQL Module</th> 
   <th style="font-size:8pt; font-family:Arial,Helvetica,Geneva,sans-serif; color:white; padding-left:4px; padding-right:4px; padding-bottom:2px; background:rgb(0,102,204)"> SQL Text</th> 
  </tr> 
  <tr> 
   <td align="right" style="font-size:8pt; font-family:Arial,Helvetica,Geneva,sans-serif; vertical-align:top; background:rgb(255,255,204)"> 28,676.95</td> 
   <td align="right" style="font-size:8pt; font-family:Arial,Helvetica,Geneva,sans-serif; vertical-align:top; background:rgb(255,255,204)"> 0</td> 
   <td align="right" style="font-size:8pt; font-family:Arial,Helvetica,Geneva,sans-serif; vertical-align:top; background:rgb(255,255,204)"> &nbsp;</td> 
   <td align="right" style="font-size:8pt; font-family:Arial,Helvetica,Geneva,sans-serif; vertical-align:top; background:rgb(255,255,204)"> 39.39</td> 
   <td align="right" style="font-size:8pt; font-family:Arial,Helvetica,Geneva,sans-serif; vertical-align:top; background:rgb(255,255,204)"> 3.09</td> 
   <td align="right" style="font-size:8pt; font-family:Arial,Helvetica,Geneva,sans-serif; vertical-align:top; background:rgb(255,255,204)"> 5.63</td> 
   <td style="font-size:8pt; font-family:Arial,Helvetica,Geneva,sans-serif; vertical-align:top; background:rgb(255,255,204)"> <a style="font-weight:bold; font-size:8pt; font-family:Arial,Helvetica,sans-serif; color:rgb(102,51,0); vertical-align:top; margin-top:0pt; margin-bottom:0pt" rel="nofollow">a46qtf7mcfw2x</a></td> 
   <td style="font-size:8pt; font-family:Arial,Helvetica,Geneva,sans-serif; vertical-align:top; background:rgb(255,255,204)"> &nbsp;</td> 
   <td style="font-size:8pt; font-family:Arial,Helvetica,Geneva,sans-serif; vertical-align:top; background:rgb(255,255,204)"> DECLARE job BINARY_INTEGER := ...</td> 
  </tr> 
 </tbody> 
</table>

SQL执行的时间= 消耗CPU的时间 + 消耗IO的时间 + 其他的等待。

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

<table style="font-family:Arial,Helvetica,Geneva,sans-serif"> 
 <tbody> 
  <tr> 
   <th style="font-size:8pt; font-family:Arial,Helvetica,Geneva,sans-serif; color:white; padding-left:4px; padding-right:4px; padding-bottom:2px; background:rgb(0,102,204)"> Event</th> 
   <th style="font-size:8pt; font-family:Arial,Helvetica,Geneva,sans-serif; color:white; padding-left:4px; padding-right:4px; padding-bottom:2px; background:rgb(0,102,204)"> Waits</th> 
   <th style="font-size:8pt; font-family:Arial,Helvetica,Geneva,sans-serif; color:white; padding-left:4px; padding-right:4px; padding-bottom:2px; background:rgb(0,102,204)"> Time(s)</th> 
   <th style="font-size:8pt; font-family:Arial,Helvetica,Geneva,sans-serif; color:white; padding-left:4px; padding-right:4px; padding-bottom:2px; background:rgb(0,102,204)"> Avg wait (ms)</th> 
   <th style="font-size:8pt; font-family:Arial,Helvetica,Geneva,sans-serif; color:white; padding-left:4px; padding-right:4px; padding-bottom:2px; background:rgb(0,102,204)"> % DB time</th> 
   <th style="font-size:8pt; font-family:Arial,Helvetica,Geneva,sans-serif; color:white; padding-left:4px; padding-right:4px; padding-bottom:2px; background:rgb(0,102,204)"> Wait Class</th> 
  </tr> 
  <tr> 
   <td style="font-size:8pt; font-family:Arial,Helvetica,Geneva,sans-serif; vertical-align:top; background:rgb(255,255,204)"> SQL*Net message from dblink</td> 
   <td align="right" style="font-size:8pt; font-family:Arial,Helvetica,Geneva,sans-serif; vertical-align:top; background:rgb(255,255,204)"> 7,070,925</td> 
   <td align="right" style="font-size:8pt; font-family:Arial,Helvetica,Geneva,sans-serif; vertical-align:top; background:rgb(255,255,204)"> 37,633</td> 
   <td align="right" style="font-size:8pt; font-family:Arial,Helvetica,Geneva,sans-serif; vertical-align:top; background:rgb(255,255,204)"> 5</td> 
   <td align="right" style="font-size:8pt; font-family:Arial,Helvetica,Geneva,sans-serif; vertical-align:top; background:rgb(255,255,204)"> 51.69</td> 
   <td style="font-size:8pt; font-family:Arial,Helvetica,Geneva,sans-serif; vertical-align:top; background:rgb(255,255,204)"> Network</td> 
  </tr> 
  <tr> 
   <td style="font-size:8pt; font-family:Arial,Helvetica,Geneva,sans-serif; vertical-align:top"> DB CPU</td> 
   <td align="right" style="font-size:8pt; font-family:Arial,Helvetica,Geneva,sans-serif; vertical-align:top"> &nbsp;</td> 
   <td align="right" style="font-size:8pt; font-family:Arial,Helvetica,Geneva,sans-serif; vertical-align:top"> 14,958</td> 
   <td align="right" style="font-size:8pt; font-family:Arial,Helvetica,Geneva,sans-serif; vertical-align:top"> &nbsp;</td> 
   <td align="right" style="font-size:8pt; font-family:Arial,Helvetica,Geneva,sans-serif; vertical-align:top"> 20.54</td> 
   <td style="font-size:8pt; font-family:Arial,Helvetica,Geneva,sans-serif; vertical-align:top"> &nbsp;</td> 
  </tr> 
  <tr> 
   <td style="font-size:8pt; font-family:Arial,Helvetica,Geneva,sans-serif; vertical-align:top; background:rgb(255,255,204)"> SQL*Net more data to client</td> 
   <td align="right" style="font-size:8pt; font-family:Arial,Helvetica,Geneva,sans-serif; vertical-align:top; background:rgb(255,255,204)"> 4,428,973</td> 
   <td align="right" style="font-size:8pt; font-family:Arial,Helvetica,Geneva,sans-serif; vertical-align:top; background:rgb(255,255,204)"> 3,156</td> 
   <td align="right" style="font-size:8pt; font-family:Arial,Helvetica,Geneva,sans-serif; vertical-align:top; background:rgb(255,255,204)"> 1</td> 
   <td align="right" style="font-size:8pt; font-family:Arial,Helvetica,Geneva,sans-serif; vertical-align:top; background:rgb(255,255,204)"> 4.33</td> 
   <td style="font-size:8pt; font-family:Arial,Helvetica,Geneva,sans-serif; vertical-align:top; background:rgb(255,255,204)"> Network</td> 
  </tr> 
 </tbody> 
</table>

**  看看这些会话在做什么，卡在一个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不一定没问题

发表评论取消回复

还没有评论，来说两句吧...

相关阅读