0 积分	1 好友	4 主题

发消息

[RAC性能调优] 带有not in或not exists的SQL会走HASH JOIN RIGHT ANTI执行计划怎优化

1^#

发表于 2013-4-26 09:39:04 | 查看: 4000| 回复: 2

SQL语句如下：

select count(*) as col_0_0_
  from TRANSACTION transactio0_
where transactio0_.TRANS_STATUS = :1
and transactio0_.AUDIT_TRANS = :2
and (transactio0_.ACTION_SEQ in (4, 7))
and transactio0_.AMOUNT > 0
and (transactio0_.TRANS_ID not in
   (select adjustrequ1_.TRANSACTIONCODE
         from ADJUSTREQUSITION adjustrequ1_
      where adjustrequ1_.APPLYSTATE <> 5
         and adjustrequ1_.APPLYSTATE <> 4
         and (adjustrequ1_.TRANSACTIONCODE is not null)))

执行计划如下：

select * from table(dbms_xplan.display(null,null,'ADVANCED +PEEKED_BINDS'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 3156663908

--------------------------------------------------------------------------------
-------------------------------

| Id  | Operation       | Name       | Rows  |
Bytes | Cost (%CPU)| Time    |

--------------------------------------------------------------------------------
-------------------------------

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT    |       |    1 |
46 |  9881 (1)| 00:01:59 |

| 1 |  SORT AGGREGATE       |       |    1 |
46 |    |    |

|*  2 | HASH JOIN RIGHT ANTI    |       | 242 |
11132 |  9881 (1)| 00:01:59 |

|*  3 | TABLE ACCESS FULL    | ADJUSTREQUSITION    | 908 |
16344 |    5 (0)| 00:00:01 |

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

|*  4 | TABLE ACCESS BY INDEX ROWID| TRANSACTION       |  1132 |
31696 |  9875 (1)| 00:01:59 |

|*  5 |    INDEX RANGE SCAN    | IDX_TRANSACTION__TRANS_STATUS | 68493 |
   | 160 (2)| 00:00:02 |

--------------------------------------------------------------------------------
-------------------------------

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

1 - SEL$5DA710D3
3 - SEL$5DA710D3 / ADJUSTREQU1_@SEL$2
4 - SEL$5DA710D3 / TRANSACTIO0_@SEL$1
5 - SEL$5DA710D3 / TRANSACTIO0_@SEL$1

Outline Data
-------------

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
  /*+
   BEGIN_OUTLINE_DATA
   SWAP_JOIN_INPUTS(@"SEL$5DA710D3" "ADJUSTREQU1_"@"SEL$2")
   USE_HASH(@"SEL$5DA710D3" "ADJUSTREQU1_"@"SEL$2")
   LEADING(@"SEL$5DA710D3" "TRANSACTIO0_"@"SEL$1" "ADJUSTREQU1_"@"SEL$2")
   FULL(@"SEL$5DA710D3" "ADJUSTREQU1_"@"SEL$2")
   INDEX(@"SEL$5DA710D3" "TRANSACTIO0_"@"SEL$1" ("TRANSACTION"."TRANS_STATUS"
))

   OUTLINE(@"SEL$2")
   OUTLINE(@"SEL$1")

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
   UNNEST(@"SEL$2")
   OUTLINE_LEAF(@"SEL$5DA710D3")
   FIRST_ROWS(100)
   OPTIMIZER_FEATURES_ENABLE('10.2.0.1')
   IGNORE_OPTIM_EMBEDDED_HINTS
   END_OUTLINE_DATA
  */

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

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
2 - access("TRANSACTIO0_"."TRANS_ID"="ADJUSTREQU1_"."TRANSACTIONCODE")
3 - filter(TO_NUMBER("ADJUSTREQU1_"."APPLYSTATE")<>4 AND TO_NUMBER("ADJUSTREQ
U1_"."APPLYSTATE")<>5)

4 - filter("TRANSACTIO0_"."AUDIT_TRANS"=:2 AND ("TRANSACTIO0_"."ACTION_SEQ"=4
OR

   "TRANSACTIO0_"."ACTION_SEQ"=7) AND "TRANSACTIO0_"."AMOUNT">0)
5 - access("TRANSACTIO0_"."TRANS_STATUS"=:1)

Column Projection Information (identified by operation id):

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

1 - (#keys=0) COUNT(*)[22]
2 - (#keys=1)
3 - "ADJUSTREQU1_"."TRANSACTIONCODE"[VARCHAR2,32]
4 - "TRANSACTIO0_"."TRANS_ID"[VARCHAR2,32]
5 - "TRANSACTIO0_".ROWID[ROWID,10]

58 rows selected.

分享0