SQL调优之业务逻辑+技术调整

SQL调优案例

最新推荐文章于 2024-03-29 17:07:58 发布

最新推荐文章于 2024-03-29 17:07:58 发布 · 1.7k 阅读

本文通过一个具体的SQL调优案例，展示了如何结合业务和技术手段优化SQL性能。案例中通过对SQL执行计划的分析，发现并解决了全表扫描导致的性能瓶颈。

部署运行你感兴趣的模型镜像

前面的SQL调优案例绝大部分都是从纯技术角度出发的，那么现在介绍一个案例，需要从业务和技术相结合的方法来进行优化

Team Leader 发来邮件说这个SQL 要得很久，下面是邮局内容，他说这个SQL有可能是 statics gather的问题

这个SQL 可能有表的statics gather 的问题，具体表可能是S_SRC_PLC。

SELECT S_SRC_PAYMENT.ROW_ID AS PRMTN_PYMT_ID,
DECODE(S_SRC_PAYMENT.PAR_PAYMENT_ID, NULL, 'Header', 'Line Item') AS PYMT_LVL_DESC,
S_SRC_PAYMENT.PAY_TYPE_CD AS PYMT_TYPE_CODE,
DECODE(S_SRC_PAYMENT.PAR_PAYMENT_ID,
NULL,
S_SRC_PAYMENT.X_PAYMENT_METHOD,
PAR.X_PAYMENT_METHOD) AS PYMT_METHD_DESC,
S_SRC_PAYMENT.CREATED_BY AS CREAT_BY_ID,
DECODE(S_SRC_PAYMENT.PAR_PAYMENT_ID,
NULL,
S_SRC_PAYMENT.X_OWNED_BY_ID,
LKP1.X_OWNED_BY) AS OWND_BY_ID,
S_SRC_PAYMENT.LAST_UPD_BY AS LAST_UPDT_BY_ID,
TRUNC(S_SRC_PAYMENT.LAST_UPD, 'dd') AS LAST_UPDT_DATE,
/* CONCAT(TO_CHAR(S_SRC_PAYMENT.LAST_UPD, 'YYYYMMDD'), '000000') AS LAST_UPDT_DATE,*/
DECODE(S_SRC_PAYMENT.PAR_PAYMENT_ID,
NULL,
S_SRC_PAYMENT.X_APPROVER_ID,
NVL(S_SRC_PAYMENT.X_APPROVER_ID, PAR.X_APPROVER_ID)) AS APPRV_NAME_ID, --Updated by David for qc1429 in R8
S_SRC_PAYMENT.PAY_STAT_CD AS PYMT_STTUS_CODE, --PEND_ATHRZ_CODE(YES, NO)
/*TO_CHAR(*/
DECODE(S_SRC_PAYMENT.PAR_PAYMENT_ID,
NULL,
S_SRC_PAYMENT.X_SAP_POST_DATE,
NVL(S_SRC_PAYMENT.X_SAP_POST_DATE, PAR.X_SAP_POST_DATE)) /*,
'YYYYMMDDHH24MISS')*/ AS POSTG_DATE, --Updated by David for qc1429 in R8
DECODE(S_SRC_PAYMENT.PAR_PAYMENT_ID,
NULL,
S_SRC_PAYMENT.X_SAP_DOC_NR,
NVL(S_SRC_PAYMENT.X_SAP_DOC_NR, PAR.X_SAP_DOC_NR)) AS SAP_NUM_CODE,
S_SRC_PAYMENT.PAYMENT_REQ_NUM AS PYMT_REQST_NUM_CODE,
S_SRC_PAYMENT.CG_GL_TOT AS PYMT_WITH_TAX_AMT,
S_SRC_PAYMENT.X_VAR_ATTRIB_27 AS WTHOLD_TAX_DESC,
S_SRC_PAYMENT.DESC_TEXT AS PYMT_CMMNT,
S_SRC_PAYMENT.X_SAP_DESC_TEXT AS PYMT_DESC,
S_SRC_PAYMENT.X_INT_ORDER AS INTRN_ORDR_CODE,
DECODE(S_SRC_PAYMENT.PAR_PAYMENT_ID,
NULL,
S_SRC_PAYMENT.X_SAP_REFERENCE_DOC,
NVL(S_SRC_PAYMENT.X_SAP_REFERENCE_DOC, PAR.X_SAP_REFERENCE_DOC)) AS INVC_NUM_CODE, --Updated by David for qc1429 in R8
S_SRC_PAYMENT.X_PAY_TO_ID AS PAYT_ACCT_ID,
S_SRC_PAYMENT.ACCNT_ID AS ACCT_ID, --(ACCT_NAME ,ACCT_DESC)
DECODE(S_SRC.SUB_TYPE,
'PG_TFMAAI_PROMOTION_ACTIVITY',
S_SRC_PAR.PERIOD_ID,
S_SRC.PERIOD_ID) AS PERD_ID,
S_SRC_PAYMENT.PROD_ID AS PROD_ID,
S_SRC_PAYMENT.X_SIEBEL_ID AS SBL_ID,
S_SRC_PAYMENT.X_VAR_ATTRIB_02 AS FUND_NAME_DESC,
S_SRC_PAYMENT.PROMO_NAME AS PRMTN_NAME_DESC,
S_SRC_PAYMENT.CG_MDF_ALLOC_ID AS MDF_ALLOC_ID,
S_SRC.NAME AS ACTVY_NAME_DESC,
S_SRC.ROW_ID AS ACTVY_ID,
S_SRC_PAYMENT.DOCNUM_GNRTD_FLG AS DEDCT_IND,
S_ORG_EXT.REGION_ID AS REGN_SKID,
S_ORG_EXT.BU_ID AS BUS_UNIT_ID,
S_SRC_PAYMENT.IND_OU_EXT_ID AS PAYT_ACCT_SKID, --(PAYT_NAME, PAYT_DESC)
S_SRC_PAYMENT.CG_MDF_ALLOC_ID AS OPT_PYMT_SKID,
--Added by Gary for CR515 2010/03/19
S_SRC_PAYMENT.X_GL_ACCOUNT AS GL_ACCT_CODE,
--Modified by Gary for CR515 2010/03/22
S_SRC_PAYMENT.X_LEGAL_ENTITY AS LEGAL_ENT_NAME,
--Added by Gary for CR515 2010/03/22
S_SRC_PAYMENT.X_SAP_COMPANY_CODE AS LEGAL_ENT_ID,
DECODE(S_SRC_PAYMENT.PAR_PAYMENT_ID,
NULL,
S_SRC_PAYMENT.ROW_ID,
S_SRC_PAYMENT.PAR_PAYMENT_ID) AS PARNT_PYMT_ID,
(CASE
WHEN S_SRC_PAYMENT.PAY_TYPE_CD = 'Deduction' THEN
S_SRC_PAYMENT.PAYMENT_REQ_NUM
WHEN S_SRC_PAYMENT.CG_CREDIT_MEMO_ID IS NOT NULL THEN
LKP2.PAYMENT_REQ_NUM
END) AS DEDCT_ID,
S_MDF_ALLOC.X_INT_ORDER AS FUND_INTL_ORDR,
DECODE(S_SRC.SUB_TYPE,
'PG_TFMAAI_PROMOTION_ACTIVITY',
S_SRC.PAR_SRC_ID,
S_SRC.ROW_ID) AS PRMTN_ID,
S_SRC.SRC_CD_FMT_ID AS ACTVY_TYPE,
--Updated by David for QC1429 in R8
--It will be changed for new CR later [Cust Invoice Date, ayment Data]
--TO_CHAR(S_SRC_PAYMENT.CREATED, 'YYYYMMDDHH24MISS') AS CREATED_DATE,
DECODE(S_SRC_PAYMENT.PAR_PAYMENT_ID,
NULL,
S_SRC_PAYMENT.CREATED,
NVL(S_SRC_PAYMENT.CREATED, PAR.CREATED)) AS CREATED_DATE,
S_SRC_PAYMENT.PERFORM_DT AS DEDCT_DATE,
S_SRC_PAYMENT.REQUEST_AMT AS DEDCT_AMT,
S_SRC_PAYMENT.X_NON_TRADE_FUND_AMT AS NON_TRADE_FUND_AMT,
S_SRC_PAYMENT.X_REASON_CODE AS REASN_CODE,
S_SRC_PAYMENT.X_REFERENCE_KEY AS REF_NUM,
S_SRC_PAYMENT.X_SAP_WITHHOLD_TAX_DESC_2 AS SAP_WTHLD_TAX_DESC_2,
S_SRC_PAYMENT.X_SAP_WITHHOLD_TAX_DESC_3 AS SAP_WTHLD_TAX_DESC_3,
S_SRC_PAYMENT.X_SAP_WHT_AMT1 AS SAP_WTHLD_TAX_AMT_1,
S_SRC_PAYMENT.X_SAP_WHT_AMT2 AS SAP_WTHLD_TAX_AMT_2,
S_SRC_PAYMENT.X_SAP_WHT_AMT3 AS SAP_WTHLD_TAX_AMT_3,
S_SRC_PAYMENT.X_SAP_DOC_DATE AS SAP_DOC_DATE,
S_SRC_PAYMENT.X_SAP_VAT_AMT AS SAP_VAT_AMT,
S_SRC_PAYMENT.X_VAR_ATTRIB_22 AS BUS_SCNAR,
DECODE(S_SRC_PAYMENT.PAY_TYPE_CD,
'Deduction',
NVL(LKP3.CG_TRADE_FUND_TOT, 0),
NULL) AS PAID_TAX_AMT,
S_SRC_PAYMENT.X_SAP_WITHHOLD_TAX_DESC_4 AS SAP_WTHLD_TAX_DESC_4,
S_SRC_PAYMENT.X_SAP_WHT_AMT4 AS SAP_WTHLD_TAX_AMT_4,
DECODE(S_SRC_PAYMENT.X_PAYMENT_TERMS,
NULL,
PAR.X_PAYMENT_TERMS,
S_SRC_PAYMENT.X_PAYMENT_TERMS) AS PYMT_TERM,
DECODE(S_SRC_PAYMENT.X_PYMNT_DUE_DT,
NULL,
PAR.X_PYMNT_DUE_DT,
S_SRC_PAYMENT.X_PYMNT_DUE_DT) AS PYMT_TERM_START_DATE,
--Updated by David for B022 on 2010-3-15
DECODE(S_SRC_PAYMENT.PAR_PAYMENT_ID,
NULL,
S_SRC_PAYMENT.X_RECEIPT_DT,
PAR.X_RECEIPT_DT) AS RECPT_DATE,
S_SRC_PAYMENT.BANK_ACCNT_NUM AS BANK_ACCT,
S_MDF.ACCNT_ID AS FUND_ACCT_ID,
S_SRC_PAYMENT.ACTL_PAY_DT AS ACTL_PAY_DATE,
NVL(S_SRC_PAYMENT.X_PYMNT_DUE_DT, PAR.X_PYMNT_DUE_DT) AS PYMT_DUE_DATE,
S_SRC_PAYMENT.BANK_DEPOSIT_TS AS BANK_DEPST_DATE,
NVL(S_SRC_PAYMENT.AMT_CURCY_CD, PAR.AMT_CURCY_CD) AS CRNCY_CODE,
S_SRC_PAYMENT.X_ISSUING_CURRENCY AS ISSUE_CRNCY_CODE,
CASE
WHEN DECODE(PLC.BUS_UNIT_NAME,
'Peru',
NVL(S_SRC_PAYMENT.X_ISSUING_CURRENCY,
PAR.X_ISSUING_CURRENCY),
NVL(S_SRC_PAYMENT.AMT_CURCY_CD, PAR.AMT_CURCY_CD)) <>
NVL(S_SRC_PAYMENT.AMT_CURCY_CD, PAR.AMT_CURCY_CD) THEN
DECODE(PLC.BUS_UNIT_NAME,
'Peru',
NVL(S_SRC_PAYMENT.X_ISSUING_CURRENCY, PAR.X_ISSUING_CURRENCY),
NVL(S_SRC_PAYMENT.AMT_CURCY_CD, PAR.AMT_CURCY_CD))
ELSE
'Local currency'
END AS DSPLY_CRNCY_CODE,
S_SRC_PAYMENT.CG_TRADE_FUND_TOT AS LOCAL_PYMT_AMT,
S_SRC_PAYMENT.X_CG_TRADE_FUND_TOT AS DYNMC_PYMT_AMT,
CASE
WHEN DECODE(PLC.BUS_UNIT_NAME,
'Peru',
NVL(S_SRC_PAYMENT.X_ISSUING_CURRENCY,
PAR.X_ISSUING_CURRENCY),
NVL(S_SRC_PAYMENT.AMT_CURCY_CD, PAR.AMT_CURCY_CD)) <>
NVL(S_SRC_PAYMENT.AMT_CURCY_CD, PAR.AMT_CURCY_CD) THEN
S_SRC_PAYMENT.X_CG_TRADE_FUND_TOT
ELSE
NULL
END AS FRGN_CRNCY_AMT,
S_MDF_ALLOC.MDF_ID AS FUND_ID,
S_SRC_PAYMENT.X_SAP_DOC_TEXT AS PYMT_SAP_DESC,
S_SRC_PAYMENT.X_SAP_DOC_TYPE AS SAP_DOC_TYPE,
-- APPRV_DT is extracted from S_SRC_PAYMENT.X_PYMNT_APPRD_DT instead of S_AUDIT_ITEM.OPERATION_DT
DECODE(S_SRC_PAYMENT.PAR_PAYMENT_ID,
NULL,
S_SRC_PAYMENT.X_PYMNT_APPRD_DT,
PAR.X_PYMNT_APPRD_DT) AS APPRV_DATE,
DECODE(S_SRC_PAYMENT.PAR_PAYMENT_ID,
NULL,
S_SRC_PAYMENT.X_PYMT_POSTING_DT,
PAR.X_PYMT_POSTING_DT) AS TRSRY_PYMT_DATE,
DECODE(S_SRC_PAYMENT.PAR_PAYMENT_ID,
NULL,
S_SRC_PAYMENT.X_PYMT_NUMBER,
PAR.X_PYMT_NUMBER) AS TRSRY_PYMT_NUM,
--Added by Gary on 2010/08/13 for R11 B004 start
DECODE(S_SRC_PAYMENT.PAR_PAYMENT_ID,
NULL,
S_SRC_PAYMENT.X_INVOICE_SCAN_DT,
PAR.X_INVOICE_SCAN_DT) AS INVC_SCAN_DATE,
--Added by Gary on 2010/08/13 for R11 B004 end
--Added and modified by Gary on 2010/08/13 for Optima11 B009(CR136) start
DECODE(S_SRC_PAYMENT.PAR_PAYMENT_ID,
NULL,
S_SRC_PAYMENT.X_SAP_DESC_TEXT,
PAR.X_SAP_DESC_TEXT) AS HDR_DESC,
S_SRC_PAYMENT.X_SAP_DESC_TEXT AS LINE_ITEM_DESC,
--Added and modified by Gary on 2010/08/13 for Optima11 B009(CR136) end
--Added by Gary on 2010/08/13 for R11 B020 start
S_SRC_PAYMENT.X_FLG_ATTRIB_02 AS ADVNC_IND,
--Added by Gary on 2010/08/13 for R11 B020 end
--modified by Gary for issue Siebel ID CN10803060
DECODE(S_SRC_PAYMENT.PAR_PAYMENT_ID,
NULL,
S_SRC_PAYMENT.X_CG_APPRD_PYMT_AMT,
PAR.X_CG_APPRD_PYMT_AMT) AS TRSRY_PYMT_AMT,
--S_SRC_PAYMENT.X_CG_APPRD_PYMT_AMT AS TRSRY_PYMT_AMT,
-- Add by Bean, Optima10, begin
DECODE(S_SRC_PAYMENT.PAR_PAYMENT_ID,
NULL,
S_SRC_PAYMENT.X_FIRST_REQUEST_DT,
NVL(S_SRC_PAYMENT.X_FIRST_REQUEST_DT, PAR.X_FIRST_REQUEST_DT)) AS FIRST_APPRV_REQST_DATE,
DECODE(S_SRC_PAYMENT.PAR_PAYMENT_ID,
NULL,
S_SRC_PAYMENT.X_PYMNT_APPRD_DT,
NVL(S_SRC_PAYMENT.X_PYMNT_APPRD_DT, PAR.X_PYMNT_APPRD_DT)) AS LATST_APPRV_REQST_DATE,
-- Add by Bean, Optima10, end
--Add by Martin, Optima10, CR605
S_SRC_PAYMENT.X_CUST_PAYMENT_NUM AS X_CUST_PAYMENT_NUM
FROM S_SRC_PAYMENT,
S_ORG_EXT,
S_SRC,
S_MDF_ALLOC,
S_SRC_PAYMENT PAR,
S_SRC S_SRC_PAR,
S_MDF,
(SELECT /*+FIRST_ROWS(1) */
S_SRC.ROW_ID, MIN(X_OWNED_BY) X_OWNED_BY
FROM S_SRC
GROUP BY S_SRC.ROW_ID) LKP1,
(SELECT D.ROW_ID, MIN(D.PAYMENT_REQ_NUM) PAYMENT_REQ_NUM
FROM S_SRC_PAYMENT D
GROUP BY D.ROW_ID) LKP2,
(SELECT P.CG_CREDIT_MEMO_ID,
SUM(P.CG_TRADE_FUND_TOT) CG_TRADE_FUND_TOT
FROM S_SRC_PAYMENT P
GROUP BY P.CG_CREDIT_MEMO_ID) LKP3,
OPT_BUS_UNIT_PLC PLC,
S_BU BU
WHERE S_MDF_ALLOC.PROMO_ID = S_SRC.ROW_ID(+)
AND S_SRC.PAR_SRC_ID = S_SRC_PAR.ROW_ID(+)
AND S_SRC_PAYMENT.ACCNT_ID = S_ORG_EXT.ROW_ID(+)
AND S_SRC_PAYMENT.CG_MDF_ALLOC_ID = S_MDF_ALLOC.ROW_ID(+)
AND S_SRC_PAYMENT.PAR_PAYMENT_ID = PAR.ROW_ID(+)
AND S_SRC_PAYMENT.X_SRC_ID = LKP1.ROW_ID(+)
AND S_SRC_PAYMENT.CG_CREDIT_MEMO_ID = LKP2.ROW_ID(+)
AND S_SRC_PAYMENT.ROW_ID = LKP3.CG_CREDIT_MEMO_ID(+)
AND S_ORG_EXT.BU_ID = BU.ROW_ID(+)
AND BU.NAME = PLC.BUS_UNIT_NAME(+)
AND S_MDF_ALLOC.MDF_ID = S_MDF.ROW_ID(+)

拿到SQL我们就要先大概浏览一下SQL语句，我发现 SQL语句中有一个hint first_rows(1)的提示
first_rows(1)这个提示会影响CBO选择执行计划，CBO会更倾向于nested loops，因为nested loops是
快速返回数据的方法。

好的我来看一下执行计划：

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------------------------------------------------------------------

Plan hash value: 1380723651

----------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | Pstart| Pstop |
----------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 8440 | | 69615 (7)| 00:03:38 | | |
| 1 | NESTED LOOPS OUTER | | 2 | 8440 | | 69615 (7)| 00:03:38 | | |
| 2 | NESTED LOOPS OUTER | | 2 | 8406 | | 69611 (7)| 00:03:38 | | |
|* 3 | HASH JOIN RIGHT OUTER | | 2 | 8268 | 284M| 69607 (7)| 00:03:38 | | |
| 4 | TABLE ACCESS FULL | S_SRC_PAYMENT_PLC | 2388K| 257M| | 15076 (9)| 00:00:48 | | |
|* 5 | HASH JOIN RIGHT OUTER | | 2 | 6862 | 259M| 50145 (7)| 00:02:37 | | |
| 6 | VIEW | | 2370K| 232M| | 26007 (5)| 00:01:22 | | |
| 7 | HASH GROUP BY | | 2370K| 76M| 100M| 26007 (5)| 00:01:22 | | |
| 8 | TABLE ACCESS FULL | S_SRC_PAYMENT_PLC | 2388K| 77M| | 14192 (3)| 00:00:45 | | |
|* 9 | HASH JOIN RIGHT OUTER | | 2 | 5682 | | 20125 (9)| 00:01:03 | | |
| 10 | TABLE ACCESS FULL | OPT_BUS_UNIT_PLC | 15 | 150 | | 19 (0)| 00:00:01 | | |
|* 11 | HASH JOIN RIGHT OUTER | | 2 | 4708 | | 20105 (9)| 00:01:03 | | |
| 12 | TABLE ACCESS FULL | S_BU_PLC | 16 | 288 | | 4 (0)| 00:00:01 | | |
|* 13 | HASH JOIN RIGHT OUTER | | 2 | 3754 | 26M| 20101 (9)| 00:01:03 | | |
| 14 | TABLE ACCESS FULL | S_ORG_EXT_PLC | 349K| 22M| | 1560 (6)| 00:00:05 | | |
|* 15 | HASH JOIN RIGHT OUTER | | 2 | 2836 | 10M| 18123 (10)| 00:00:57 | | |
| 16 | TABLE ACCESS FULL | S_MDF_PLC | 342K| 6363K| | 1537 (3)| 00:00:05 | | |
|* 17 | HASH JOIN RIGHT OUTER | | 2 | 2054 | 16M| 16417 (10)| 00:00:52 | | |
| 18 | TABLE ACCESS FULL | S_MDF_ALLOC_PLC | 347K| 12M| | 784 (5)| 00:00:03 | | |
|* 19 | HASH JOIN RIGHT OUTER | | 2 | 1310 | | 15372 (11)| 00:00:49 | | |
| 20 | VIEW | | 300 | 4500 | | 15363 (11)| 00:00:49 | | |
| 21 | HASH GROUP BY | | 300 | 2100 | | 15363 (11)| 00:00:49 | | |
| 22 | TABLE ACCESS FULL | S_SRC_PAYMENT_PLC | 2388K| 15M| | 14670 (6)| 00:00:46 | | |
| 23 | NESTED LOOPS OUTER | | 2 | 640 | | 8 (0)| 00:00:01 | | |
| 24 | TABLE ACCESS FULL | S_SRC_PAYMENT_PLC | 2388K| 621M| | 2 (0)| 00:00:01 | | |
| 25 | VIEW PUSHED PREDICATE | | 1 | 47 | | 3 (0)| 00:00:01 | | |
|* 26 | FILTER | | | | | | | | |
| 27 | SORT AGGREGATE | | 1 | 11 | | | | | |
| 28 | TABLE ACCESS BY GLOBAL INDEX ROWID| S_SRC_PLC | 1 | 11 | | 3 (0)| 00:00:01 | ROWID | ROWID |
|* 29 | INDEX UNIQUE SCAN | S_SRC_PLC_PK | 1 | | | 2 (0)| 00:00:01 | | |
| 30 | TABLE ACCESS BY GLOBAL INDEX ROWID | S_SRC_PLC | 1 | 69 | | 2 (0)| 00:00:01 | ROWID | ROWID |
|* 31 | INDEX UNIQUE SCAN | S_SRC_PLC_PK | 1 | | | 1 (0)| 00:00:01 | | |
| 32 | TABLE ACCESS BY GLOBAL INDEX ROWID | S_SRC_PLC | 1 | 17 | | 2 (0)| 00:00:01 | ROWID | ROWID |
|* 33 | INDEX UNIQUE SCAN | S_SRC_PLC_PK | 1 | | | 1 (0)| 00:00:01 | | |
----------------------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

3 - access("S_SRC_PAYMENT"."PAR_PAYMENT_ID"="PAR"."ROW_ID"(+))
5 - access("S_SRC_PAYMENT"."CG_CREDIT_MEMO_ID"="LKP2"."ROW_ID"(+))
9 - access("BU"."NAME"="PLC"."BUS_UNIT_NAME"(+))
11 - access("S_ORG_EXT"."BU_ID"="BU"."ROW_ID"(+))
13 - access("S_SRC_PAYMENT"."ACCNT_ID"="S_ORG_EXT"."ROW_ID"(+))
15 - access("S_MDF_ALLOC"."MDF_ID"="S_MDF"."ROW_ID"(+))
17 - access("S_SRC_PAYMENT"."CG_MDF_ALLOC_ID"="S_MDF_ALLOC"."ROW_ID"(+))
19 - access("S_SRC_PAYMENT"."ROW_ID"="LKP3"."CG_CREDIT_MEMO_ID"(+))
26 - filter(COUNT(*)>0)
29 - access("S_SRC"."ROW_ID"="S_SRC_PAYMENT"."X_SRC_ID")
31 - access("S_MDF_ALLOC"."PROMO_ID"="S_SRC"."ROW_ID"(+))
33 - access("S_SRC"."PAR_SRC_ID"="S_SRC_PAR"."ROW_ID"(+))

CBO 认为这个SQL只返回2行数据，我询问发邮件的人，他说这个SQL肯定不会只返回2行数据，它会返回2百多行的数据
于是我去掉 first_rows(1) 这个 hint，那么我们再来看一下这个SQL语句的执行计划:

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------------------------------------

Plan hash value: 2079133087

-----------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | Pstart| Pstop |
-----------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2389K| 2090M| | 1536K (7)| 01:20:06 | | |
|* 1 | HASH JOIN RIGHT OUTER | | 2389K| 2090M| 341M| 1536K (7)| 01:20:06 | | |
| 2 | VIEW | | 2370K| 314M| | 26007 (5)| 00:01:22 | | |
| 3 | HASH GROUP BY | | 2370K| 76M| 100M| 26007 (5)| 00:01:22 | | |
| 4 | TABLE ACCESS FULL | S_SRC_PAYMENT_PLC | 2388K| 77M| | 14192 (3)| 00:00:45 | | |
|* 5 | HASH JOIN RIGHT OUTER | | 2389K| 1773M| 284M| 1478K (7)| 01:17:03 | | |
| 6 | TABLE ACCESS FULL | S_SRC_PAYMENT_PLC | 2388K| 257M| | 15076 (9)| 00:00:48 | | |
|* 7 | HASH JOIN OUTER | | 2389K| 1515M| 1328M| 1435K (7)| 01:14:49 | | |
|* 8 | HASH JOIN RIGHT OUTER | | 2389K| 1301M| | 835K (7)| 00:43:33 | | |
| 9 | TABLE ACCESS FULL | OPT_BUS_UNIT_PLC | 15 | 150 | | 19 (0)| 00:00:01 | | |
|* 10 | HASH JOIN RIGHT OUTER | | 2389K| 1278M| | 835K (7)| 00:43:32 | | |
| 11 | TABLE ACCESS FULL | S_BU_PLC | 16 | 288 | | 4 (0)| 00:00:01 | | |
|* 12 | HASH JOIN RIGHT OUTER | | 2389K| 1237M| 26M| 835K (7)| 00:43:32 | | |
| 13 | TABLE ACCESS FULL | S_ORG_EXT_PLC | 349K| 22M| | 1560 (6)| 00:00:05 | | |
|* 14 | HASH JOIN RIGHT OUTER | | 2389K| 1082M| 10M| 816K (7)| 00:42:33 | | |
| 15 | TABLE ACCESS FULL | S_MDF_PLC | 342K| 6363K| | 1537 (3)| 00:00:05 | | |
|* 16 | HASH JOIN OUTER | | 2389K| 1039M| 1027M| 798K (7)| 00:41:38 | | |
|* 17 | HASH JOIN OUTER | | 2389K| 1000M| 870M| 453K (8)| 00:23:39 | | |
|* 18 | HASH JOIN RIGHT OUTER | | 2388K| 842M| 16M| 43534 (8)| 00:02:17 | | |
| 19 | TABLE ACCESS FULL | S_MDF_ALLOC_PLC | 347K| 12M| | 784 (5)| 00:00:03 | | |
|* 20 | HASH JOIN RIGHT OUTER| | 2388K| 758M| | 30542 (10)| 00:01:36 | | |
| 21 | VIEW | | 300 | 18000 | | 15363 (11)| 00:00:49 | | |
| 22 | HASH GROUP BY | | 300 | 2100 | | 15363 (11)| 00:00:49 | | |
| 23 | TABLE ACCESS FULL | S_SRC_PAYMENT_PLC | 2388K| 15M| | 14670 (6)| 00:00:46 | | |
| 24 | TABLE ACCESS FULL | S_SRC_PAYMENT_PLC | 2388K| 621M| | 15105 (9)| 00:00:48 | | |
| 25 | PARTITION LIST ALL | | 66M| 4358M| | 316K (9)| 00:16:29 | 1 | 8 |
| 26 | TABLE ACCESS FULL | S_SRC_PLC | 66M| 4358M| | 316K (9)| 00:16:29 | 1 | 8 |
| 27 | PARTITION LIST ALL | | 66M| 1073M| | 297K (4)| 00:15:32 | 1 | 8 |
| 28 | TABLE ACCESS FULL | S_SRC_PLC | 66M| 1073M| | 297K (4)| 00:15:32 | 1 | 8 |
| 29 | VIEW | | 66M| 5934M| | 475K (9)| 00:24:47 | | |
| 30 | HASH GROUP BY | | 66M| 694M| 1265M| 475K (9)| 00:24:47 | | |
| 31 | PARTITION LIST ALL | | 66M| 694M| | 303K (6)| 00:15:50 | 1 | 8 |
| 32 | TABLE ACCESS FULL | S_SRC_PLC | 66M| 694M| | 303K (6)| 00:15:50 | 1 | 8 |
-----------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - access("S_SRC_PAYMENT"."CG_CREDIT_MEMO_ID"="LKP2"."ROW_ID"(+))
5 - access("S_SRC_PAYMENT"."PAR_PAYMENT_ID"="PAR"."ROW_ID"(+))
7 - access("S_SRC_PAYMENT"."X_SRC_ID"="LKP1"."ROW_ID"(+))
8 - access("BU"."NAME"="PLC"."BUS_UNIT_NAME"(+))
10 - access("S_ORG_EXT"."BU_ID"="BU"."ROW_ID"(+))
12 - access("S_SRC_PAYMENT"."ACCNT_ID"="S_ORG_EXT"."ROW_ID"(+))
14 - access("S_MDF_ALLOC"."MDF_ID"="S_MDF"."ROW_ID"(+))
16 - access("S_SRC"."PAR_SRC_ID"="S_SRC_PAR"."ROW_ID"(+))
17 - access("S_MDF_ALLOC"."PROMO_ID"="S_SRC"."ROW_ID"(+))
18 - access("S_SRC_PAYMENT"."CG_MDF_ALLOC_ID"="S_MDF_ALLOC"."ROW_ID"(+))
20 - access("S_SRC_PAYMENT"."ROW_ID"="LKP3"."CG_CREDIT_MEMO_ID"(+))

54 rows selected.

这一次CBO认为会返回2百30多万的数据，注意看执行计划它会对表S_SRC_PLC 做3次全表扫描

SQL> select count(*) from S_SRC_PLC;

COUNT(*)
----------
66164470

Elapsed: 00:01:29.71

3次全表扫描之后会和其他表做HASH JOIN，那么现在知道这个SQL语句的性能瓶颈了，如果能减少对这3个表的全表扫描次数，减少取得的数据，
那么这个SQL性能会大大提高，同时S_SRC_PAYMENT_PLC这个表会扫描4次，它有2百多万行数据

根据我的建议，在不更改业务逻辑的前提下改写了相关SQL，将大表的分区全扫描改为单分区扫描，同时去掉一次不必要的扫描。

SQL改写如下：

SELECT P.PARNT_PYMT_ID AS PARNT_PYMT_ID,
P.PRMTN_PYMT_ID AS PRMTN_PYMT_ID,
P.PYMT_LVL_DESC AS PYMT_LVL_DESC,
P.PYMT_TYPE_CODE AS PYMT_TYPE_CODE,
DECODE(P.PYMT_TYPE_CODE,
'Vendor Invoice',
LOV.VAL,
P.PYMT_METHD_DESC) AS PYMT_METHD_DESC,
P.CREAT_BY_ID AS CREAT_BY_ID,
P.OWND_BY_ID AS OWND_BY_ID,
P.LAST_UPDT_BY_ID AS LAST_UPDT_BY_ID,
P.LAST_UPDT_DATE AS LAST_UPDT_DATE,
P.APPRV_NAME_ID AS APPRV_NAME_ID,
NVL(BU.BUS_UNIT_SKID, 0) AS BUS_UNIT_SKID,
NVL(C.FISC_YR_SKID, 0) AS FY_DATE_SKID,
NVL(C.CAL_MASTR_SKID, 0) AS DATE_SKID,
NVL(FUND.FUND_SKID, 0) AS FUND_SKID,
NVL(PRM.PRMTN_SKID, 0) AS PRMTN_SKID,
NVL(DECODE(MAS.DAY_DATE, NULL, 0, MAS.CAL_MASTR_SKID), 0) AS POSTG_DATE_SKID,
NVL(A1.ACCT_SKID, 0) AS ACCT_FUND_SKID,
NVL(ACC.ACCT_SKID, 0) AS PAYT_ACCT_SKID,
ACC.NAME AS PAYT_NAME,
ACC.ACCT_NAME AS PAYT_DESC,
NVL(A2.ACCT_SKID, 0) AS ACCT_PRMTN_SKID,
A2.NAME AS ACCT_NAME,
A2.ACCT_NAME AS ACCT_DESC,
NVL(PROD.PROD_SKID, 0) AS PROD_SKID,
U1.LOGIN_NAME AS CREAT_BY_NAME,
U2.LOGIN_NAME AS OWND_BY_NAME,
U3.LOGIN_NAME AS LAST_UPDT_BY_NAME,
U4.LOGIN_NAME AS APPRV_NAME,
TRIM(C1.FIRST_NAME || ' ' || C1.LAST_NAME) AS CREAT_BY_DESC,
TRIM(C2.FIRST_NAME || ' ' || C2.LAST_NAME) AS OWND_BY_DESC,
TRIM(C3.FIRST_NAME || ' ' || C3.LAST_NAME) AS LAST_UPDT_BY_DESC,
TRIM(C4.FIRST_NAME || ' ' || C4.LAST_NAME) AS APPRV_BY_DESC,
--NVL(PYMT.OPT_PYMT_SKID,0) as OPT_PYMT_SKID,
0 AS OPT_PYMT_SKID,
NVL(ACT.ACTVY_SKID, 0) AS ACTVY_SKID,
P.PYMT_STTUS_CODE AS PYMT_STTUS_CODE,
DECODE(P.PYMT_STTUS_CODE, 'Requested', 'YES', 'NO') AS PEND_ATHRZ_CODE,
P.POSTG_DATE AS POSTG_DATE,
P.SAP_NUM_CODE AS SAP_NUM_CODE,
P.PYMT_REQST_NUM_CODE AS PYMT_REQST_NUM_CODE,
NVL(P.PYMT_WITH_TAX_AMT, 0) AS PYMT_WITH_TAX_AMT,
P.WTHOLD_TAX_DESC AS WTHOLD_TAX_DESC,
P.PYMT_CMMNT AS PYMT_CMMNT_DESC,
P.PYMT_DESC AS PYMT_DESC,
P.INTRN_ORDR_CODE AS INTRN_ORDR_CODE,
P.INVC_NUM_CODE AS INVC_NUM_CODE,
P.PAYT_ACCT_ID AS PAYT_ACCT_ID,
P.ACCT_ID AS ACCT_ID,
P.PERD_ID AS PERD_ID,
P.PROD_ID AS PROD_ID,
P.SBL_ID AS SBL_ID,
P.FUND_NAME_DESC AS FUND_NAME_DESC,
P.PRMTN_NAME_DESC AS PRMTN_NAME_DESC,
P.MDF_ALLOC_ID AS MDF_ALLOC_ID,
P.ACTVY_NAME_DESC AS ACTVY_NAME_DESC,
P.ACTVY_ID AS ACTVY_ID,
P.DEDCT_IND AS DEDCT_IND,
--P.REGN_SKID as REGN_SKID,
P.BUS_UNIT_ID AS BUS_UNIT_ID,
P.DEDCT_ID AS DEDCT_ID,
P.FUND_INTL_ORDR AS FUND_INTL_ORDR_DESC,
P.PRMTN_ID AS PRMTN_ID,
P.ACTVY_TYPE AS ACTVY_TYPE_CODE,
P.CREATED_DATE AS CREAT_DATE,
P.DEDCT_DATE AS DEDCT_DATE,
NVL(P.DEDCT_AMT, 0) AS DEDCT_AMT,
NVL(P.NON_TRADE_FUND_AMT, 0) AS NON_TRADE_FUND_AMT,
P.REASN_CODE AS REASN_CODE,
P.REF_NUM AS REF_NUM,
P.SAP_WTHLD_TAX_DESC_2 AS SAP_WTHLD_TAX_DESC_2,
P.SAP_WTHLD_TAX_DESC_3 AS SAP_WTHLD_TAX_DESC_3,
NVL(P.SAP_WTHLD_TAX_AMT_1, 0) AS SAP_WTHLD_TAX_AMT_1,
NVL(P.SAP_WTHLD_TAX_AMT_2, 0) AS SAP_WTHLD_TAX_AMT_2,
NVL(P.SAP_WTHLD_TAX_AMT_3, 0) AS SAP_WTHLD_TAX_AMT_3,
P.SAP_DOC_DATE AS SAP_DOC_DATE,
NVL(P.SAP_VAT_AMT, 0) AS SAP_VAT_AMT,
P.BUS_SCNAR AS BUS_SCNAR,
NVL(P.PAID_TAX_AMT, 0) AS PAID_TAX_AMT,
P.SAP_WTHLD_TAX_DESC_4 AS SAP_WTHLD_TAX_DESC_4,
NVL(P.SAP_WTHLD_TAX_AMT_4, 0) AS SAP_WTHLD_TAX_AMT_4,
P.PYMT_TERM AS PYMT_TERM,
P.PYMT_TERM_START_DATE AS PYMT_TERM_START_DATE,
P.RECPT_DATE AS RECPT_DATE,
P.BANK_ACCT AS BANK_ACCT,
P.FUND_ACCT_ID,
P.ACTL_PAY_DATE AS ACTL_PAY_DATE,
P.PYMT_DUE_DATE AS PYMT_DUE_DATE,
P.BANK_DEPST_DATE AS BANK_DEPST_DATE,
P.CRNCY_CODE AS CRNCY_CODE,
P.ISSUE_CRNCY_CODE AS ISSUE_CRNCY_CODE,
P.DSPLY_CRNCY_CODE AS DSPLY_CRNCY_CODE,
P.LOCAL_PYMT_AMT AS LOCAL_PYMT_AMT,
P.DYNMC_PYMT_AMT AS DYNMC_PYMT_AMT,
NVL(P.FRGN_CRNCY_AMT, 0) AS FRGN_CRNCY_AMT,
P.FUND_ID AS FUND_ID,
P.PYMT_SAP_DESC AS PYMT_SAP_DESC,
P.SAP_DOC_TYPE AS SAP_DOC_CODE,
P.APPRV_DATE AS APPRV_DATE,
P.TRSRY_PYMT_DATE AS TRSRY_PYMT_DATE,
P.TRSRY_PYMT_NUM AS TRSRY_PYMT_NUM,
P.TRSRY_PYMT_AMT AS TRSRY_PYMT_AMT,
-- Optima10, B011, Bean, Begin
P.FIRST_APPRV_REQST_DATE AS FIRST_APPRV_REQST_DATE,
P.LATST_APPRV_REQST_DATE AS LATST_APPRV_REQST_DATE,
-- Optima10, B011, Bean, End
-- For consistency with the original design
0 AS CDC_CODE,
0 AS ETL_MGRT_CODE,
0 AS ETL_RUN_ID,
0 AS PRMTN_PYMT_SKID,
0 AS REGN_ID,
0 AS REGN_SKID,
' ' AS REGN_CODE,
0 AS PERD_END_DATE_SKID,
0 AS PERD_START_DATE_SKID,
--Added by Vincent for CR3094
NVL2(LOV_INVOICE.VAL, 'Y', 'N') AS ON_INVC_IND,
-- Added by Simon to keep Booked Amount logic consistent with TX 2010-5-20
NVL2(LOV_ACTL.VAL,
NVL2(LOV_ACTL2.VAL,
(CASE
WHEN P.PYMT_STTUS_CODE = 'Booked' AND
(LOV_ACTL2.X_FILTER_VAL NOT IN ('Off Invoice', 'On Invoice') OR
(LOV_ACTL2.X_FILTER_VAL IN ('Off Invoice', 'On Invoice') AND
P.PRMTN_PYMT_ID = P.PARNT_PYMT_ID)) THEN
'Y'
ELSE
'N'
END),
'N'),
'N') AS PYMT_BOOK_IND,
--Added by Gary for CR515 2010/03/19
P.GL_ACCT_CODE AS GL_ACCT_CODE,
P.LEGAL_ENT_ID AS LEGAL_ENT_ID,
--Added by Gary on 2010/08/13 for R11 B004 start
P.INVC_SCAN_DATE AS INVC_SCAN_DATE,
--Added by Gary on 2010/08/13 for R11 B004 end
--Added and modified by Gary on 2010/08/13 for Optima11 B009(CR136) start
P.HDR_DESC AS HDR_DESC,
P.LINE_ITEM_DESC AS LINE_ITEM_DESC,
--Added and modified by Gary on 2010/08/13 for Optima11 B009(CR136) end
--Added by Gary on 2010/08/13 for R11 B020 start
P.ADVNC_IND AS ADVNC_IND,
--Added by Gary on 2010/08/13 for R11 B020 end
--Added by Gary for CR515 2010/03/22
P.LEGAL_ENT_NAME AS LEGAL_ENT_NAME,
--Add by Martin, Optima10, CR605
P.X_CUST_PAYMENT_NUM AS DOC_LINE_ITEM_NUM,
--Added by Kingham for CR687 in Optima10
--On 28-Jun-2010
-- add by Leo on Jun 5, 2010 for CR04 - 340, begin
CASE
WHEN LOV_ACTL2.X_FILTER_VAL IN ('Off Invoice', 'On Invoice') AND
P.PYMT_STTUS_CODE = 'Booked' THEN
P.LOCAL_PYMT_AMT
ELSE
0
END AS ON_INVC_BOOK_AMT,
CASE
WHEN LOV_ACTL2.X_FILTER_VAL NOT IN ('Off Invoice', 'On Invoice') AND
P.PYMT_STTUS_CODE = 'Booked' THEN
P.LOCAL_PYMT_AMT
ELSE
0
END AS NON_ON_INVC_BOOK_AMT
-- add by Leo on Jun 5, 2010 for CR04 - 340, end
FROM (SELECT S_SRC_PAYMENT.ROW_ID AS PRMTN_PYMT_ID,
DECODE(S_SRC_PAYMENT.PAR_PAYMENT_ID,
NULL,
'Header',
'Line Item') AS PYMT_LVL_DESC,
S_SRC_PAYMENT.PAY_TYPE_CD AS PYMT_TYPE_CODE,
DECODE(S_SRC_PAYMENT.PAR_PAYMENT_ID,
NULL,
S_SRC_PAYMENT.X_PAYMENT_METHOD,
PAR.X_PAYMENT_METHOD) AS PYMT_METHD_DESC,
S_SRC_PAYMENT.CREATED_BY AS CREAT_BY_ID,
DECODE(S_SRC_PAYMENT.PAR_PAYMENT_ID,
NULL,
S_SRC_PAYMENT.X_OWNED_BY_ID,
S_SRC_PAR.X_OWNED_BY) AS OWND_BY_ID,
S_SRC_PAYMENT.LAST_UPD_BY AS LAST_UPDT_BY_ID,
TRUNC(S_SRC_PAYMENT.LAST_UPD, 'dd') AS LAST_UPDT_DATE,
/* CONCAT(TO_CHAR(S_SRC_PAYMENT.LAST_UPD, 'YYYYMMDD'), '000000') AS LAST_UPDT_DATE,*/
DECODE(S_SRC_PAYMENT.PAR_PAYMENT_ID,
NULL,
S_SRC_PAYMENT.X_APPROVER_ID,
NVL(S_SRC_PAYMENT.X_APPROVER_ID, PAR.X_APPROVER_ID)) AS APPRV_NAME_ID, --Updated by David for qc1429 in R8
S_SRC_PAYMENT.PAY_STAT_CD AS PYMT_STTUS_CODE, --PEND_ATHRZ_CODE(YES, NO)
/*TO_CHAR(*/
DECODE(S_SRC_PAYMENT.PAR_PAYMENT_ID,
NULL,
S_SRC_PAYMENT.X_SAP_POST_DATE,
NVL(S_SRC_PAYMENT.X_SAP_POST_DATE, PAR.X_SAP_POST_DATE)) /*,
'YYYYMMDDHH24MISS')*/ AS POSTG_DATE, --Updated by David for qc1429 in R8
DECODE(S_SRC_PAYMENT.PAR_PAYMENT_ID,
NULL,
S_SRC_PAYMENT.X_SAP_DOC_NR,
NVL(S_SRC_PAYMENT.X_SAP_DOC_NR, PAR.X_SAP_DOC_NR)) AS SAP_NUM_CODE,
S_SRC_PAYMENT.PAYMENT_REQ_NUM AS PYMT_REQST_NUM_CODE,
S_SRC_PAYMENT.CG_GL_TOT AS PYMT_WITH_TAX_AMT,
S_SRC_PAYMENT.X_VAR_ATTRIB_27 AS WTHOLD_TAX_DESC,
S_SRC_PAYMENT.DESC_TEXT AS PYMT_CMMNT,
S_SRC_PAYMENT.X_SAP_DESC_TEXT AS PYMT_DESC,
S_SRC_PAYMENT.X_INT_ORDER AS INTRN_ORDR_CODE,
DECODE(S_SRC_PAYMENT.PAR_PAYMENT_ID,
NULL,
S_SRC_PAYMENT.X_SAP_REFERENCE_DOC,
NVL(S_SRC_PAYMENT.X_SAP_REFERENCE_DOC,
PAR.X_SAP_REFERENCE_DOC)) AS INVC_NUM_CODE, --Updated by David for qc1429 in R8
S_SRC_PAYMENT.X_PAY_TO_ID AS PAYT_ACCT_ID,
S_SRC_PAYMENT.ACCNT_ID AS ACCT_ID, --(ACCT_NAME ,ACCT_DESC)
DECODE(S_SRC.SUB_TYPE,
'PG_TFMAAI_PROMOTION_ACTIVITY',
S_SRC_PAR.PERIOD_ID,
S_SRC.PERIOD_ID) AS PERD_ID,
S_SRC_PAYMENT.PROD_ID AS PROD_ID,
S_SRC_PAYMENT.X_SIEBEL_ID AS SBL_ID,
S_SRC_PAYMENT.X_VAR_ATTRIB_02 AS FUND_NAME_DESC,
S_SRC_PAYMENT.PROMO_NAME AS PRMTN_NAME_DESC,
S_SRC_PAYMENT.CG_MDF_ALLOC_ID AS MDF_ALLOC_ID,
S_SRC.NAME AS ACTVY_NAME_DESC,
S_SRC.ROW_ID AS ACTVY_ID,
S_SRC_PAYMENT.DOCNUM_GNRTD_FLG AS DEDCT_IND,
S_ORG_EXT.REGION_ID AS REGN_SKID,
S_ORG_EXT.BU_ID AS BUS_UNIT_ID,
S_SRC_PAYMENT.IND_OU_EXT_ID AS PAYT_ACCT_SKID, --(PAYT_NAME, PAYT_DESC)
S_SRC_PAYMENT.CG_MDF_ALLOC_ID AS OPT_PYMT_SKID,
--Added by Gary for CR515 2010/03/19
S_SRC_PAYMENT.X_GL_ACCOUNT AS GL_ACCT_CODE,
--Modified by Gary for CR515 2010/03/22
S_SRC_PAYMENT.X_LEGAL_ENTITY AS LEGAL_ENT_NAME,
--Added by Gary for CR515 2010/03/22
S_SRC_PAYMENT.X_SAP_COMPANY_CODE AS LEGAL_ENT_ID,
DECODE(S_SRC_PAYMENT.PAR_PAYMENT_ID,
NULL,
S_SRC_PAYMENT.ROW_ID,
S_SRC_PAYMENT.PAR_PAYMENT_ID) AS PARNT_PYMT_ID,
(CASE
WHEN S_SRC_PAYMENT.PAY_TYPE_CD = 'Deduction' THEN
S_SRC_PAYMENT.PAYMENT_REQ_NUM
WHEN S_SRC_PAYMENT.CG_CREDIT_MEMO_ID IS NOT NULL THEN
LKP2.PAYMENT_REQ_NUM
END) AS DEDCT_ID,
S_MDF_ALLOC.X_INT_ORDER AS FUND_INTL_ORDR,
DECODE(S_SRC.SUB_TYPE,
'PG_TFMAAI_PROMOTION_ACTIVITY',
S_SRC.PAR_SRC_ID,
S_SRC.ROW_ID) AS PRMTN_ID,
S_SRC.SRC_CD_FMT_ID AS ACTVY_TYPE,
--Updated by David for QC1429 in R8
--It will be changed for new CR later [Cust Invoice Date, ayment Data]
--TO_CHAR(S_SRC_PAYMENT.CREATED, 'YYYYMMDDHH24MISS') AS CREATED_DATE,
DECODE(S_SRC_PAYMENT.PAR_PAYMENT_ID,
NULL,
S_SRC_PAYMENT.CREATED,
NVL(S_SRC_PAYMENT.CREATED, PAR.CREATED)) AS CREATED_DATE,
S_SRC_PAYMENT.PERFORM_DT AS DEDCT_DATE,
S_SRC_PAYMENT.REQUEST_AMT AS DEDCT_AMT,
S_SRC_PAYMENT.X_NON_TRADE_FUND_AMT AS NON_TRADE_FUND_AMT,
S_SRC_PAYMENT.X_REASON_CODE AS REASN_CODE,
S_SRC_PAYMENT.X_REFERENCE_KEY AS REF_NUM,
S_SRC_PAYMENT.X_SAP_WITHHOLD_TAX_DESC_2 AS SAP_WTHLD_TAX_DESC_2,
S_SRC_PAYMENT.X_SAP_WITHHOLD_TAX_DESC_3 AS SAP_WTHLD_TAX_DESC_3,
S_SRC_PAYMENT.X_SAP_WHT_AMT1 AS SAP_WTHLD_TAX_AMT_1,
S_SRC_PAYMENT.X_SAP_WHT_AMT2 AS SAP_WTHLD_TAX_AMT_2,
S_SRC_PAYMENT.X_SAP_WHT_AMT3 AS SAP_WTHLD_TAX_AMT_3,
S_SRC_PAYMENT.X_SAP_DOC_DATE AS SAP_DOC_DATE,
S_SRC_PAYMENT.X_SAP_VAT_AMT AS SAP_VAT_AMT,
S_SRC_PAYMENT.X_VAR_ATTRIB_22 AS BUS_SCNAR,
DECODE(S_SRC_PAYMENT.PAY_TYPE_CD,
'Deduction',
NVL(LKP3.CG_TRADE_FUND_TOT, 0),
NULL) AS PAID_TAX_AMT,
S_SRC_PAYMENT.X_SAP_WITHHOLD_TAX_DESC_4 AS SAP_WTHLD_TAX_DESC_4,
S_SRC_PAYMENT.X_SAP_WHT_AMT4 AS SAP_WTHLD_TAX_AMT_4,
DECODE(S_SRC_PAYMENT.X_PAYMENT_TERMS,
NULL,
PAR.X_PAYMENT_TERMS,
S_SRC_PAYMENT.X_PAYMENT_TERMS) AS PYMT_TERM,
DECODE(S_SRC_PAYMENT.X_PYMNT_DUE_DT,
NULL,
PAR.X_PYMNT_DUE_DT,
S_SRC_PAYMENT.X_PYMNT_DUE_DT) AS PYMT_TERM_START_DATE,
--Updated by David for B022 on 2010-3-15
DECODE(S_SRC_PAYMENT.PAR_PAYMENT_ID,
NULL,
S_SRC_PAYMENT.X_RECEIPT_DT,
PAR.X_RECEIPT_DT) AS RECPT_DATE,
S_SRC_PAYMENT.BANK_ACCNT_NUM AS BANK_ACCT,
S_MDF.ACCNT_ID AS FUND_ACCT_ID,
S_SRC_PAYMENT.ACTL_PAY_DT AS ACTL_PAY_DATE,
NVL(S_SRC_PAYMENT.X_PYMNT_DUE_DT, PAR.X_PYMNT_DUE_DT) AS PYMT_DUE_DATE,
S_SRC_PAYMENT.BANK_DEPOSIT_TS AS BANK_DEPST_DATE,
NVL(S_SRC_PAYMENT.AMT_CURCY_CD, PAR.AMT_CURCY_CD) AS CRNCY_CODE,
S_SRC_PAYMENT.X_ISSUING_CURRENCY AS ISSUE_CRNCY_CODE,
CASE
WHEN DECODE(PLC.BUS_UNIT_NAME,
'Peru',
NVL(S_SRC_PAYMENT.X_ISSUING_CURRENCY,
PAR.X_ISSUING_CURRENCY),
NVL(S_SRC_PAYMENT.AMT_CURCY_CD, PAR.AMT_CURCY_CD)) <>
NVL(S_SRC_PAYMENT.AMT_CURCY_CD, PAR.AMT_CURCY_CD) THEN
DECODE(PLC.BUS_UNIT_NAME,
'Peru',
NVL(S_SRC_PAYMENT.X_ISSUING_CURRENCY,
PAR.X_ISSUING_CURRENCY),
NVL(S_SRC_PAYMENT.AMT_CURCY_CD, PAR.AMT_CURCY_CD))
ELSE
'Local currency'
END AS DSPLY_CRNCY_CODE,
S_SRC_PAYMENT.CG_TRADE_FUND_TOT AS LOCAL_PYMT_AMT,
S_SRC_PAYMENT.X_CG_TRADE_FUND_TOT AS DYNMC_PYMT_AMT,
CASE
WHEN DECODE(PLC.BUS_UNIT_NAME,
'Peru',
NVL(S_SRC_PAYMENT.X_ISSUING_CURRENCY,
PAR.X_ISSUING_CURRENCY),
NVL(S_SRC_PAYMENT.AMT_CURCY_CD, PAR.AMT_CURCY_CD)) <>
NVL(S_SRC_PAYMENT.AMT_CURCY_CD, PAR.AMT_CURCY_CD) THEN
S_SRC_PAYMENT.X_CG_TRADE_FUND_TOT
ELSE
NULL
END AS FRGN_CRNCY_AMT,
S_MDF_ALLOC.MDF_ID AS FUND_ID,
S_SRC_PAYMENT.X_SAP_DOC_TEXT AS PYMT_SAP_DESC,
S_SRC_PAYMENT.X_SAP_DOC_TYPE AS SAP_DOC_TYPE,
-- APPRV_DT is extracted from S_SRC_PAYMENT.X_PYMNT_APPRD_DT instead of S_AUDIT_ITEM.OPERATION_DT
DECODE(S_SRC_PAYMENT.PAR_PAYMENT_ID,
NULL,
S_SRC_PAYMENT.X_PYMNT_APPRD_DT,
PAR.X_PYMNT_APPRD_DT) AS APPRV_DATE,
DECODE(S_SRC_PAYMENT.PAR_PAYMENT_ID,
NULL,
S_SRC_PAYMENT.X_PYMT_POSTING_DT,
PAR.X_PYMT_POSTING_DT) AS TRSRY_PYMT_DATE,
DECODE(S_SRC_PAYMENT.PAR_PAYMENT_ID,
NULL,
S_SRC_PAYMENT.X_PYMT_NUMBER,
PAR.X_PYMT_NUMBER) AS TRSRY_PYMT_NUM,
--Added by Gary on 2010/08/13 for R11 B004 start
DECODE(S_SRC_PAYMENT.PAR_PAYMENT_ID,
NULL,
S_SRC_PAYMENT.X_INVOICE_SCAN_DT,
PAR.X_INVOICE_SCAN_DT) AS INVC_SCAN_DATE,
--Added by Gary on 2010/08/13 for R11 B004 end
--Added and modified by Gary on 2010/08/13 for Optima11 B009(CR136) start
DECODE(S_SRC_PAYMENT.PAR_PAYMENT_ID,
NULL,
S_SRC_PAYMENT.X_SAP_DESC_TEXT,
PAR.X_SAP_DESC_TEXT) AS HDR_DESC,
S_SRC_PAYMENT.X_SAP_DESC_TEXT AS LINE_ITEM_DESC,
--Added and modified by Gary on 2010/08/13 for Optima11 B009(CR136) end
--Added by Gary on 2010/08/13 for R11 B020 start
S_SRC_PAYMENT.X_FLG_ATTRIB_02 AS ADVNC_IND,
--Added by Gary on 2010/08/13 for R11 B020 end
--modified by Gary for issue Siebel ID CN10803060
DECODE(S_SRC_PAYMENT.PAR_PAYMENT_ID,
NULL,
S_SRC_PAYMENT.X_CG_APPRD_PYMT_AMT,
PAR.X_CG_APPRD_PYMT_AMT) AS TRSRY_PYMT_AMT,
--S_SRC_PAYMENT.X_CG_APPRD_PYMT_AMT AS TRSRY_PYMT_AMT,
-- Add by Bean, Optima10, begin
DECODE(S_SRC_PAYMENT.PAR_PAYMENT_ID,
NULL,
S_SRC_PAYMENT.X_FIRST_REQUEST_DT,
NVL(S_SRC_PAYMENT.X_FIRST_REQUEST_DT,
PAR.X_FIRST_REQUEST_DT)) AS FIRST_APPRV_REQST_DATE,
DECODE(S_SRC_PAYMENT.PAR_PAYMENT_ID,
NULL,
S_SRC_PAYMENT.X_PYMNT_APPRD_DT,
NVL(S_SRC_PAYMENT.X_PYMNT_APPRD_DT,
PAR.X_PYMNT_APPRD_DT)) AS LATST_APPRV_REQST_DATE,
-- Add by Bean, Optima10, end
--Add by Martin, Optima10, CR605
S_SRC_PAYMENT.X_CUST_PAYMENT_NUM AS X_CUST_PAYMENT_NUM
FROM S_SRC_PAYMENT,
S_ORG_EXT,
-- Revised the SQL for performance tunning
-- by Luke 12-21-2010 Begin
(SELECT ROW_ID, PAR_SRC_ID, SUB_TYPE, SRC_CD_FMT_ID, PERIOD_ID, NAME
FROM S_SRC
WHERE SUB_TYPE = 'PG_TFMAAI_PROMOTION_ACTIVITY') S_SRC,
S_MDF_ALLOC,
S_SRC_PAYMENT PAR,
(SELECT ROW_ID, PAR_SRC_ID, SUB_TYPE, SRC_CD_FMT_ID, PERIOD_ID, NAME, X_OWNED_BY
FROM S_SRC
WHERE SUB_TYPE = 'PLAN_ACCOUNT_PROMOTION') S_SRC_PAR,
S_MDF,
/*(SELECT /*+FIRST_ROWS(1) */
S_SRC.ROW_ID, MIN(X_OWNED_BY) X_OWNED_BY
FROM S_SRC
GROUP BY S_SRC.ROW_ID) LKP1,*/
-- Revised the SQL for performance tunning
-- by Luke 12-21-2010 End
(SELECT D.ROW_ID, MIN(D.PAYMENT_REQ_NUM) PAYMENT_REQ_NUM
FROM S_SRC_PAYMENT D
GROUP BY D.ROW_ID) LKP2,
(SELECT P.CG_CREDIT_MEMO_ID,
SUM(P.CG_TRADE_FUND_TOT) CG_TRADE_FUND_TOT
FROM S_SRC_PAYMENT P
GROUP BY P.CG_CREDIT_MEMO_ID) LKP3,
OPT_BUS_UNIT_PLC PLC,
S_BU BU
WHERE S_MDF_ALLOC.PROMO_ID = S_SRC.ROW_ID(+)
AND S_SRC.PAR_SRC_ID = S_SRC_PAR.ROW_ID(+)
AND S_SRC_PAYMENT.ACCNT_ID = S_ORG_EXT.ROW_ID(+)
AND S_SRC_PAYMENT.CG_MDF_ALLOC_ID = S_MDF_ALLOC.ROW_ID(+)
AND S_SRC_PAYMENT.PAR_PAYMENT_ID = PAR.ROW_ID(+)
--AND S_SRC_PAYMENT.X_SRC_ID = LKP1.ROW_ID(+)
AND S_SRC_PAYMENT.CG_CREDIT_MEMO_ID = LKP2.ROW_ID(+)
AND S_SRC_PAYMENT.ROW_ID = LKP3.CG_CREDIT_MEMO_ID(+)
AND S_ORG_EXT.BU_ID = BU.ROW_ID(+)
AND BU.NAME = PLC.BUS_UNIT_NAME(+)
AND S_MDF_ALLOC.MDF_ID = S_MDF.ROW_ID(+)) P,
(SELECT BU_ID, NAME, TYPE, SUB_TYPE, VAL
FROM S_LST_OF_VAL
WHERE TYPE = 'PG_TFM_PAYMT_METHOD') LOV,
(SELECT BU_ID, TYPE, NAME, VAL, X_FILTER_VAL
FROM S_LST_OF_VAL
WHERE TYPE = 'PG_TFMIB_FI_DOC_PAY_MEANS') LOV_INVOICE,
(SELECT BU.ROW_ID BU_ID, LOV.TYPE, LOV.NAME, VAL, X_FILTER_VAL
FROM S_LST_OF_VAL LOV, S_BU BU
WHERE LOV.TYPE = 'COUNTRY_CODE'
AND LOV.NAME = BU.NAME) LOV_ACTL,
(SELECT BU_ID, TYPE, NAME, VAL, X_FILTER_VAL
FROM S_LST_OF_VAL
WHERE TYPE = 'PG_TFMIB_FI_DOC_PAY_MEANS') LOV_ACTL2,
--Add by Vincent for CR3094
OPT_BUS_UNIT_ESI BU,
OPT_PERD_ESI PERD,
CAL_MASTR_DIM C,
OPT_FUND_ESI FUND,
OPT_PRMTN_ESI PRM,
CAL_MASTR_DIM MAS,
OPT_ACCT_ESI ACC,
OPT_ACCT_ESI A1,
OPT_ACCT_ESI A2,
OPT_PROD_ESI PROD,
-- OPT_PYMT_FCT PYMT, --- remove it since we find OPT_PYMT_SKID is not used in future processing
OPT_ACTVY_ESI ACT,
OPT_USER_LKP U1,
OPT_USER_LKP U2,
OPT_USER_LKP U3,
OPT_USER_LKP U4,
OPT_CNTCT_LKP C1,
OPT_CNTCT_LKP C2,
OPT_CNTCT_LKP C3,
OPT_CNTCT_LKP C4
WHERE P.PYMT_TYPE_CODE = LOV.SUB_TYPE(+)
AND P.PYMT_METHD_DESC = LOV.NAME(+)
AND P.BUS_UNIT_ID = LOV.BU_ID(+)
AND P.BUS_UNIT_ID = LOV_INVOICE.BU_ID(+)
AND P.PYMT_METHD_DESC = LOV_INVOICE.NAME(+)
AND P.SAP_DOC_TYPE = LOV_INVOICE.VAL(+)
AND P.BUS_UNIT_ID = BU.BUS_UNIT_ID(+)
AND P.PERD_ID = PERD.PERD_ID(+)
AND PERD.START_DATE = C.DAY_DATE(+)
AND P.FUND_ID = FUND.FUND_ID(+)
AND P.PRMTN_ID = PRM.PRMTN_ID(+)
AND P.POSTG_DATE = MAS.DAY_DATE(+)
AND P.PAYT_ACCT_SKID = ACC.ACCT_ID(+)
AND P.FUND_ACCT_ID = A1.ACCT_ID(+)
AND P.ACCT_ID = A2.ACCT_ID(+)
AND P.PROD_ID = PROD.PROD_ID(+)
-- AND P.MDF_ALLOC_ID = PYMT.OPT_PYMT_ID(+)
AND P.ACTVY_ID = ACT.ACTVY_ID(+)
AND P.CREAT_BY_ID = U1.USER_ID(+)
AND P.OWND_BY_ID = U2.USER_ID(+)
AND P.LAST_UPDT_BY_ID = U3.USER_ID(+)
AND P.APPRV_NAME_ID = U4.USER_ID(+)
AND P.CREAT_BY_ID = C1.CNTCT_ID(+)
AND P.OWND_BY_ID = C2.CNTCT_ID(+)
AND P.LAST_UPDT_BY_ID = C3.CNTCT_ID(+)
AND P.APPRV_NAME_ID = C4.CNTCT_ID(+)
AND P.BUS_UNIT_ID = LOV_ACTL.BU_ID(+)
AND RTRIM(SUBSTR(P.SBL_ID, 1, 2)) = LOV_ACTL.VAL(+)
AND P.BUS_UNIT_ID = LOV_ACTL2.BU_ID(+)
AND P.SAP_DOC_TYPE = LOV_ACTL2.VAL(+);

我们再看执行计划

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Plan hash value: 3551243059

--------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | Pstart| Pstop |
--------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2424K| 21G| | 2121K (2)| 01:50:33 | | |
|* 1 | HASH JOIN RIGHT OUTER | | 2424K| 21G| | 2121K (2)| 01:50:33 | | |
| 2 | VIEW | index$_join$_031 | 37010 | 469K| | 120 (8)| 00:00:01 | | |
|* 3 | HASH JOIN | | | | | | | | |
| 4 | INDEX FAST FULL SCAN | OPT_CAL_MASTR_DIM_PK | 37010 | 469K| | 53 (6)| 00:00:01 | | |
| 5 | INDEX FAST FULL SCAN | OPT_CAL_MASTR_DIM_NX1 | 37010 | 469K| | 64 (5)| 00:00:01 | | |
|* 6 | HASH JOIN RIGHT OUTER | | 2388K| 21G| | 2120K (2)| 01:50:33 | | |
| 7 | TABLE ACCESS FULL | OPT_CAL_MASTR_DIM | 37010 | 650K| | 767 (5)| 00:00:03 | | |
|* 8 | HASH JOIN RIGHT OUTER | | 2388K| 20G| | 2120K (2)| 01:50:30 | | |
| 9 | TABLE ACCESS FULL | OPT_PERD_ESI | 2724 | 46308 | | 38 (3)| 00:00:01 | | |
|* 10 | HASH JOIN RIGHT OUTER | | 2388K| 20G| 5680K| 2119K (2)| 01:50:30 | | |
| 11 | VIEW | index$_join$_030 | 215K| 3150K| | 968 (7)| 00:00:04 | | |
|* 12 | HASH JOIN | | | | | | | | |
| 13 | INDEX FAST FULL SCAN | OPT_PRMTN_ESI_NX1 | 215K| 3150K| | 350 (5)| 00:00:02 | | |
| 14 | INDEX FAST FULL SCAN | OPT_PRMTN_ESI_PK | 215K| 3150K| | 412 (5)| 00:00:02 | | |
|* 15 | HASH JOIN RIGHT OUTER | | 2388K| 20G| | 1796K (2)| 01:33:39 | | |
| 16 | PARTITION LIST ALL | | 2420 | 55660 | | 1099 (2)| 00:00:04 | 1 | 16 |
| 17 | TABLE ACCESS FULL | OPT_CNTCT_LKP | 2420 | 55660 | | 1099 (2)| 00:00:04 | 1 | 16 |
|* 18 | HASH JOIN RIGHT OUTER | | 2388K| 20G| | 1795K (2)| 01:33:36 | | |
| 19 | PARTITION LIST ALL | | 2420 | 55660 | | 1099 (2)| 00:00:04 | 1 | 16 |
| 20 | TABLE ACCESS FULL | OPT_CNTCT_LKP | 2420 | 55660 | | 1099 (2)| 00:00:04 | 1 | 16 |
|* 21 | HASH JOIN RIGHT OUTER | | 2388K| 20G| | 1794K (2)| 01:33:32 | | |
| 22 | TABLE ACCESS FULL | OPT_USER_LKP | 2410 | 40970 | | 38 (3)| 00:00:01 | | |
|* 23 | HASH JOIN RIGHT OUTER | | 2388K| 20G| | 1794K (2)| 01:33:32 | | |
| 24 | TABLE ACCESS FULL | OPT_USER_LKP | 2410 | 40970 | | 38 (3)| 00:00:01 | | |
|* 25 | HASH JOIN RIGHT OUTER | | 2388K| 20G| 20M| 1794K (2)| 01:33:31 | | |
| 26 | TABLE ACCESS FULL | OPT_ACCT_ESI | 347K| 16M| | 2562 (3)| 00:00:09 | | |
|* 27 | HASH JOIN RIGHT OUTER | | 2388K| 20G| | 1473K (2)| 01:16:49 | | |
| 28 | TABLE ACCESS FULL | OPT_USER_LKP | 2410 | 40970 | | 38 (3)| 00:00:01 | | |
|* 29 | HASH JOIN RIGHT OUTER | | 2388K| 20G| | 1473K (2)| 01:16:49 | | |
| 30 | TABLE ACCESS FULL | OPT_USER_LKP | 2410 | 40970 | | 38 (3)| 00:00:01 | | |
|* 31 | HASH JOIN RIGHT OUTER | | 2388K| 20G| | 1473K (2)| 01:16:49 | | |
| 32 | PARTITION LIST ALL | | 2420 | 55660 | | 1099 (2)| 00:00:04 | 1 | 16 |
| 33 | TABLE ACCESS FULL | OPT_CNTCT_LKP | 2420 | 55660 | | 1099 (2)| 00:00:04 | 1 | 16 |
|* 34 | HASH JOIN RIGHT OUTER | | 2388K| 20G| | 1472K (2)| 01:16:45 | | |
| 35 | PARTITION LIST ALL | | 2420 | 55660 | | 1099 (2)| 00:00:04 | 1 | 16 |
| 36 | TABLE ACCESS FULL | OPT_CNTCT_LKP | 2420 | 55660 | | 1099 (2)| 00:00:04 | 1 | 16 |
|* 37 | HASH JOIN RIGHT OUTER | | 2388K| 20G| 4752K| 1471K (2)| 01:16:41 | | |
| 38 | TABLE ACCESS FULL | OPT_FUND_ESI | 179K| 2632K| | 1267 (2)| 00:00:04 | | |
|* 39 | HASH JOIN RIGHT OUTER | | 2388K| 20G| 9248K| 1155K (2)| 01:00:14 | | |
| 40 | TABLE ACCESS FULL | OPT_ACTVY_ESI | 350K| 5136K| | 3143 (2)| 00:00:10 | | |
|* 41 | HASH JOIN RIGHT OUTER | | 2388K| 20G| 20M| 838K (2)| 00:43:43 | | |
| 42 | TABLE ACCESS FULL | OPT_ACCT_ESI | 347K| 16M| | 2562 (3)| 00:00:09 | | |
|* 43 | HASH JOIN RIGHT OUTER | | 2388K| 20G| | 523K (3)| 00:27:18 | | |
| 44 | TABLE ACCESS FULL | OPT_BUS_UNIT_ESI | 14 | 196 | | 37 (0)| 00:00:01 | | |
|* 45 | HASH JOIN RIGHT OUTER | | 2388K| 20G| 9M| 523K (3)| 00:27:18 | | |
| 46 | TABLE ACCESS FULL | OPT_ACCT_ESI | 347K| 6100K| | 2545 (2)| 00:00:08 | | |
|* 47 | HASH JOIN RIGHT OUTER | | 2388K| 20G| | 210K (5)| 00:10:57 | | |
| 48 | VIEW | | 12 | 1668 | | 75 (7)| 00:00:01 | | |
|* 49 | HASH JOIN | | 12 | 756 | | 75 (7)| 00:00:01 | | |
|* 50 | TABLE ACCESS FULL | S_LST_OF_VAL_PLC | 12 | 540 | | 70 (6)| 00:00:01 | | |
| 51 | TABLE ACCESS FULL | S_BU_PLC | 16 | 288 | | 4 (0)| 00:00:01 | | |
|* 52 | HASH JOIN RIGHT OUTER | | 2388K| 19G| | 209K (5)| 00:10:57 | | |
|* 53 | TABLE ACCESS FULL | S_LST_OF_VAL_PLC | 28 | 1008 | | 71 (8)| 00:00:01 | | |
|* 54 | HASH JOIN RIGHT OUTER | | 2388K| 19G| | 209K (5)| 00:10:56 | | |
|* 55 | TABLE ACCESS FULL | S_LST_OF_VAL_PLC | 28 | 1316 | | 71 (8)| 00:00:01 | | |
|* 56 | HASH JOIN RIGHT OUTER | | 2388K| 19G| | 209K (5)| 00:10:56 | | |
|* 57 | TABLE ACCESS FULL | S_LST_OF_VAL_PLC | 1 | 50 | | 71 (8)| 00:00:01 | | |
| 58 | NESTED LOOPS OUTER | | 2388K| 19G| | 209K (5)| 00:10:55 | | |
| 59 | VIEW | | 2388K| 19G| | 209K (5)| 00:10:55 | | |
|* 60 | HASH JOIN RIGHT OUTER | | 2388K| 1984M| | 209K (5)| 00:10:55 | | |
| 61 | TABLE ACCESS FULL | OPT_BUS_UNIT_PLC | 15 | 150 | | 19 (0)| 00:00:01 | | |
|* 62 | HASH JOIN RIGHT OUTER | | 2388K| 1961M| | 209K (5)| 00:10:55 | | |
| 63 | TABLE ACCESS FULL | S_BU_PLC | 16 | 288 | | 4 (0)| 00:00:01 | | |
|* 64 | HASH JOIN RIGHT OUTER | | 2388K| 1920M| 341M| 209K (5)| 00:10:55 | | |
| 65 | VIEW | | 2370K| 314M| | 26007 (5)| 00:01:22 | | |
| 66 | HASH GROUP BY | | 2370K| 76M| 100M| 26007 (5)| 00:01:22 | | |
| 67 | TABLE ACCESS FULL | S_SRC_PAYMENT_PLC | 2388K| 77M| | 14192 (3)| 00:00:45 | | |
|* 68 | HASH JOIN RIGHT OUTER | | 2388K| 1603M| 284M| 153K (5)| 00:08:00 | | |
| 69 | TABLE ACCESS FULL | S_SRC_PAYMENT_PLC | 2388K| 257M| | 15076 (9)| 00:00:48 | | |
|* 70 | HASH JOIN RIGHT OUTER | | 2388K| 1346M| 12M| 113K (5)| 00:05:54 | | |
| 71 | PARTITION LIST SINGLE | | 211K| 10M| | 1767 (4)| 00:00:06 | KEY | KEY |
| 72 | TABLE ACCESS FULL | S_SRC_PLC | 211K| 10M| | 1767 (4)| 00:00:06 | 1 | 1 |
|* 73 | HASH JOIN RIGHT OUTER | | 2388K| 1232M| 47M| 92130 (5)| 00:04:49 | | |
| 74 | PARTITION LIST SINGLE | | 349K| 43M| | 2759 (6)| 00:00:09 | KEY | KEY |
| 75 | TABLE ACCESS FULL | S_SRC_PLC | 349K| 43M| | 2759 (6)| 00:00:09 | 2 | 2 |
|* 76 | HASH JOIN RIGHT OUTER | | 2388K| 934M| 11M| 74057 (6)| 00:03:52 | | |
| 77 | TABLE ACCESS FULL | S_ORG_EXT_PLC | 349K| 7166K| | 1560 (6)| 00:00:05 | | |
|* 78 | HASH JOIN RIGHT OUTER | | 2388K| 886M| 10M| 58453 (7)| 00:03:03 | | |
| 79 | TABLE ACCESS FULL | S_MDF_PLC | 342K| 6363K| | 1537 (3)| 00:00:05 | | |
|* 80 | HASH JOIN RIGHT OUTER | | 2388K| 842M| 16M| 43534 (8)| 00:02:17 | | |
| 81 | TABLE ACCESS FULL | S_MDF_ALLOC_PLC | 347K| 12M| | 784 (5)| 00:00:03 | | |
|* 82 | HASH JOIN RIGHT OUTER| | 2388K| 758M| | 30542 (10)| 00:01:36 | | |
| 83 | VIEW | | 300 | 18000 | | 15363 (11)| 00:00:49 | | |
| 84 | HASH GROUP BY | | 300 | 2100 | | 15363 (11)| 00:00:49 | | |
| 85 | TABLE ACCESS FULL | S_SRC_PAYMENT_PLC | 2388K| 15M| | 14670 (6)| 00:00:46 | | |
| 86 | TABLE ACCESS FULL | S_SRC_PAYMENT_PLC | 2388K| 621M| | 15105 (9)| 00:00:48 | | |
| 87 | TABLE ACCESS BY INDEX ROWID | OPT_PROD_ESI | 1 | 17 | | 1 (0)| 00:00:01 | | |
|* 88 | INDEX RANGE SCAN | OPT_PROD_ESI_NX3 | 1 | | | 1 (0)| 00:00:01 | | |
--------------------------------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - access("P"."POSTG_DATE"="MAS"."DAY_DATE"(+))
3 - access(ROWID=ROWID)
6 - access("PERD"."START_DATE"="C"."DAY_DATE"(+))
8 - access("P"."PERD_ID"="PERD"."PERD_ID"(+))
10 - access("P"."PRMTN_ID"="PRM"."PRMTN_ID"(+))
12 - access(ROWID=ROWID)
15 - access("P"."OWND_BY_ID"="C2"."CNTCT_ID"(+))
18 - access("P"."APPRV_NAME_ID"="C4"."CNTCT_ID"(+))
21 - access("P"."OWND_BY_ID"="U2"."USER_ID"(+))
23 - access("P"."APPRV_NAME_ID"="U4"."USER_ID"(+))
25 - access("P"."ACCT_ID"="A2"."ACCT_ID"(+))
27 - access("P"."CREAT_BY_ID"="U1"."USER_ID"(+))
29 - access("P"."LAST_UPDT_BY_ID"="U3"."USER_ID"(+))
31 - access("P"."CREAT_BY_ID"="C1"."CNTCT_ID"(+))
34 - access("P"."LAST_UPDT_BY_ID"="C3"."CNTCT_ID"(+))
37 - access("P"."FUND_ID"="FUND"."FUND_ID"(+))
39 - access("P"."ACTVY_ID"="ACT"."ACTVY_ID"(+))
41 - access("P"."PAYT_ACCT_SKID"="ACC"."ACCT_ID"(+))
43 - access("P"."BUS_UNIT_ID"="BU"."BUS_UNIT_ID"(+))
45 - access("P"."FUND_ACCT_ID"="A1"."ACCT_ID"(+))
47 - access("P"."BUS_UNIT_ID"="LOV_ACTL"."BU_ID"(+) AND "LOV_ACTL"."VAL"(+)=RTRIM(SUBSTR("P"."SBL_ID",1,2)))
49 - access("LOV"."NAME"="BU"."NAME")
50 - filter("LOV"."TYPE"='COUNTRY_CODE')
52 - access("P"."BUS_UNIT_ID"="BU_ID"(+) AND "P"."SAP_DOC_TYPE"="VAL"(+))
53 - filter("BU_ID"(+) IS NOT NULL AND "TYPE"(+)='PG_TFMIB_FI_DOC_PAY_MEANS')
54 - access("P"."BUS_UNIT_ID"="BU_ID"(+) AND "P"."PYMT_METHD_DESC"="NAME"(+) AND "P"."SAP_DOC_TYPE"="VAL"(+))
55 - filter("BU_ID"(+) IS NOT NULL AND "TYPE"(+)='PG_TFMIB_FI_DOC_PAY_MEANS')
56 - access("P"."PYMT_TYPE_CODE"="SUB_TYPE"(+) AND "P"."PYMT_METHD_DESC"="NAME"(+) AND "P"."BUS_UNIT_ID"="BU_ID"(+))
57 - filter("BU_ID"(+) IS NOT NULL AND "SUB_TYPE"(+) IS NOT NULL AND "TYPE"(+)='PG_TFM_PAYMT_METHOD')
60 - access("BU"."NAME"="PLC"."BUS_UNIT_NAME"(+))
62 - access("S_ORG_EXT"."BU_ID"="BU"."ROW_ID"(+))
64 - access("S_SRC_PAYMENT"."CG_CREDIT_MEMO_ID"="LKP2"."ROW_ID"(+))
68 - access("S_SRC_PAYMENT"."PAR_PAYMENT_ID"="PAR"."ROW_ID"(+))
70 - access("PAR_SRC_ID"="ROW_ID"(+))
73 - access("S_MDF_ALLOC"."PROMO_ID"="ROW_ID"(+))
76 - access("S_SRC_PAYMENT"."ACCNT_ID"="S_ORG_EXT"."ROW_ID"(+))
78 - access("S_MDF_ALLOC"."MDF_ID"="S_MDF"."ROW_ID"(+))
80 - access("S_SRC_PAYMENT"."CG_MDF_ALLOC_ID"="S_MDF_ALLOC"."ROW_ID"(+))
82 - access("S_SRC_PAYMENT"."ROW_ID"="LKP3"."CG_CREDIT_MEMO_ID"(+))
88 - access("P"."PROD_ID"="PROD"."PROD_ID"(+))

139 rows selected.

从执行计划中我们可以看出对表S_SRC_PLC 的访问由全分区扫描

| 25 | PARTITION LIST ALL | | 66M| 4358M| | 316K (9)| 00:16:29 | 1 | 8 |
| 26 | TABLE ACCESS FULL | S_SRC_PLC | 66M| 4358M| | 316K (9)| 00:16:29 | 1 | 8 |

变成了单分区全扫描，数据量由6千万降低到30多万

| 74 | PARTITION LIST SINGLE | | 349K| 43M| | 2759 (6)| 00:00:09 | KEY | KEY |
| 75 | TABLE ACCESS FULL | S_SRC_PLC

性能直线上升，最终SQL能在10分钟以内完成。

总结：SQL调优，方法多种多样。对SQL进行优化一定要熟悉业务，如果DBA不熟悉业务，我们可以利用我们DBA的知识，给开发人员指出SQL的性能瓶颈，让熟悉业务的开发人员根据我们的建议去更改代码，从而达到优化目的，这也是SQL调优。

您可能感兴趣的与本文相关的镜像