ORACLE12.1.0.2 WINDOWS2008
语句的效率奇差,我怀疑是 FILTER 操作导致,但对 FILTER 的运作原理理解不深刻,未能看出是哪个环节影响了效率,
请问:1 在这个执行计划中,两个 FILTER 步骤,谁先执行,谁后执行,分析做了什么操作?数据如何流转过滤?
2 第20步的 filter 操作,是不是执行由其子操作(24->23->25->22->21)出结果后,再由其和第3步骤的结果做对比:20 - filter(LNNVL("TARR"."FENTRYID"<>:B1)) ?
3 我希望能找出 导致 FILTER 操作低效的原因?
SQL> explain plan for
2 SELECT AR.FID RECID, '' RECBILLNO, AR.FALLAMOUNTFOR, '' FCURWRITTENOFFAMOUNTFOR, '' FNRECEIPTAMOUNT,
3 CASE WHEN (AR.FALLAMOUNTFOR < '') THEN (-'' * ABS(SUM(NVL(PML.FCURWRITTENOFFAMOUNTFOR, '')))) ELSE ABS(SUM(NVL(PML.FCURWRITTENOFFAMOUNTFOR, ''))) END FCHARGEOFFAMOUNT,
4 PML.FSRCBILLNO FCHARGEOFFBILLNO
5 FROM T_AR_RECMACTHLOGENTRY PML
6 INNER JOIN T_AR_RECMacthLog PM ON (PM.FID = PML.FID AND (PM.FISACTIVATION <> '0'))
7 INNER JOIN T_AR_RECEIVABLE AR ON AR.FID = PML.FTARGETBILLID
8 WHERE (((((PML.FTARGETFROMID = 'AR_receivable' AND (PML.FSOURCEFROMID <> 'AR_receivable')) AND (PML.FSOURCEFROMID <> 'AR_RECEIVEBILL'))AND (PML.FSOURCEFROMID <> 'AR_REFUNDBILL'))
9 AND EXISTS (SELECT '' FROM T_AR_RECEIVABLEENTRY ARE
10 INNER JOIN TMPBBEF6E241F2611E78CED98BE94F FLOW ON Flow.FRECID = ARE.FENTRYID WHERE ARE.FID= AR.FID))
11 AND PML.FENTRYID NOT IN (SELECT DISTINCT TARR.FENTRYID FROM
12 (SELECT TARME.FENTRYID FROM T_AR_MATCKENTRY TARME
13 INNER JOIN T_AR_MATCK TARM ON (TARM.FID = TARME.FID ANDTARME.FMATCHTYPE = '3')) "TEMP"
14 LEFT OUTER JOIN T_AR_RECMACTHLOGENTRY TARR ON (TARR.FSRCROWID = "TEMP".FENTRYID AND TARR.FSOURCETYPE ='b9b2335770b84a3aa9b09b22767cd7e3')))
15 GROUP BY AR.FID, PML.FSRCBILLNO, AR.FALLAMOUNTFOR, AR.FWRITTENOFFSTATUS;
已解释。
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 2357001836
----------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 96 | 10378 (1)| 00:00:01 |
| 1 | HASH GROUP BY | | 1 | 96 | 10378 (1)| 00:00:01 |
|* 2 | FILTER | | | | | |
| 3 | NESTED LOOPS | | 1 | 96 | 5377 (1)| 00:00:01 |
| 4 | NESTED LOOPS | | 1 | 96 | 5377 (1)| 00:00:01 |
|* 5 | HASH JOIN | | 1 | 89 | 5376 (1)| 00:00:01 |
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 6 | NESTED LOOPS | | 1 | 16 | 4 (25)| 00:00:01 |
| 7 | NESTED LOOPS | | 1 | 16 | 4 (25)| 00:00:01 |
| 8 | VIEW | VW_SQ_1 | 1 | 5 | 3 (34)| 00:00:01 |
| 9 | HASH UNIQUE | | 1 | 14 | 3 (34)| 00:00:01 |
| 10 | NESTED LOOPS | | 1 | 14 | 2 (0)| 00:00:01 |
| 11 | NESTED LOOPS | | 1 | 14 | 2 (0)| 00:00:01 |
| 12 | INDEX FULL SCAN | IDX_BBEF6E241F2611E78CED98_1 | 1 | 4 | 1 (0)| 00:00:01 |
|* 13 | INDEX UNIQUE SCAN | PK_AR_RECEIVABLEENTRY | 1 | | 0 (0)| 00:00:01 |
| 14 | TABLE ACCESS BY INDEX ROWID| T_AR_RECEIVABLEENTRY | 1 | 10 | 1 (0)| 00:00:01 |
|* 15 | INDEX UNIQUE SCAN | PK_AR_RECEIVABLE | 1 | | 0 (0)| 00:00:01 |
| 16 | TABLE ACCESS BY INDEX ROWID | T_AR_RECEIVABLE | 1 | 11 | 1 (0)| 00:00:01 |
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|* 17 | TABLE ACCESS FULL | T_AR_RECMACTHLOGENTRY | 54913 | 3914K| 5372 (1)| 00:00:01 |
|* 18 | INDEX UNIQUE SCAN | PK_AR_RECMACTHLOG | 1 | | 0 (0)| 00:00:01 |
|* 19 | TABLE ACCESS BY INDEX ROWID | T_AR_RECMACTHLOG | 1 | 7 | 1 (0)| 00:00:01 |
|* 20 | FILTER | | | | | |
| 21 | NESTED LOOPS OUTER | | 2 | 120 | 5000 (1)| 00:00:01 |
| 22 | NESTED LOOPS | | 1 | 17 | 603 (1)| 00:00:01 |
| 23 | INDEX FAST FULL SCAN | PK_AR_MATCK | 96554 | 471K| 2 (0)| 00:00:01 |
|* 24 | TABLE ACCESS FULL | T_AR_MATCKENTRY | 1 | 12 | 601 (1)| 00:00:01 |
|* 25 | TABLE ACCESS FULL | T_AR_RECMACTHLOGENTRY | 1 | 43 | 4397 (1)| 00:00:01 |
----------------------------------------------------------------------------------------------------------------------
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter( NOT EXISTS (SELECT 0 FROM "T_AR_RECMACTHLOGENTRY" "TARR","T_AR_MATCK" "TARM","T_AR_MATCKENTRY"
"TARME" WHERE "TARM"."FID"="TARME"."FID" AND "TARME"."FMATCHTYPE"='3' AND LNNVL("TARR"."FENTRYID"<>:B1) AND
"TARR"."FSRCROWID"(+)="TARME"."FENTRYID" AND "TARR"."FSOURCETYPE"(+)='b9b2335770b84a3aa9b09b22767cd7e3'))
5 - access("AR"."FID"="PML"."FTARGETBILLID")
13 - access("FLOW"."FRECID"="ARE"."FENTRYID")
15 - access("ITEM_1"="AR"."FID")
17 - filter("PML"."FTARGETFROMID"='AR_receivable' AND "PML"."FSOURCEFROMID"<>'AR_RECEIVEBILL' AND
"PML"."FSOURCEFROMID"<>'AR_receivable' AND "PML"."FSOURCEFROMID"<>'AR_REFUNDBILL')
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
18 - access("PM"."FID"="PML"."FID")
19 - filter("PM"."FISACTIVATION"<>'0')
20 - filter(LNNVL("TARR"."FENTRYID"<>:B1))
24 - filter("TARM"."FID"="TARME"."FID" AND "TARME"."FMATCHTYPE"='3')
25 - filter("TARR"."FSRCROWID"(+)="TARME"."FENTRYID" AND
"TARR"."FSOURCETYPE"(+)='b9b2335770b84a3aa9b09b22767cd7e3')
Note
-----
- dynamic statistics used: dynamic sampling (level=2)
- 5 Sql Plan Directives used for this statement
已选择 55 行。