-- 客户生产环境反应一个job跑不出结果，我在应用程序log中找到了大量类似的语句select count(*) from (select LOAN_ID, CUSTOMER_REF, PROD_TYPE, SITE_CODE, COMPANY_CODE, LOANASSET_TYPE, LOANASSET_ID, LOANASSET_STATUS, LOAN_REF, LOAN_PROD_CODE, LOAN_CCY, LOAN_AMT, LOAN_OUTS_AMT, LOAN_EFFECTIVEDATE, LOAN_MATURITYDATE, LOAN_STATUS, OBLIGOR_CODE, CUSTOMER_CODE, UPDATE_TIMEfrom ((select l.LOAN_ID, o.ASSET_NAME as CUSTOMER_REF, o.PROD_TYPE as PROD_TYPE, l.SITE_CODE, l.COMPANY_CODE, l.LOANASSET_TYPE, l.LOANASSET_ID, l.LOANASSET_STATUS, l.LOAN_REF, l.LOAN_PROD_CODE, l.LOAN_CCY, l.LOAN_AMT, l.LOAN_OUTS_AMT, l.LOAN_EFFECTIVEDATE, l.LOAN_MATURITYDATE, l.LOAN_STATUS, l.OBLIGOR_CODE, l.CUSTOMER_CODE, l.UPDATE_TIME from TPOI_LOAN l, TPOI_PO_ASSET o where l.Loanasset_Type = 'O' and L.Loanasset_Id = o.po_asset_id) union all (select l.LOAN_ID, a.ASSET_NAME as CUSTOMER_REF, a.PROD_TYPE as PROD_TYPE, l.SITE_CODE, l.COMPANY_CODE, l.LOANASSET_TYPE, l.LOANASSET_ID, l.LOANASSET_STATUS, l.LOAN_REF, l.LOAN_PROD_CODE, l.LOAN_CCY, l.LOAN_AMT, l.LOAN_OUTS_AMT, l.LOAN_EFFECTIVEDATE, l.LOAN_MATURITYDATE, l.LOAN_STATUS, l.OBLIGOR_CODE, l.CUSTOMER_CODE, l.UPDATE_TIME from TPOI_LOAN l, TPOI_ASSET a where l.Loanasset_Type = 'A' and L.Loanasset_Id = a.ASSET_ID) union all (select l.LOAN_ID, p.POOL_NAME as CUSTOMER_REF, '' as PROD_TYPE, l.SITE_CODE, l.COMPANY_CODE, l.LOANASSET_TYPE, l.LOANASSET_ID, l.LOANASSET_STATUS, l.LOAN_REF, l.LOAN_PROD_CODE, l.LOAN_CCY, l.LOAN_AMT, l.LOAN_OUTS_AMT, l.LOAN_EFFECTIVEDATE, l.LOAN_MATURITYDATE, l.LOAN_STATUS, l.OBLIGOR_CODE, l.CUSTOMER_CODE, l.UPDATE_TIME from TPOI_LOAN l, TPOI_POOL p where l.Loanasset_Type = 'P' and L.Loanasset_Id = p.POOL_ID)) twhere (SITE_CODE = 'BANK' or COMPANY_CODE = 'SELLERWANG' ) XT10Plan hash value: 4089048245----------------------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |----------------------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | | 23 (0)| 00:00:01 || 1 | SORT AGGREGATE | | 1 | | | || 2 | VIEW | | 642 | | 23 (0)| 00:00:01 || 3 | UNION-ALL | | | | | || 4 | NESTED LOOPS | | 335 | 9045 | 11 (0)| 00:00:01 ||* 5 | TABLE ACCESS FULL | TPOI_LOAN | 335 | 7705 | 11 (0)| 00:00:01 ||* 6 | INDEX UNIQUE SCAN | TPOI_PO_ASSET_PK | 1 | 4 | 0 (0)| 00:00:01 || 7 | NESTED LOOPS | | 306 | 8262 | 11 (0)| 00:00:01 ||* 8 | TABLE ACCESS FULL | TPOI_LOAN | 306 | 7038 | 11 (0)| 00:00:01 ||* 9 | INDEX UNIQUE SCAN | TPOI_ASSET_PK | 1 | 4 | 0 (0)| 00:00:01 || 10 | NESTED LOOPS | | | | | || 11 | NESTED LOOPS | | 1 | 36 | 1 (0)| 00:00:01 || 12 | INDEX FULL SCAN | TPOI_POOL_AK2 | 1 | 13 | 0 (0)| 00:00:01 ||* 13 | INDEX RANGE SCAN | TPOI_LOAN_IDX1 | 1 | | 1 (0)| 00:00:01 ||* 14 | TABLE ACCESS BY INDEX ROWID| TPOI_LOAN | 1 | 23 | 1 (0)| 00:00:01 |----------------------------------------------------------------------------------------------------Predicate Information (identified by operation id):--------------------------------------------------- 5 - filter("L"."LOANASSET_TYPE"='O' AND ("L"."SITE_CODE"='TCBANK' OR "L"."COMPANY_CODE"='SELLERWANG')) 6 - access("L"."LOANASSET_ID"="O"."PO_ASSET_ID") 8 - filter("L"."LOANASSET_TYPE"='A' AND ("L"."SITE_CODE"='TCBANK' OR "L"."COMPANY_CODE"='SELLERWANG')) 9 - access("L"."LOANASSET_ID"="A"."ASSET_ID") 13 - access("L"."LOANASSET_TYPE"='P' AND "L"."LOANASSET_ID"="P"."POOL_ID") 14 - filter("L"."SITE_CODE"='TCBANK' OR "L"."COMPANY_CODE"='SELLERWANG')-- 上面的执行是在测试环境中的结果，和开发人员沟通，以上sql可能在同一时间执行几千次，看到这个sql太TM坑爹了，于是我做了下面的改写 SELECT COUNT(*) FROM (SELECT l.loan_id, t.customer_ref, t.prod_type, l.site_code, l.company_code, l.loanasset_type, l.loanasset_id, l.loanasset_status, l.loan_ref, l.loan_prod_code, l.loan_ccy, l.loan_amt, l.loan_outs_amt, l.loan_effectivedate, l.loan_maturitydate, l.loan_status, l.obligor_code, l.customer_code, l.update_time FROM tpoi_loan l INNER JOIN (SELECT po_asset_id, asset_name AS customer_ref, prod_type AS prod_type, 'O' AS loanasset_type FROM tpoi_po_asset o UNION ALL SELECT asset_id AS po_asset_id, a.asset_name AS customer_ref, a.prod_type AS prod_type, 'A' AS loanasset_type FROM tpoi_asset a UNION ALL SELECT pool_id AS po_asset_id, pool_name AS customer_ref, '' AS prod_type, 'P' AS loanasset_type FROM tpoi_pool p) t ON t.po_asset_id = l.loanasset_id AND t.loanasset_type = l.loanasset_type WHERE l.loanasset_type IN ('O', 'A', 'P') AND (l.site_code = 'BANK' OR l.company_code = 'SELLERWANG')) xt100 Plan hash value: 973488319---------------------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |---------------------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | 30 | 11 (0)| 00:00:01 || 1 | SORT AGGREGATE | | 1 | 30 | | || 2 | NESTED LOOPS | | 215 | 6450 | 11 (0)| 00:00:01 ||* 3 | TABLE ACCESS FULL | TPOI_LOAN | 652 | 14996 | 11 (0)| 00:00:01 ||* 4 | VIEW | | 1 | 7 | 0 (0)| 00:00:01 || 5 | UNION ALL PUSHED PREDICATE | | | | | ||* 6 | INDEX UNIQUE SCAN | TPOI_PO_ASSET_PK | 1 | 4 | 0 (0)| 00:00:01 ||* 7 | INDEX UNIQUE SCAN | TPOI_ASSET_PK | 1 | 4 | 0 (0)| 00:00:01 ||* 8 | INDEX RANGE SCAN | TPOI_POOL_AK2 | 1 | 13 | 0 (0)| 00:00:01 |---------------------------------------------------------------------------------------------------Predicate Information (identified by operation id):--------------------------------------------------- 3 - filter(("L"."LOANASSET_TYPE"='A' OR "L"."LOANASSET_TYPE"='O' OR "L"."LOANASSET_TYPE"='P') AND ("L"."SITE_CODE"='TCBANK' OR "L"."COMPANY_CODE"='SELLERWANG')) 4 - filter("T"."LOANASSET_TYPE"="L"."LOANASSET_TYPE") 6 - access("PO_ASSET_ID"="L"."LOANASSET_ID") 7 - access("ASSET_ID"="L"."LOANASSET_ID") 8 - access("POOL_ID"="L"."LOANASSET_ID")