Exadata優化同樣要包容傳統的思維 - 記Exadata優化案例二則

Exadata帶來很多炫目的特性，smart scan / storage index / HCC ...
有不少使用者曾經問我，是不是Exadata上就不需要索引了？  呵呵，每當這時候，我都想起那句話 :"不要迷戀哥，哥只是個傳說..."

案例一： 
X3 1/4配系統，有語句如下
UPDATE MID_TEST.APP_RINS_REPOLICY_POLICY ARRP SET ARRP.SPE_EXC = (SELECT
ARRPT.SPE_EXC FROM MID_TEST.APP_RINS_REPOLICY_PLY_TRANS ARRPT WHERE
ARRP.POLICYNO = ARRPT.POLICYNO AND ARRPT.DATA_TYPE = '1')

居然執行了42分鐘
col min(sample_time) for a40
col max(sample_time) for a40
select min(sample_time),max(sample_time),SESSION_ID,SESSION_SERIAL# from v$active_session_history where sql_id='9tatf699z5t46' group by SESSION_ID,SESSION_SERIAL# order by 1;

MIN(SAMPLE_TIME)                         MAX(SAMPLE_TIME)                         SESSION_ID SESSION_SERIAL#
---------------------------------------- ---------------------------------------- ---------- ---------------
26-NOV-13 04.04.57.134 PM                26-NOV-13 04.47.00.456 PM                      1002            1687

兩張表也不是很大
SQL> select count(*) from mid_test.APP_RINS_REPOLICY_PLY_TRANS;
  COUNT(*)
----------
   3752031
  
SQL> select count(*) from mid_test.APP_RINS_REPOLICY_POLICY;
  COUNT(*)
----------
    223597


執行計劃是想走 smart scan的
---------------------------------------------------------------------------------------------------------------------
| Id  | Operation                             | Name                        | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------------------------------
|   0 | UPDATE STATEMENT                      |                             |       |       |  1712M(100)|          |
|   1 |  UPDATE                               | APP_RINS_REPOLICY_POLICY    |       |       |            |          |
|   2 |   TABLE ACCESS STORAGE FULL           | APP_RINS_REPOLICY_POLICY    |   223K|    39M|  1717   (1)| 00:00:21 |
|*  3 |   TABLE ACCESS STORAGE FULL FIRST ROWS| APP_RINS_REPOLICY_PLY_TRANS |     1 |    26 |  7659   (2)| 00:01:32 |
---------------------------------------------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
   1 - UPD$1
   2 - UPD$1 / ARRP@UPD$1
   3 - SEL$1 / ARRPT@SEL$1

Predicate Information (identified by operation id):
---------------------------------------------------
   3 - filter(("ARRPT"."POLICYNO"=:B1 AND "ARRPT"."DATA_TYPE"=1))

Column Projection Information (identified by operation id):
-----------------------------------------------------------
   2 - (upd=3; cmp=2) "ARRP".ROWID[ROWID,10], "ARRP"."POLICYNO"[VARCHAR2,22], "ARRP"."SPE_EXC"[VARCHAR2,500]
   3 - "ARRPT"."POLICYNO"[VARCHAR2,22], "ARRPT"."SPE_EXC"[VARCHAR2,500], "ARRPT"."DATA_TYPE"[NUMBER,22]

事件  "STORAGE FULL FIRST ROWS" 很可疑，為什麼CBO要選擇First Row？ 
過濾器也很可疑， 為什麼會過濾 ARRPT.POLICYNO=:B1 ADN ARRPT.DATA_TYPE=1
CBO在這裡嚴重智商不足:)

該怎麼辦？其實跳出Exadata的範圍，只要建個索引就能解決問題
create index mid_test.APP_RINS_REPOLICY_PLY_TRANS_1 on mid_test.APP_RINS_REPOLICY_PLY_TRANS (POLICYNO,DATA_TYPE,SPE_EXC) parallel 8 nologging;
alter index mid_test.APP_RINS_REPOLICY_PLY_TRANS_1 noparallel;
修改後語句執行計劃變為
------------------------------------------------------------------------------------------------------------
| Id  | Operation                  | Name                          | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------------------
|   0 | UPDATE STATEMENT           |                               |   223K|    39M|   896K (25)| 02:59:14 |
|   1 |  UPDATE                    | APP_RINS_REPOLICY_POLICY      |       |       |            |          |
|   2 |   TABLE ACCESS STORAGE FULL| APP_RINS_REPOLICY_POLICY      |   223K|    39M|  1717   (1)| 00:00:21 |
|*  3 |   INDEX RANGE SCAN         | APP_RINS_REPOLICY_PLY_TRANS_1 |     1 |    26 |     3   (0)| 00:00:01 |
------------------------------------------------------------------------------------------------------------

案例二：
UPDATE APP_RINS_REPOLICY_CLAIM ARRC SET ARRC.BUSIBREAKAMT = (SELECT ARRCT.BUSIBREAKAMT FROM APP_RINS_REPOLICY_CLM_TRANS ARRCT WHERE ARRC.POLICYNO = ARRCT.POLICYNO AND ARRCT.DATA_TYPE = '1')

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/78250/viewspace-1061911/，如需轉載，請註明出處，否則將追究法律責任。