巨大的挑战-oracle 语句优化 数据量在300万左右 现执行要1小时-汗

Question

CREATE OR REPLACE procedure NADORA1.prcMake_New_020
as
begin
insert into KZICHUTI_JDUSHZI1
(
        DBK_REC_IDD,
        DBK_DAT_SSI_HZK,
        DBK_DAT_SSI_JKK,
        DBK_DAT_UPD_HZK,
        DBK_DAT_UPD_JKK,
        DBK_FIL_001,
        ZMK_COD,
        KNZ_COD,
KBN_COD,
        BNR_BNG,
        KNA_MOJ,
        ITR_BNG,
        CKD,
        FKA_NND,
        HAS_NND,
        HKZ_JDS_MHN,
        KII_COD,
        KAZ_CTI_SQS,
        KAZ_COD,
        KAZ_HRY_GEK_COD,
        KAZ_COD_HNK_YYY,
        KAZ_COD_HNK_MMM,
        KAZ_COD_HNK_DDD,
        KAZ_COD_KER_YYY,
        KAZ_COD_KER_MMM,
        KAZ_COD_KER_DDD,
        KAZ_COD_SNI_YYY,
        KAZ_COD_SNI_MMM,
        KAZ_COD_SNI_DDD,
        ZIR_COD,
        CTI_YYY,
        CTI_MMM,
        CAL_GAK,
        GEK,
        ZGK,
        TRI_YYY,
        TRI_MMM,
        TRI_DDD,
        CTI_ZGN_GAK,
        TAI_YYY,
        TAI_MMM,
        TAI_JUN,
        CTI_NND,
        SYD_TRK_YYY,
        SYD_TRK_MMM,
        NER_COD,
        TEP_FLG,
        RDT_KAZ_FLG,
        GRN_FGU,
        GRN_PER,
        NNP_KJN_005,
        YBI
)
select 
        a.DBK_REC_IDD DBK_REC_IDD,
        a.DBK_DAT_SSI_HZK DBK_DAT_SSI_HZK,
        a.DBK_DAT_SSI_JKK DBK_DAT_SSI_JKK,
        a.DBK_DAT_UPD_HZK DBK_DAT_UPD_HZK,
        a.DBK_DAT_UPD_JKK DBK_DAT_UPD_JKK,
        a.DBK_FIL_001 DBK_FIL_001,        
(case when NVL(b.NEW_ZMK_COD,' ') = ' ' then a.ZMK_COD else b.NEW_ZMK_COD end) ZMK_COD,
        (case when NVL(substr(b.NEW_KNZ_COD,2,1),' ') = ' ' then a.KNZ_COD else substr(b.NEW_KNZ_COD,2,1) end) KNZ_COD,
        substr(b.NEW_KZI_BNG,1,1) KBN_COD,
        (case when NVL(substr(b.NEW_KZI_BNG,2,3),' ') = ' ' then a.BNR_BNG else cast(substr(b.NEW_KZI_BNG,2,3) as char(3)) end) BNR_BNG,
        (case when NVL(substr(b.NEW_KZI_BNG,5,1),' ') = ' ' then a.KNA_MOJ else cast(substr(b.NEW_KZI_BNG,5,1) as nvarchar2(1)) end) KNA_MOJ,
        (case when NVL(substr(b.NEW_KZI_BNG,6,4),' ') = ' ' then a.ITR_BNG else cast(substr(b.NEW_KZI_BNG,6,4) as char(4)) end) ITR_BNG,
        (case when NVL(substr(b.NEW_KZI_BNG,10,1),' ') = ' ' then a.CKD else cast(substr(b.NEW_KZI_BNG,10,1) as char(1)) end) CKD,
        (case when NVL(substr(b.NEW_KBT,1,4),' ') = ' ' then a.FKA_NND else cast(substr(b.NEW_KBT,1,4) as number) end) FKA_NND,
        (case when NVL(substr(b.NEW_KBT,5,4),' ') = ' ' then a.HAS_NND else cast(substr(b.NEW_KBT,5,4) as number) end) HAS_NND,
        (case when NVL(substr(b.NEW_KBT,9,1),' ') = ' ' then a.HKZ_JDS_MHN else substr(b.NEW_KBT,9,1) end) HKZ_JDS_MHN,
        (case when nvl(b.OLD_CTG,'') = '' then a.KII_COD else OLD_CTG end) KII_COD,
        a.KAZ_CTI_SQS KAZ_CTI_SQS,
        a.KAZ_COD KAZ_COD,
        a.KAZ_HRY_GEK_COD KAZ_HRY_GEK_COD,
        a.KAZ_COD_HNK_YYY KAZ_COD_HNK_YYY,
        a.KAZ_COD_HNK_MMM KAZ_COD_HNK_MMM,
        a.KAZ_COD_HNK_DDD KAZ_COD_HNK_DDD,
        a.KAZ_COD_KER_YYY KAZ_COD_KER_YYY,
        a.KAZ_COD_KER_MMM KAZ_COD_KER_MMM,
        a.KAZ_COD_KER_DDD KAZ_COD_KER_DDD,
        a.KAZ_COD_SNI_YYY KAZ_COD_SNI_YYY,
        a.KAZ_COD_SNI_MMM KAZ_COD_SNI_MMM,
        a.KAZ_COD_SNI_DDD KAZ_COD_SNI_DDD,
        a.ZIR_COD ZIR_COD,
        a.CTI_YYY CTI_YYY,
        a.CTI_MMM CTI_MMM,
        a.CAL_GAK CAL_GAK,
        a.GEK GEK,
        a.ZGK ZGK,
        a.TRI_YYY TRI_YYY,
        a.TRI_MMM TRI_MMM,
        a.TRI_DDD TRI_DDD,
        a.CTI_ZGN_GAK CTI_ZGN_GAK,
        a.TAI_YYY TAI_YYY,
        a.TAI_MMM TAI_MMM,
        a.TAI_JUN TAI_JUN,
        a.CTI_NND CTI_NND,
        a.SYD_TRK_YYY SYD_TRK_YYY,
        a.SYD_TRK_MMM SYD_TRK_MMM,
        a.NER_COD NER_COD,
        a.TEP_FLG TEP_FLG,
        a.RDT_KAZ_FLG RDT_KAZ_FLG,
        a.GRN_FGU GRN_FGU,
        a.GRN_PER GRN_PER,
        a.NNP_KJN_005 NNP_KJN_005,
        a.YBI YBI
from
KZICHUTI_JDUSHZI aKZICHUTI_JDUSHZI1
left join M_KEY b on
    a.ZMK_COD = b.OLD_ZMK_COD and
    a.KNZ_COD = substr(b.OLD_KNZ_COD,1,1) and
    a.BNR_BNG = substr(b.OLD_KZI_BNG,1,3) and
    a.KNA_MOJ = substr(b.OLD_KZI_BNG,4,1) and
    a.ITR_BNG = substr(b.OLD_KZI_BNG,5,4) and
    a.CKD = substr(b.OLD_KZI_BNG,9,1) and
    a.FKA_NND = substr(b.OLD_KBT,1,4) and
    a.HAS_NND = substr(b.OLD_KBT,5,4) and
    a.HKZ_JDS_MHN = substr(b.OLD_KBT,9,1);
exception
when others then
dbms_output.put_line('other execption raised');
end;

Answer 1

 when NVL(substr(b.NEW_KZI_BNG,2,3),' ') = ' ' 
换成 
NVL(substr(b.NEW_KZI_BNG,2,3),' ')
when ='' then else..
case when 有两个用法，你搜索下那一种用法，那过相对来说效率更高些，或者你可以试试用decode()

Answer 2

不好意思，很少做oracle存储过程，对oracle不熟悉，怎么看执行计划？

Answer 3

拜托各位告诉诊断一下，性能最主要是卡在那里了？
case 语句？
left join？
条件中的运算 substr(b.OLD_KZI_BNG,9,1) ？

Answer 4

还有一点
insert into KZICHUTI_JDUSHZI1 
改成
insert /*+ append */ into KZICHUTI_JDUSHZI1 

Answer 5

自己做过的一个优化
这个insert语句是不是卡在回滚段处
如果是的话，建议用select 语句改写insert语句，不让数据进入回滚段

Answer 6

用如果数据源表很大的话建议用并行操作
execute immediate 'alter session enable palallel DML'
或者先把用到字段放在一个小的临时表里，然后在往事实表里插入数据

Answer 7

from 
KZICHUTI_JDUSHZI  M_KEY b on 
    a.ZMK_COD = b.OLD_ZMK_COD and 
    a.KNZ_COD = substr(b.OLD_KNZ_COD,1,1) and 
    a.BNR_BNG = substr(b.OLD_KZI_BNG,1,3) and 
    a.KNA_MOJ = substr(b.OLD_KZI_BNG,4,1) and 
    a.ITR_BNG = substr(b.OLD_KZI_BNG,5,4) and 
    a.CKD = substr(b.OLD_KZI_BNG,9,1) and 
    a.FKA_NND = substr(b.OLD_KBT,1,4) and 
    a.HAS_NND = substr(b.OLD_KBT,5,4) and 
    a.HKZ_JDS_MHN = substr(b.OLD_KBT,9,1); 这两个表的大小什么情况呢？索引情况怎么样的？表的统计信息是正确？方便把执行计划帖出来吗？只需要把select部分帖出来看看．

Answer 8

on 
    a.ZMK_COD = b.OLD_ZMK_COD and 
    a.KNZ_COD = substr(b.OLD_KNZ_COD,1,1) and 
    a.BNR_BNG = substr(b.OLD_KZI_BNG,1,3) and 
    a.KNA_MOJ = substr(b.OLD_KZI_BNG,4,1) and 
    a.ITR_BNG = substr(b.OLD_KZI_BNG,5,4) and 
    a.CKD = substr(b.OLD_KZI_BNG,9,1) and 
    a.FKA_NND = substr(b.OLD_KBT,1,4) and 
    a.HAS_NND = substr(b.OLD_KBT,5,4) and 
    a.HKZ_JDS_MHN = substr(b.OLD_KBT,9,1); 把能够排除掉越多的越放在下面，SQL语句中限定条件的执行顺序是自下而上的，还有如2楼的，可以把WHEN换成DECODE试试

Answer 9

支持case when 换成decode

Answer 10

请楼主参考：Oracle Sql 语句性能调优

Answer 11

从楼主说的数据量在300万，那么KZICHUTI_JDUSHZI 表是大表
从你的sql看，做了全表访问！这个没有办法
从你左连接M_KEY 表来看，不妨创建substr(xx)函数索引，
例如a.KNZ_COD = substr(b.OLD_KNZ_COD,1,1) 连接中创建M_KEY的substr(b.OLD_KNZ_COD,1,1)函数索引
这样可以走索引，访问数据可能会快些
但因为你的KZICHUTI_JDUSHZI表走了全表访问，故不会快很多！最好能把你的那断sql的执行计划贴出来，这样有针对性的帮你优化

Answer 12

应该要在某些字段加上个索引吧.这样就快了些.如果你已经加了索引,就尝试优化一下sql语句吧

Answer 13

表是否走了索引？你的sql看起来像统计一个表报，怕浪费时间的话 写成过程放在后台执行 每天都要统计的话就配置上crontab

Answer 14

换成decode也没有什么用，但是执行一小时还是太长了。逻辑结构应该还是有问题，最好给组织个具体数据，表名字好长，不知道是不是我看错。不好下手

Answer 15

1、where语句中，过滤越多的语句，放在越后面
2、case when 用decode代替
3、select中的2个表建立合适的索引

Answer 16

如果select出来的数据很多的话，在insert语句加上no loggig关键字，不写日志效率会提高很多。

Answer 17

数据量在300万左右，inseert, 没有 where 过滤。楼主要做什么？数据迁移？可以试试 direct load insert 语法来加快速度：INSERT /*+ APPEND */ INTO tab_a 
   SELECT xx,xxx,... FROM tab_b; 
COMMIT; 还可以临时修改表的 logging mode：
ALTER TABLE tab_a NOLOGGING;direct load insert 的其他参数用法，楼主可以 google 一下。

Answer 18

1.insert /*+ append */ into KZICHUTI_JDUSHZI1 
2.优化连接操作，可以调整物理设计，比方说分区啊，通过分区内连接来实现即可不过你现在300万数据量需要一个小时也太夸张了，检查以下你的数据分布模式特征吧