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 |
------------------------------------------------------------------------------------------------------------

語句消耗用時 "Elapsed: 00:00:06.97"

非常明顯， TABLE ACCESS STORAGE FULL FIRST ROWS 變為了 INDEX RANGE SCAN ，處理的資料範圍小了，自然就執行快了很多
所以這個案例中，優化的根本在於“縮小範圍”

案例二：
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')

又是一個很簡單的SQL，同樣是X3 1/4，執行了1354s

怎麼優化呢？並行？索引？
其實這是個中間處理過程表，做個分割槽即可
CREATE TABLE "MID_TEST"."APP_RINS_REPOLICY_CLM_TRANS1"
( "POLICYNO" VARCHAR2(22),
"REPOLICYNO" VARCHAR2(22),
"BUSIBREAKAMT" NUMBER(14,2),
"REAMOUNT" NUMBER(14,2),
"REPREMIUM" NUMBER(14,2),
"SPE_EXC" VARCHAR2(500),
"DATA_TYPE" VARCHAR2(1)
)
partition by list (DATA_TYPE)
(PARTITION DATA_TYPE1 VALUES ('1') ,
PARTITION DATA_TYPE2 VALUES ('2') ,
PARTITION DATA_TYPE3 VALUES ('3') ,
PARTITION DATA_TYPE_OTHER VALUES (DEFAULT)
)
TABLESPACE "DW_TB";

更新後的執行計劃清涼多了
--------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
--------------------------------------------------------------------------------------------------------------------------------------
| 0 | UPDATE STATEMENT | | 29157 | 626K| 437K (7)| 01:27:32 | | |
| 1 | UPDATE | APP_RINS_REPOLICY_CLAIM | | | | | | |
| 2 | TABLE ACCESS STORAGE FULL | APP_RINS_REPOLICY_CLAIM | 29157 | 626K| 292 (1)| 00:00:04 | | |
| 3 | PARTITION LIST SINGLE | | 1 | 28 | 14 (0)| 00:00:01 | KEY | KEY |
|* 4 | TABLE ACCESS STORAGE FULL FIRST ROWS| APP_RINS_REPOLICY_CLM_TRANS | 1 | 28 | 14 (0)| 00:00:01 | 1 | 1 |
--------------------------------------------------------------------------------------------------------------------------------------

執行時間變成誇張的 " Elapsed: 00:00:00.91 "
同樣的，在這個案例中，資料處理的範圍小了，執行就快了很多。這個Case表現出來的，還是優化的根本在於“縮小範圍”

其實回想Exadata的幾個特性，謂詞過濾、儲存索引等等，本質上也是幫助執行時縮小範圍，這個跟傳統的優化手段並不矛盾，具體選誰是要具體案例具體分析的
千萬不要單純的追逐Exadata某些特性的使用，而忘記優化的根本目的
回到最開始的那個問題，“是不是Exadata上就不需要索引了？ ” ，我的答案是：
不要為了使用Exadata的某些特性就把別的都排斥掉，分割槽、索引、巢狀、Smart SCAN 、Storage Index ...一切的優化都有其優勢和劣勢，任何時候都不要把優化的手段當成優化的目的，Exadata優化也要包容傳統的思維

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

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

相關文章