关注业务-把优化做到极致

24号信仰

已于 2023-07-25 17:04:00 修改

阅读量1.2k

点赞数 2

分类专栏：【SQL TUNING】 24号信仰の优化专栏文章标签：优化 SQL优化

于 2019-04-03 17:52:54 首次发布

本文链接：https://blog.csdn.net/Skybig1988/article/details/88997324

版权

本文记录了一次SQL优化过程，从解决ORA-12805错误开始，通过调整索引并行度，再到利用分区裁剪优化，最后通过NEST LOOP和HINT实现0.2秒的执行时间，揭示了SQL优化的思路和方法。

摘要由CSDN通过智能技术生成

SELECT .. .. ..
  FROM AWP.TTT_TTT_TRANSACTION_2018 INCT
  LEFT JOIN AWP.TTT_TTT_TELM TELM
    ON INCT.TELLER_NO = TELM.TELLER_NO
  LEFT JOIN SJB_CD_01.TMP_TT_BAN_EDP ED1P
    ON INCT.TRAN_CODE = ED1P.交易代码
  LEFT JOIN AWP.TTT_TTT_CB_CIF CUSM
    ON INCT.CUSTOMER_NO = CUSM.CUSTOMER_NO_MICM
  LEFT JOIN AWP.TTT_TTT_CB_ACCT INVM
    ON INCT.ACCT_NO = INVM.ACCT_NO
  LEFT JOIN AWP.TTT_TTT_BRHM BRHM
    ON INVM.BRANCH_NO = BRHM.BRANCH_NO
  LEFT JOIN AWP.TTT_TTT_DEPP DEPP
    ON INVM.ACCT_TYPE = DEPP.ACCT_TYPE || DEPP.INT_CAT
  JOIN SJB_CD_01.TMP_TT_LZ_POS_XYK_ALL XYK
    ON XYK.持卡人证件号码 = CUSM.ID_NO_MICM
 WHERE 1 = 1
   AND TO_DATE(INCT.TRAN_DATE, 'yyyy-mm-dd') BETWEEN
       TO_DATE(XYK.交易日期, 'yyyy-mm-dd') AND
       TO_DATE(XYK.交易日期, 'yyyy-mm-dd') + 5
   AND INCT.INCT_01_AMOUNT BETWEEN XYK.刷卡金额 * 0.9 AND XYK.刷卡金额

SQL执行6分钟以后报错：ORA-12805:并行查询服务器意外停止

1.查看执行计划，确认SQL是否进行的并行操作

PLAN HASH VALUE: 1375319919                                                                                                                                                                      
                                                                                                                                                                                                 
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------                
| ID  | OPERATION                                         | NAME                           | ROWS  | BYTES | COST (%CPU)| TIME     | PSTART| PSTOP |    TQ  |IN-OUT| PQ DISTRIB |                
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------                
|   0 | SELECT STATEMENT                                  |                                |     1 |  1480 | 14871   (1)| 00:02:59 |       |       |        |      |            |                
|   1 |  PX COORDINATOR                                   |                                |       |       |            |          |       |       |        |      |            |                
|   2 |   PX SEND QC (RANDOM)                             | :TQ10009                       |     1 |  1480 | 14871   (1)| 00:02:59 |       |       |  Q1,09 | P->S | QC (RAND)  |                
|*  3 |    HASH JOIN OUTER                                |                                |     1 |  1480 | 14871   (1)| 00:02:59 |       |       |  Q1,09 | PCWP |            |                
|   4 |     PX RECEIVE                                    |                                |     1 |  1231 | 14858   (1)| 00:02:59 |       |       |  Q1,09 | PCWP |            |                
|   5 |      PX SEND HASH                                 | :TQ10008                       |     1 |  1231 | 14858   (1)| 00:02:59 |       |       |  Q1,08 | P->P | HASH       |                
|*  6 |       HASH JOIN OUTER                             |                                |     1 |  1231 | 14858   (1)| 00:02:59 |       |       |  Q1,08 | PCWP |            |                
|   7 |        PX RECEIVE                                 |                                |     1 |  1103 | 14845   (1)| 00:02:59 |       |       |  Q1,08 | PCWP |            |                
|   8 |         PX SEND HASH                              | :TQ10007                       |     1 |  1103 | 14845   (1)| 00:02:59 |       |       |  Q1,07 | P->P | HASH       |                
|   9 |          NESTED LOOPS OUTER                       |                                |     1 |  1103 | 14845   (1)| 00:02:59 |       |       |  Q1,07 | PCWP |            |                
|* 10 |           HASH JOIN OUTER                         |                                |     1 |   976 | 14841   (1)| 00:02:59 |       |       |  Q1,07 | PCWP |            |                
|  11 |            PX RECEIVE                             |                                |     1 |   854 | 14532   (1)| 00:02:55 |       |       |  Q1,07 | PCWP |            |                
|  12 |             PX SEND HASH                          | :TQ10006                       |     1 |   854 | 14532   (1)| 00:02:55 |       |       |  Q1,06 | P->P | HASH       |                
|* 13 |              HASH JOIN OUTER                      |                                |     1 |   854 | 14532   (1)| 00:02:55 |       |       |  Q1,06 | PCWP |            |                
|  14 |               PX RECEIVE                          |                                |     1 |   803 | 14517   (1)| 00:02:55 |       |       |  Q1,06 | PCWP |