一個insert插入語句很慢的優化

2019-11-08 20:45:49

字體：大中小

來源：轉載

供稿：網友

一個insert插入語句很慢的優化

原sql語句：

INSERTINTO RISKREPT.BASE_FMLG(BATCH_DATE,DATE_STAMP_ST,TIME_STAMP_ST,ORG,ACCT,CARD_NBR,CARD_SEQ,MER_ORG,MER_NBR,REQUEST_TYPE_ID,LOGO,SYSTEM_ACTION,FINAL_ACTION,ACTION_REASON,REVERSAL_REASON,AVAIL_CR,CASH_AVAIL_CR,ACCT_CURR_BAL,ACCT_CURR_BAL_CASH,B007_GMT_DATE_TIME,B018_MER_TYPE,B019_CNTRY_CODE,B032_ACQ_ID,B033_FWD_ID,AUTH_CODE,B039_RESP_CODE,B041_CRD_ACCPT_TERM,B042_TERMTYPE2_MER_ID,B043_CRD_ACCPT_NAM,B043_CRD_ACCPT_CITY,B043_CRD_ACCPT_ST_CTRY,MESSAGE_TYPE_ID,RECORD_TYPE,SALES_AMT_RMB,TOTAL_SALES_AMT,B049_CURR_CODE,BLING_AMT,POS_ENTRY_MODE,PIN_ENTRY_MODE,TRADE_INTERNET,SALES_CTRY,SALES_CTRY_NAME,SALES_CITY,SALES_CITY_NAME,SALES_LINK,MER_CODE,MER_MCC,MER_NAM,ACQ_NAME,REVCODE,AUTH_TYPE,REF_NBR,VI_B011_SYS_AUDT_TRCE,CARD_TYPE,STAGE_TYPE,STAGE_NUM,OVERSEA_FLAG,RISK_FCD,RISK_LCD)SELECT BATCH_DATE,DATE_STAMP_ST,TIME_STAMP_ST,ORG,ACCT,CARD_NBR,CARD_SEQ,MER_ORG,MER_NBR,REQUEST_TYPE_ID,LOGO,SYSTEM_ACTION,FINAL_ACTION,ACTION_REASON,REVERSAL_REASON,AVAIL_CR,CASH_AVAIL_CR,ACCT_CURR_BAL,ACCT_CURR_BAL_CASH,B007_GMT_DATE_TIME,B018_MER_TYPE,B019_CNTRY_CODE,B032_ACQ_ID,B033_FWD_ID,AUTH_CODE,B039_RESP_CODE,B041_CRD_ACCPT_TERM,B042_TERMTYPE2_MER_ID,B043_CRD_ACCPT_NAM,B043_CRD_ACCPT_CITY,B043_CRD_ACCPT_ST_CTRY,MESSAGE_TYPE_ID,RECORD_TYPE,SALES_AMT_RMB,TOTAL_SALES_AMT,B049_CURR_CODE,BLING_AMT,POS_ENTRY_MODE,PIN_ENTRY_MODE,TRADE_INTERNET,SALES_CTRY,SALES_CTRY_NAME,SALES_CITY,SALES_CITY_NAME,SALES_LINK,MER_CODE,MER_MCC,MER_NAM,ACQ_NAME,REVCODE,AUTH_TYPE,REF_NBR,VI_B011_SYS_AUDT_TRCE,CARD_TYPE,STAGE_TYPE,STAGE_NUM,OVERSEA_FLAG,SYSDATE,SYSDATEFROM TEMP_FMLG_PURGE

原sql執行計劃非常簡單：

該語句是job中的語句，每天都需要跑的，其歷史執行時間如下圖，可以看出執行時間非常長的都是user_io_wait等待比較嚴重的一些：

查看一下相關表的屬性和數據量：

SELECT v.OWNER,v.TABLE_NAME,v.partitioned,v.LAST_ANALYZED,v.NUM_ROWS,v.table_size2, v.EMPTY_BLOCKS FROM vw_table_lhr v

WHERE v.TABLE_NAMEIN('BASE_FMLG',

'TEMP_FMLG_PURGE');

BASE_FMLG有15億的數據量，是個分區表，每次從TEMP_FMLG_PURGE中取數，TEMP_FMLG_PURGE大約有234W的數據量，

索引信息：

SELECT v.index_owner, v.index_name, v.index_type, v.partitioned, v.索引列, v.index_size, v.num_rows

FROM vw_table_index_lhr v

WHERE v.TABLE_NAMEIN('BASE_FMLG',

'TEMP_FMLG_PURGE');

被插入的表有5個索引，且都是分區索引，不涉及全局索引，涉及到的分區索引：

select v.index_owner, v.index_name, v.index_type, v.索引列,

v.partition_size, v.num_rows

fromvw_table_index_part2_lhr V

where V.TABLE_NAME='BASE_FMLG'andv.PARTITION_NAME='P201407';

查一下數據來源：

SELECT t.BATCH_DATE,

COUNT(1)

FROMTEMP_FMLG_PURGE t

GROUPBY t.BATCH_DATE;

看來，都是當天的數據，所以只涉及到分區表的單個分區

selectv.PARTITION_NAME,

v.TABLE_NAME,

v.LAST_ANALYZED,

v.NUM_ROWS,

v.partition_size ,

v.EMPTY_BLOCKS,v.LOGGING

fromVW_TABLE_PART_LHR V

where V.TABLE_NAME='BASE_FMLG';

系統預估剩余時間：select* from VW_LONGRUN_LHR awhere a.SQL_ID='2pnas8zbxtk3a';

插入200W的數據到一個單個分區16G的分區表中需要花費將近12個小時，似乎慢了點。。。。。

問題解決：

查詢會話的統計信息，發現redo的產生量非常的大，所以解決辦法：

第一步：將表修改為nologging屬性

第二步：將索引修改為nologging屬性

第三步：插入的時候采用append方式來插入

第四步：如果還是慢點的話，可以采用并行插入，增大排序緩沖區

第五步：如果有可能可以先把索引置于無效狀態，然后插入完成之后再重建索引

注意：以上解決辦法①必須是該表的數據不重要，不然修改為nologging屬性后萬一數據丟失可能就找不回來了，② 索引一般都為nologging模式，索引記錄redo沒有作用 ③ 采用append插入的前提是該表上邊沒有大量的delete動作

最后優化后的代碼：

先將表及其索引置于NOLOGGING模式：

altertable RISKREPT.BASE_FMLGNOLOGGING;

alterindex DX_RKO_FMLG_BATCH_DATENOLOGGING;

alterindex IDX_RKO_FMLG_ACCTNOLOGGING;

alterindex IDX_RKO_FMLG_CARDNOLOGGING;

alterindex IDX_RKO_FMLG_DTNOLOGGING;

alterindex IDX_RKO_FMLG_MERNOLOGGING;

----- 修改會話的屬性，開啟并行插入：

altersessionset workarea_size_policy=manual;

altersessionset sort_area_size=1000000000;

altersessionENABLEparalleldml;

EXPLAINPLANfor

INSERT/*+parallel(BASE_FMLG,4) */INTO RISKREPT.BASE_FMLG(BATCH_DATE,DATE_STAMP_ST,TIME_STAMP_ST,ORG,ACCT,CARD_NBR,CARD_SEQ,MER_ORG,MER_NBR,REQUEST_TYPE_ID,LOGO,SYSTEM_ACTION,FINAL_ACTION,ACTION_REASON,REVERSAL_REASON,AVAIL_CR,CASH_AVAIL_CR,ACCT_CURR_BAL,ACCT_CURR_BAL_CASH,B007_GMT_DATE_TIME,B018_MER_TYPE,B019_CNTRY_CODE,B032_ACQ_ID,B033_FWD_ID,AUTH_CODE,B039_RESP_CODE,B041_CRD_ACCPT_TERM,B042_TERMTYPE2_MER_ID,B043_CRD_ACCPT_NAM,B043_CRD_ACCPT_CITY,B043_CRD_ACCPT_ST_CTRY,MESSAGE_TYPE_ID,RECORD_TYPE,SALES_AMT_RMB,TOTAL_SALES_AMT,B049_CURR_CODE,BLING_AMT,POS_ENTRY_MODE,PIN_ENTRY_MODE,TRADE_INTERNET,SALES_CTRY,SALES_CTRY_NAME,SALES_CITY,SALES_CITY_NAME,SALES_LINK,MER_CODE,MER_MCC,MER_NAM,ACQ_NAME,REVCODE,AUTH_TYPE,REF_NBR,VI_B011_SYS_AUDT_TRCE,CARD_TYPE,STAGE_TYPE,STAGE_NUM,OVERSEA_FLAG,RISK_FCD,RISK_LCD)SELECT BATCH_DATE,DATE_STAMP_ST,TIME_STAMP_ST,ORG,ACCT,CARD_NBR,CARD_SEQ,MER_ORG,MER_NBR,REQUEST_TYPE_ID,LOGO,SYSTEM_ACTION,FINAL_ACTION,ACTION_REASON,REVERSAL_REASON,AVAIL_CR,CASH_AVAIL_CR,ACCT_CURR_BAL,ACCT_CURR_BAL_CASH,B007_GMT_DATE_TIME,B018_MER_TYPE,B019_CNTRY_CODE,B032_ACQ_ID,B033_FWD_ID,AUTH_CODE,B039_RESP_CODE,B041_CRD_ACCPT_TERM,B042_TERMTYPE2_MER_ID,B043_CRD_ACCPT_NAM,B043_CRD_ACCPT_CITY,B043_CRD_ACCPT_ST_CTRY,MESSAGE_TYPE_ID,RECORD_TYPE,SALES_AMT_RMB,TOTAL_SALES_AMT,B049_CURR_CODE,BLING_AMT,POS_ENTRY_MODE,PIN_ENTRY_MODE,TRADE_INTERNET,SALES_CTRY,SALES_CTRY_NAME,SALES_CITY,SALES_CITY_NAME,SALES_LINK,MER_CODE,MER_MCC,MER_NAM,ACQ_NAME,REVCODE,AUTH_TYPE,REF_NBR,VI_B011_SYS_AUDT_TRCE,CARD_TYPE,STAGE_TYPE,STAGE_NUM,OVERSEA_FLAG,SYSDATE,SYSDATEFROM TEMP_FMLG_PURGE_2 ;