两条查询语句
sqlA: SELECT * FROM TABLE(dbms_xplan.display_awr('6cxvz42cqab9r' )) 查看awr中sqlid的执行计划
sqlB: SELECT * FROM TABLE(dbms_xplan.display_cursor('6cxvz42cqab9r' )) 查看cursor中sqlid的执行计划
两条查看的plan hash value是一致的,但是 在t_dpd_rpt这个表进行全表扫描的时候,awr中和cursor显示的不一样,明显cursor返回行太多了。为什么同是全表扫描,也没有绑定变量,会出现这种差别呢。
其中返回的结果sqlA为:
1 SQL_ID 6cxvz42cqab9r
2 --------------------
3 UPDATE T_DPD_RPT D SET D.COMMISSION = (DECODE(D.SELL_WAY, 7, (SELECT SUM(CI.MONEY) FROM
4 T_AGENCY_COMMISION_INFO CI, T_AGENCY_COMMISSION_TYPE T WHERE CI.LIST_ID = D.LIST_ID AND
5 CI.COMMISION_TYPE_ID = T.TYPE_ID AND T.PROP = 1), (SELECT SUM(PC.COMMISION) FROM T_PRODUCT_COMMISION PC
6 WHERE PC.LIST_ID = D.LIST_ID))) WHERE D.COMMISSION IS NULL
7
8 Plan hash value:2799042232
9
10 -------------------------------------------------------------------------------------------------------------------
11 | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
12 -------------------------------------------------------------------------------------------------------------------
13 | 0 | UPDATE STATEMENT | | | | 2 (100)| |
14 | 1 | UPDATE | T_DPD_RPT | | | | |
15 | 2 | TABLE ACCESS FULL | T_DPD_RPT | 1 | 29 | 2 (0)| 00:00:01 |
16 | 3 | SORT AGGREGATE | | 1 | 17 | | |
17 | 4 | NESTED LOOPS | | 3 | 51 | 22 (0)| 00:00:01 |
18 | 5 | TABLE ACCESS FULL | T_AGENCY_COMMISION_INFO | 3 | 36 | 21 (0)| 00:00:01 |
19 | 6 | TABLE ACCESS BY INDEX ROWID | T_AGENCY_COMMISSION_TYPE | 1 | 5 | 1 (0)| 00:00:01 |
20 | 7 | INDEX UNIQUE SCAN | PK_T_AGENCY_COMMISSION_TYPE | 1 | | 1 (0)| 00:00:01 |
21 | 8 | SORT AGGREGATE | | 1 | 10 | | |
22 | 9 | TABLE ACCESS BY INDEX ROWID| T_PRODUCT_COMMISION | 1 | 10 | 1 (0)| 00:00:01 |
23 | 10 | INDEX RANGE SCAN | UNI_PRODUCT_COMMISION__LIST_AG | 1 | | 1 (0)| 00:00:01 |
24 -------------------------------------------------------------------------------------------------------------------
sqlB为:
1 SQL_ID 6cxvz42cqab9r, child number 0
2 -------------------------------------
3 UPDATE T_DPD_RPT D SET D.COMMISSION = (DECODE(D.SELL_WAY, 7, (SELECT SUM(CI.MONEY) FROM
4 T_AGENCY_COMMISION_INFO CI, T_AGENCY_COMMISSION_TYPE T WHERE CI.LIST_ID = D.LIST_ID AND
5 CI.COMMISION_TYPE_ID = T.TYPE_ID AND T.PROP = 1), (SELECT SUM(PC.COMMISION) FROM T_PRODUCT_COMMISION PC
6 WHERE PC.LIST_ID = D.LIST_ID))) WHERE D.COMMISSION IS NULL
7
8 Plan hash value:2799042232
9
10 -------------------------------------------------------------------------------------------------------------------
11 | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
12 -------------------------------------------------------------------------------------------------------------------
13 | 0 | UPDATE STATEMENT | | | | 16M(100)| |
14 | 1 | UPDATE | T_DPD_RPT | | | | |
15 |* 2 | TABLE ACCESS FULL | T_DPD_RPT | 177K| 1902K| 2676 (1)| 00:00:33 |
16 | 3 | SORT AGGREGATE | | 1 | 20 | | |
17 | 4 | NESTED LOOPS | | 2 | 40 | 87 (2)| 00:00:02 |
18 |* 5 | TABLE ACCESS FULL | T_AGENCY_COMMISION_INFO | 3 | 42 | 84 (2)| 00:00:02 |
19 |* 6 | TABLE ACCESS BY INDEX ROWID | T_AGENCY_COMMISSION_TYPE | 1 | 6 | 1 (0)| 00:00:01 |
20 |* 7 | INDEX UNIQUE SCAN | PK_T_AGENCY_COMMISSION_TYPE | 1 | | 0 (0)| |
21 | 8 | SORT AGGREGATE | | 1 | 10 | | |
22 | 9 | TABLE ACCESS BY INDEX ROWID| T_PRODUCT_COMMISION | 1 | 10 | 4 (0)| 00:00:01 |
23 |* 10 | INDEX RANGE SCAN | UNI_PRODUCT_COMMISION__LIST_AG | 1 | | 3 (0)| 00:00:01 |
24 -------------------------------------------------------------------------------------------------------------------
25
26 Predicate Information (identified by operation id):
27 ---------------------------------------------------
28
29 2 - filter("D"."COMMISSION" IS NULL)
30 5 - filter("CI"."LIST_ID"=:B1)
31 6 - filter(TO_NUMBER("T"."PROP")=1)
32 7 - access("CI"."COMMISION_TYPE_ID"="T"."TYPE_ID")
33 10 - access("PC"."LIST_ID"=:B1)