系统临时表空间已经达到64G， 但是依然报ORA-1652错误，现在业务申请扩展临时表空间到128g。
经过抓取AWR报告，发现如下几条sql语句执行时间最长，经过分析 发现 大部分是 “MINUS ”和“insert into select......”类型语句。重点关注 MINUS。
| 7sx6fj2pvrp99 | INSERT INTO CAMS_ACCT_TEMP03 SELECT * FROM CAMS_ACCT_TEMP01 A WHERE NOT EXISTS(SELECT 1 FROM CAMS_ACCT_TEMP03 B WHERE A.ACCT_NO = B.ACCT_NO) MINUS SELECT * FROM CAMS_ACCT_TEMP02 A WHERE NOT EXISTS(SELECT 1 FROM CAMS_ACCT_TEMP03 B WHERE A.ACCT_NO = B.ACCT_NO)； |
INSERT INTO CAMS_CUST_TEMP03 SELECT * FROM CAMS_CUST_TEMP01 MINUS SELECT * FROM CAMS_CUST_TEMP02；
INSERT INTO CAMS_SUB_ACCT_TEMP (COMPANY, OPEN_BANK, ACCT_NO, SUB_ACCT_NO, INTERNAL_KEY) (SELECT COMPANY, OPEN_BANK, ACCT_NO, SUB_ACCT_NO, INTERNAL_KEY FROM INTF_CAMS_SUB_ACCT WHERE DATA_DATE = :B1 MINUS SELECT COMPANY, OPEN_BANK, ACCT_NO, SUB_ACCT_NO, INTERNAL_KEY FROM INTF_CAMS_SUB_ACCT WHERE DATA_DATE = :B2 )；
INSERT INTO CAMS_CARD_INFO_TEMP03 SELECT * FROM CAMS_CARD_INFO_TEMP01 MINUS SELECT * FROM CAMS_CARD_INFO_TEMP02；
| g90k5fb17b34h | INSERT INTO CAMS_CUST_TEMP01 (COMPANY , ORG_CODE , CUST_NO , CUST_NAME , CERTI_KIND , CERTI_NO , EFFECTIVE_DATE , ISSUE_ORGAN_REGION , IDENTITY_KIND , COUNTRY , SEX , POST_CODE , ADDRESS , TEL , DATA_STATE , AUDITING_STATE , ERROR_REASON , REPORT_DATE , REPORT_FILE , UPD_USER , UPD_TIME ) SELECT COMPANY , ORG_CODE , CUST_NO , CUST_NAME , CERTI_KIND , CERTI_NO , EFFECTIVE_DATE , ISSUE_ORGAN_REGION , IDENTITY_KIND , COUNTRY , SEX , POST_CODE , ADDRESS , TEL , DATA_STATE , AUDITING_STATE , ERROR_REASON , REPORT_DATE , REPORT_FILE , UPD_USER , UPD_TIME FROM INTF_CAMS_CUST A WHERE A.DATA_DATE = :B1； |
首先我们来了解下 ‘MINUS’函数，minus主要作用在于取两个 集合/表 补集，就类似对两个集合排序 然后类似冒泡算法对比去重。
抓了2个sql的执行计划，可以发现使用了大量的temp排序，一条sql执行消耗800+MB的temp 排序，可以通过 not in ,not exists 来改写，实际工作中，not in比not exists要好。
对于需要排序的列，可以适当建立索引，走索引扫描可以避免额外排序。
SQL1:
SQL_ID cp8cdxcj53rbh, child number 0
-------------------------------------
SELECT * FROM IGRSVISE.CAMS_ACCT_TEMP01 A WHERE NOT EXISTS( SELECT 1 FROM IGRSVISE.CAMS_ACCT_TEMP03 B WHERE A.ACCT_NO = B.ACCT_NO)
MINUS
SELECT * FROM IGRSVISE.CAMS_ACCT_TEMP02 A WHERE NOT EXISTS( SELECT 1 FROM IGRSVISE.CAMS_ACCT_TEMP03 B WHERE A.ACCT_NO = B.ACCT_NO)
SQL2:
INSERT INTO CAMS_CUST_TEMP03 SELECT * FROM IGRSVISE.CAMS_CUST_TEMP01 MINUS SELECT * FROM IGRSVISE.CAMS_CUST_TEMP02
改写成如下 not in
SQL3 :
3;"g90k5fb17b34h";INSERT INTO CAMS_CUST_TEMP01 (COMPANY ,ORG_CODE ,CUST_NO ,CUST_NAME,CERTI_KIND ,CERTI_NO ,EFFECTIVE_DATE ,ISSUE_ORGAN_REGION,IDENTITY_KIND ,COUNTRY ,SEX ,POST_CODE ,ADDRESS ,TEL ,DATA_STATE,AUDITING_STATE ,ERROR_REASON ,REPORT_DATE ,REPORT_FILE ,UPD_USER,UPD_TIME ) SELECT COMPANY ,ORG_CODE ,CUST_NO ,CUST_NAME ,CERTI_KIND,CERTI_NO ,EFFECTIVE_DATE ,ISSUE_ORGAN_REGION ,IDENTITY_KIND ,COUNTRY,SEX ,POST_CODE ,ADDRESS ,TEL ,DATA_STATE ,AUDITING_STATE ,ERROR_REASON,REPORT_DATE ,REPORT_FILE ,UPD_USER ,UPD_TIME FROM INTF_CAMS_CUST AWHERE A.DATA_DATE = :B1
SQL4：
"0d9ua5sqm01jk";INSERT INTO CAMS_CARD_INFO_TEMP01 (COMPANY, ORG_CODE, CARD_NO,IS_PRIMARY_CARD, MATURITY_DATE, ACCT_MEDIUM, CLOSED_DATE, CARD_STATUS,ERROR_REASON, REPORT_DATE, REPORT_FILE, UPD_USER, UPD_TIME,MAIN_CARD_NO ) SELECT COMPANY, ORG_CODE, CARD_NO, IS_PRIMARY_CARD,MATURITY_DATE, ACCT_MEDIUM, CLOSED_DATE, CARD_STATUS, ERROR_REASON,REPORT_DATE, REPORT_FILE, UPD_USER, UPD_TIME, MAIN_CARD_NO FROM INTF_CAMS_CARD_INFO A WHERE A.DATA_DATE = :B1
临时表还有一种优化方法，类似这种：
with cr as ( select code from product where Name like 'C%' )select * from product where code in (select * from cr)