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

Exadata优化同样要包容传统的思维 - 记Exadata优化案例二则

最新文章