一个insert插入语句很慢的优化_fengxinzisue

http://blog.sina.com.cn/u/1029388674

首页博文目录关于我

个人资料

微博

加好友发纸条

写留言加关注

博客等级：
博客积分：

博客访问：
关注人气：
获赠金笔：0支
赠出金笔：0支
荣誉徽章：

正文字体大小：大中小

一个insert插入语句很慢的优化

(2018-06-01 16:18:02)

分类：数据库

原sql语句：

INSERT 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, SYSDATE FROM TEMP_FMLG_PURGE

原sql执行计划非常简单：

http://blog.itpub.net/attachment/201408/1/26736162_1406903827B0Ii.png

该语句是job中的语句，每天都需要跑的，其历史执行时间如下图，可以看出执行时间非常长的都是user_io_wait等待比较严重的一些：

http://blog.itpub.net/attachment/201408/1/26736162_1406903829ck5U.png

查看一下相关表的属性和数据量：

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_NAME IN ('BASE_FMLG',

'TEMP_FMLG_PURGE');

http://blog.itpub.net/attachment/201408/1/26736162_14069038328VEe.png

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_NAME IN ('BASE_FMLG',

'TEMP_FMLG_PURGE');

http://blog.itpub.net/attachment/201408/1/26736162_1406903837N13t.png

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

select v.index_owner,

v.index_name,

v.index_type,

v.索引列,

v.partition_size,

v.num_rows

from vw_table_index_part2_lhr V

where V.TABLE_NAME='BASE_FMLG'

and v.PARTITION_NAME='P201407' ;

http://blog.itpub.net/attachment/201408/1/26736162_1406903846MH7M.png

查一下数据来源：

SELECT t.BATCH_DATE,

COUNT(1)

FROM TEMP_FMLG_PURGE t

GROUP BY t.BATCH_DATE;

http://blog.itpub.net/attachment/201408/1/26736162_1406903846ArJS.png

看来，都是当天的数据，所以只涉及到分区表的单个分区

select v.PARTITION_NAME,

v.TABLE_NAME,

v.LAST_ANALYZED,

v.NUM_ROWS,

v.partition_size ,

v.EMPTY_BLOCKS,v.LOGGING

from VW_TABLE_PART_LHR V

where V.TABLE_NAME='BASE_FMLG';

http://blog.itpub.net/attachment/201408/1/26736162_1406903852RKGS.png

系统预估剩余时间：select * from VW_LONGRUN_LHR a where a.SQL_ID='2pnas8zbxtk3a';

http://blog.itpub.net/attachment/201408/1/26736162_14069038559924.png

插入200W的数据到一个单个分区16G的分区表中需要花费将近12个小时，似乎慢了点。。。。。

问题解决：

查询会话的统计信息，发现redo的产生量非常的大，所以解决办法：

第一步：将表修改为nologging属性

第二步：将索引修改为nologging属性

第三步：插入的时候采用append方式来插入

第四步：如果还是慢点的话，可以采用并行插入，增大排序缓冲区

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

注意：以上解决办法必须是该表的数据不重要，不然修改为nologging属性后万一数据丢失可能就找不回来了，索引一般都为nologging模式，索引记录redo没有作用采用append插入的前提是该表上边没有大量的delete动作

最后优化后的代码：

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

alter table RISKREPT.BASE_FMLG NOLOGGING;

alter index DX_RKO_FMLG_BATCH_DATE NOLOGGING;

alter index IDX_RKO_FMLG_ACCT NOLOGGING;

alter index IDX_RKO_FMLG_CARD NOLOGGING;

alter index IDX_RKO_FMLG_DT NOLOGGING;

alter index IDX_RKO_FMLG_MER NOLOGGING;

----- 修改会话的属性，开启并行插入：

alter session set workarea_size_policy=manual;

alter session set sort_area_size=1000000000;

alter session ENABLE parallel dml;

EXPLAIN PLAN for

INSERT 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, SYSDATE FROM TEMP_FMLG_PURGE_2 ;