粗心的运维DBA

来源：互联网发布：js原生添加dom元素编辑：程序博客网时间：2024/05/17 22:35

要下班的时候，网友发来QQ消息，说SQL跑得慢，要1-3秒出结果，希望优化一下：

SELECT   /*+INDEX(TMS,IDX1_TB_EVT_DLV_W)*/TMS.MAIL_NUM,TMS.DLV_BUREAU_ORG_CODE AS DLVORGCODE,RO.ORG_SNAME AS DLVORGNAME,TMS.DLV_PSEG_CODE AS DLVSECTIONCODE,TMS.DLV_PSEG_NAME AS DLVSECTIONNAME,TO_CHAR(TMS.DLV_DATE,'YYYY-MM-DD HH24:MI:SS') AS RECTIME,TMS.DLV_STAFF_CODE AS HANDOVERUSERCODE,TU2.REALNAME AS  HANDOVERUSERNAME,DECODE( TMS.DLV_STS_CODE ,'I','妥投','H','未妥投', TMS.DLV_STS_CODE) AS  DLV_STS_CODE,     CASE WHEN  TMS.MAIL_NUM LIKE 'EC%' THEN '代收'            WHEN TMS.MAIL_NUM LIKE 'ED%CW' THEN '代收'            WHEN  TMS.MAIL_NUM LIKE 'FJ%' THEN '代收'            WHEN  TMS.MAIL_NUM LIKE 'GC%' THEN '代收'               ELSE                           '非代收'                        END MAIL_NUM_TYPE FROM TB_EVT_DLV_W TMSLEFT JOIN RES_ORG RO ON TMS.DLV_BUREAU_ORG_CODE=RO.ORG_CODE    LEFT JOIN TB_USER TU2 ON TU2.DELVORGCODE=TMS.DLV_BUREAU_ORG_CODE AND TU2.USERNAME=TMS.DLV_STAFF_CODEWHERE NOT EXISTS (SELECT  /*+INDEX(TDW,IDX1_TB_MAIL_SECTION_STORE)*/ MAIL_NUM FROM TB_MAIL_SECTION_STORE TDW WHERE  TDW.MAIL_NUM = TMS.MAIL_NUM              AND TDW.DLVORGCODE = TMS.DLV_BUREAU_ORG_CODE              and  TDW.DLVORGCODE='35000133'                AND TDW.RECTIME >= TO_DATE('2012-11-01 00:00','YYYY-MM-DD HH24:MI:SS')                AND TO_DATE('2012-11-08 15:15','YYYY-MM-DD HH24:MI:SS') >= TDW.RECTIME  and rownum=1   )                AND                TMS.DLV_BUREAU_ORG_CODE = '35000133'                AND TMS.DLV_DATE >=     TO_DATE('2012-11-01 00:00','YYYY-MM-DD HH24:MI:SS')                AND TO_DATE('2012-11-08 15:15','YYYY-MM-DD HH24:MI:SS') >= TMS.DLV_DATE                AND ('' IS NULL OR TMS.DLV_STAFF_CODE ='')                AND ('' IS NULL OR TU2.REALNAME  LIKE '%%')                AND TMS.REC_AVAIL_FLAG = '1' Plan hash value: 1159587453 ----------------------------------------------------------------------------------------------------------------------------------------| Id  | Operation                                 | Name                       | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |----------------------------------------------------------------------------------------------------------------------------------------|   0 | SELECT STATEMENT                          |                            |       |       |   322K(100)|          |       |       ||*  1 |  FILTER                                   |                            |       |       |            |          |       |       ||   2 |   NESTED LOOPS OUTER                      |                            |   131 | 13493 |   928   (1)| 00:00:12 |       |       ||*  3 |    HASH JOIN RIGHT OUTER                  |                            |   129 | 10191 |   670   (1)| 00:00:09 |       |       ||*  4 |     TABLE ACCESS BY INDEX ROWID           | EMS_USER                   |     6 |   120 |     8   (0)| 00:00:01 |       |       ||*  5 |      INDEX RANGE SCAN                     | EMS_USER_NEW_INX_ORG       |     7 |       |     3   (0)| 00:00:01 |       |       ||*  6 |     TABLE ACCESS BY GLOBAL INDEX ROWID    | TB_EVT_DLV_W               |   129 |  7611 |   661   (0)| 00:00:08 | ROWID | ROWID ||*  7 |      INDEX RANGE SCAN                     | IDX1_TB_EVT_DLV_W          |   586 |       |    86   (0)| 00:00:02 |       |       ||*  8 |       COUNT STOPKEY                       |                            |       |       |            |          |       |       ||*  9 |        FILTER                             |                            |       |       |            |          |       |       ||  10 |         PARTITION RANGE ITERATOR          |                            |     1 |    31 |   246   (0)| 00:00:03 |   KEY |   KEY ||* 11 |          TABLE ACCESS BY LOCAL INDEX ROWID| TB_MAIL_SECTION_STORE      |     1 |    31 |   246   (0)| 00:00:03 |   KEY |   KEY ||* 12 |           INDEX RANGE SCAN                | IDX1_TB_MAIL_SECTION_STORE |     1 |       |   245   (0)| 00:00:03 |   KEY |   KEY ||  13 |    TABLE ACCESS BY INDEX ROWID            | RES_ORG                    |     1 |    24 |     2   (0)| 00:00:01 |       |       ||* 14 |     INDEX RANGE SCAN                      | IDX_RES_ORG                |     1 |       |     1   (0)| 00:00:01 |       |       |---------------------------------------------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id):---------------------------------------------------    1 - filter(TO_DATE('2012-11-01 00:00','YYYY-MM-DD HH24:MI:SS')<=TO_DATE('2012-11-08 15:15','YYYY-MM-DD HH24:MI:SS'))   3 - access("EU"."USERNAME"="TMS"."DLV_STAFF_CODE" AND "EU"."DELVORGCODE"="TMS"."DLV_BUREAU_ORG_CODE")   4 - filter("EU"."POSTMANKIND"<>5)   5 - access("EU"."DELVORGCODE"='35000133')   6 - filter(("TMS"."DLV_DATE">=TO_DATE('2012-11-01 00:00','YYYY-MM-DD HH24:MI:SS') AND "TMS"."REC_AVAIL_FLAG"='1' AND               "TMS"."DLV_DATE"<=TO_DATE('2012-11-08 15:15','YYYY-MM-DD HH24:MI:SS')))   7 - access("TMS"."DLV_BUREAU_ORG_CODE"='35000133')       filter( IS NULL)   8 - filter(ROWNUM=1)   9 - filter((TO_DATE('2012-11-01 00:00','YYYY-MM-DD HH24:MI:SS')<=TO_DATE('2012-11-08 15:15','YYYY-MM-DD HH24:MI:SS') AND               :B1='35000133'))  11 - filter(("TDW"."RECTIME">=TO_DATE('2012-11-01 00:00','YYYY-MM-DD HH24:MI:SS') AND "TDW"."RECTIME"<=TO_DATE('2012-11-08               15:15','YYYY-MM-DD HH24:MI:SS')))  12 - access("TDW"."DLVORGCODE"=:B1 AND "TDW"."MAIL_NUM"=:B2)  14 - access("TMS"."DLV_BUREAU_ORG_CODE"="RO"."ORG_CODE")

拿到这个执行计划之后，大概看了一下，花了1分钟就搞定问题(我帮别人优化SQL都不连接他们数据库，直接用眼睛看，或者问几句就OK了)

落落 17:55:35
你那个应该是分区表吧？ ---我指的是这个表 TB_EVT_DLV_W
落落 17:55:50
根据 DLV_DATE 分区的？？
落落 17:55:53
是不是?
网友XXX 17:56:01
SHI
网友XXX 17:56:03
是的

落落 17:56:17
create index idx on TB_EVT_DLV_W
(DLV_BUREAU_ORG_CODE) local;
落落 17:56:29
你那个索引不是local的

--------------------------------------------------------------

6点下班回家过后，晚上网友发来QQ消息

--------------------------------------------------------------

网友XXX 20:41:33
比原来快了不少
网友XXX 20:41:38
给力落落兄

这个SQL性能问题，是由于他们运维DBA 在创建索引的时候，搞忘了 local关键字，

从而导致 SQL 在有 where条件(分区键包含在where条件中) 能进行分区裁剪的时候，不能有效的踢出大量数据。

最终导致ID=7 这里这个分区扫描大量的leaf block 并且到ID=6 这里还去回表，又进行大量的过滤操作。

各位读者，你们能1分钟内解决这个SQL性能问题吗？如果不能，来参加我的SQL优化培训吧吐舌头

联系方式： QQ 692162374