第一章 適用范圍
本案例中的問題SQL發生在當前主流的ORACLE：11G環境。不排除隨著后續版本的升級可能會有一定細微的差異表現。但問題的主要現象及優化思路，是大同小異的。
本案例內容來源于客戶應用部門，急需要對某一功能進行上線。將SQL提交到我這里做審核。分析過程中總結出很多典型的優化方法和技巧，故有了此文章。
第二章 問題概述
上線功能是一系列多條SQL，本條SQL是其中相對耗時的SQL之一。
下面列出SQL語句：
select count(distinct tcc.case_id) agPolicyTwoClaimNum
from TCCP,
TCC,
TCM,
TCP,
TPL
where TCCP.case_id = tcc.case_id
and TCCP.policy_id = tcm.policy_id
and TCCP.item_id = tcp.item_id
and tcp.product_id = tpl.product_id
and (tpl.ill_rate > 0)
and tpl.period_type = '3'
and TCCP.audit_conclusion is not null
and months_between(tcc.accident_time, tcp.validate_date) <= 24
and tcc.finish_time >=
(select ADD_MONTHS(max(t.apply_date), -12)
from TCM t
where t.agent_id = tcm.agent_id)
and tcm.agent_id = '387932438';
通過文本，大家可以思考幾分鐘，這條SQL有哪些問題，可以從哪里去調整。
。。。
下面分析執行效率：
相同執行計劃，由于傳入值的不同，消耗資源存在一定差異，但總體偏慢。執行時間接近1秒、59W邏輯讀開銷。
列出執行計劃如下：
多次執行在消除了物理讀的情況下，還需要0.87秒才能完成查詢。可以看到效率是比較低的。主要耗時就發生在內層框出的兩部分。
- 兩次TCM表的索引掃描并回表；
- 關聯TCCP表并回表的部分。
這條查詢返回77行數據，得到匯總的count值。但中間卻要訪問想多多的數據量。可以理解為查詢代價是偏大的。針對上述耗時部分，需要想辦法去優化他。
第三章 問題優化分析
通過上一章節?；久鞔_了主要的耗時步驟發生在兩次TCM表和一次TCCP表的訪問過程中。思考下通過何種思路來優化。
首先TCM表是主要的過濾條件。且SQL通過該列索引訪問效率并不差。范圍掃描0.02秒。主要慢在回表部分。TCCP表現也近似。因此考慮下是否通過調整索引設計來避免回表，達到優化的目的。
考慮到當前表體積較大，索引數量比較多。且為系統中比較核心的業務表之一。因此初步想法是先不通過調整索引來嘗試優化。通過如下手段逐步優化SQL的各個耗時部分：
3.1 去掉一次表訪問：
首先SQL中兩次訪問TCM表，且主要的過濾條件都通過agent_id列。因此考慮能否去掉一次表訪問。也就是子查詢中的部分是多余的
(select ADD_MONTHS(max(t.apply_date), -12)
from TCM t
where t.agent_id = tcm.agent_id);
該段代碼的目的是為了獲取相同agent_id的最大值并減去1年的日期條件。為了實現該查詢，可以考慮在外層TCM訪問時同步獲取該1年內的日期值。
select MAX(apply_date) over(), -12)
from TCM t
where t.agent_id = '387932438'
即：在外層TCM表訪問時通過窗戶函數來同步獲得最大日期列。
調整SQL語句為如下代碼：
select count(distinct tcc.case_id) agPolicyTwoClaimNum
from TCCP,
TCC,
(select policy_id, ADD_MONTHS(MAX(apply_date) over(), -12) apply_date
from TCM t
where t.agent_id = '387932438') tcm,
TCP,
TPL
where TCCP.case_id = tcc.case_id
and TCCP.policy_id = tcm.policy_id
and TCCP.item_id = tcp.item_id
and tcp.product_id = tpl.product_id
and (tpl.ill_rate > 0)
and tpl.period_type = '3'
and TCCP.audit_conclusion is not null
and months_between(tcc.accident_time, tcp.validate_date) <= 24
and tcc.finish_time >= tcm.apply_date;
調整后執行計劃如下：
調整后，省去了一次TCM的訪問，降低了部分邏輯讀及執行時間。有一定的提升，繼續分析其余部分。
3.2 利用CTE提升回表效率：
第二部分的耗時開銷來自于TCCP表的索引回表部分。TCM與TCCP關聯后僅返回299行記錄。但回表步驟的開銷卻比較高。
僅返回299行記錄，但回表次數卻與索引訪問次數一致，達到了159K次。這里看到采用了最老式的回表方式。即：關聯一行索引列完成一次回表。
這里介紹下其余回表方式：
Table Prefetch是,索引掃描表的過程中，如果產生物理I/O，預取接下來要讀取的Block，
提前放到Buffer cache里一種功能。
Batching I/O是, 索引掃描表的過程中，如果要產生物理I/O，先積攢起來到一定量以后，一次性的讀取Block的一種功能。
兩個功能都是為了避免，每條記錄都產生不必要的I/O Call。
下面單獨測試三種不同回表方式的性能差異：
為了便于觀察差異，將驅動表建立覆蓋索引。
1.傳統回表：
select /*+ OPTIMIZER_FEATURES_ENABLE('10.2.0.3') no_nlj_prefetch(TCCP) */
count(TCCP.audit_conclusion)
from (select policy_id from TCM t where t.agent_id = '387932438') tcm,
TCCP
where TCCP.policy_id = tcm.policy_id
and TCCP.audit_conclusion is not null;
- nlj_prefetch回表：
select /*+ nlj_prefetch(TCCP) */
count(TCCP.audit_conclusion)
from (select policy_id from TCM t where t.agent_id = '387932438') tcm,
TCCP
where TCCP.policy_id = tcm.policy_id
and TCCP.audit_conclusion is not null;
- nlj_batching回表：
select /*+ nlj_batching(tccp) */
count(TCCP.audit_conclusion)
from (select policy_id from TCM t where t.agent_id = '387932438') tcm,
TCCP
where TCCP.policy_id = tcm.policy_id
and TCCP.audit_conclusion is not null;
三種回表方式的時間成本分別為：0.14秒、0.01秒、0.01秒。即后兩種回表方式都是很高效的。有效降低回表的次數。無論如何添加相應提示，均無法走出相應的回表效果。這里分析原因為關聯tccp表時，還要通過回表步驟返回其余的case_id、item_id列在與其余的表做關聯有關。因此優化器拒絕了上述預取或批量回表的方式。造成了回表這里的耗時較高。
為了利用批量回表的特性，將SQL語句繼續改寫，讓TCM與TCCP關聯后通過CTE寫法再與外層表關聯：
with TEMP as
(select /*+ materialize */
tcm.apply_date, TCCP.case_id, TCCP.item_id
from (select policy_id,
ADD_MONTHS(MAX(apply_date) over(), -12) apply_date
from TCM t
where t.agent_id = '387932438') tcm,
TCCP
where TCCP.policy_id = tcm.policy_id
and TCCP.audit_conclusion is not null)
select count(distinct tcc.case_id) agPolicyTwoClaimNum
from TCC,
TEMP,
TCP,
TPL
where TEMP.case_id = tcc.case_id
and TEMP.item_id = tcp.item_id
and tcp.product_id = tpl.product_id
and (tpl.ill_rate > 0)
and tpl.period_type = '3'
and months_between(tcc.accident_time, tcp.validate_date) <= 24
and tcc.finish_time >= TCCP.apply_date;
調整后的執行計劃：
順利采用了批量回表特性，執行時間又縮短了一部分。優化到此，主要的耗時步驟都解決了，只剩下TCM的回表問題。當前執行時間在0.5秒左右。此時與應用人員確認，是否滿足要求。
答案是否定的，要求業務環境的多條SQL平均執行時間在2秒內返回結果。而本條SQL僅是其中的一環。因此還需要進一步優化。
3.3 創建索引：
前期的想法不調整大表TCM的索引，看來是不行的。為了滿足業務響應時間，索引該建也得建啊。
但如何建索引：
AGENT_ID是過濾列、policy_id是回表列、apply_date是為了取最大值。考慮是將過濾列在前，其次是取最大值的apply_date、最后是回表列。
CREATE INDEX IDX_TCM__AGENT_AP ON TCM(AGENT_ID,apply_date,policy_id);
創建索引后，分別測試一下原始SQL和上一步驟改寫好的SQL。
原始SQL執行計劃如下：
索引建立的很合適，兩次TCM表均通過索引得到比較好的訪問效率。矛盾集中在了TCCP上面。
改寫好的SQL測試：
創建索引后看到，不論是原始SQL，還是改寫好的SQL，執行時間都很短了。說明關鍵的索引還是很有必要的。
繼續分析，是否還有優化空間？
3.4 傳入等值條件：
針對3.3中的優化效果，盡管省去了一次TCM的訪問。但由于是把TCM作為一個單獨的VIEW去訪問，在構造生成的時候，還是會消耗不小的代價的。
而原始SQL兩次索引訪問TCM的代價并不高。因此這里考慮，將TCM這部分還原回原始SQL訪問兩次的寫法：
這里看到代碼中是隱含著TCM的agent_id=t表的agent_id的。
手動傳入實際的AGENT值。索引的建立正好滿足能直接獲取到相應最大值的情況。因此期望能否利用索引的INDEX RANGE SCAN (MIN/MAX)特性。
select count(distinct tcc.case_id) agPolicyTwoClaimNum
from TCCP,
TCC,
TCM,
TCP,
TPL
where TCCP.case_id = tcc.case_id
and TCCP.policy_id = tcm.policy_id
and TCCP.item_id = tcp.item_id
and tcp.product_id = tpl.product_id
and (tpl.ill_rate > 0)
and tpl.period_type = '3'
and TCCP.audit_conclusion is not null
and months_between(tcc.accident_time, tcp.validate_date) <= 24
and tcc.finish_time >=
(select ADD_MONTHS(max(t.apply_date), -12)
from TCM t
where t.agent_id = '387932438')
and tcm.agent_id = '387932438';
調整后資源消耗及時間有了進一步的下降。
3.5 傳入條件與CTE結合：
將3.4步驟中傳入條件的寫法與CTE結合起來。
with TEMP as
(select /*+ materialize */
TCCP.case_id, TCCP.item_id
from (select policy_id from TCM t where t.agent_id = '387932438') tcm,
TCCP
where TCCP.policy_id = tcm.policy_id
and TCCP.audit_conclusion is not null)
select count(distinct tcc.case_id) agPolicyTwoClaimNum
from TCC,
TEMP,
TCP,
TPL
where TEMP.case_id = tcc.case_id
and TEMP.item_id = tcp.item_id
and tcp.product_id = tpl.product_id
and (tpl.ill_rate > 0)
and tpl.period_type = '3'
and months_between(tcc.accident_time, tcp.validate_date) <= 24
and tcc.finish_time >=
(select ADD_MONTHS(max(t.apply_date), -12)
from TCM t
where t.agent_id = '387932438');
調整后的執行計劃如下：
優化到此，主要的耗時步驟均有了較大改善。SQL執行僅需要0.11秒。與應用人員確認，已經滿足業務的時效性要求?？梢哉J為優化完成了。
但仔細想一下，如果還想進一步提升，是否還有辦法呢？
3.6 利用物化視圖：
答案是有的。觀察上面優化好的執行計劃，可以看到主要的耗時步驟還是框出的TCM與TCCP關聯產生（CTE部分）。這部分經測試傳入不同的條件，其關聯后的結果都是相對較小的(TCCP表較小)。因此只是關聯的中間結果大導致耗時較長。
且這里兩表直接關聯代碼簡單，可以建立實時刷新的物化視圖，避免訪問較大的中間結果集。且該條SQL在業務環節中不只出現一次。還有其余步驟有類似的代碼結構。建立物化視圖后有多個部分都可以從中受益。
建立物化視圖：把查詢條件agent_id列展示出來，用于在外層語句中過濾。
create materialized view TCM_TCP BUILD IMMEDIATE refresh force on commit as
select tcm.agent_id, TCCP.case_id, TCCP.item_id
from TCM,
TCCP
where TCCP.policy_id = tcm.policy_id
and TCCP.audit_conclusion is not null;
建立物化視圖日志，當基表有數據變動時，實時刷新物化視圖數據。
create materialized view log on TCM WITH ROWID INCLUDING NEW VALUES ;
create materialized view log on TCCP WITH ROWID INCLUDING NEW VALUES ;
創建過濾條件AGENT列索引。
CREATE INDEX IDX_TCM_TCP_AGENT on TCM_TCP(agent_id);
最后改寫代碼從物化視圖中查詢：
select count(distinct tcc.case_id) agPolicyTwoClaimNum
from TCM_TCP,
TCC,
TCP,
TPL
where TCM_TCP.case_id = tcc.case_id
and TCM_TCP.item_id = tcp.item_id
and tcp.product_id = tpl.product_id
and (tpl.ill_rate > 0)
and tpl.period_type = '3'
and months_between(tcc.accident_time, tcp.validate_date) <= 24
and tcc.finish_time >=
(select ADD_MONTHS(max(t.apply_date), -12)
from TCM t
where t.agent_id = '387932438')
and TCM_TCP.agent_id = '387932438';
經過上述改造，調整后的執行計劃如下：
查詢在0.01秒完成，整段代碼中沒有訪問任何大量的中間結果集。從這里也能看出，訪問的中間結果少，查詢必然高效。優化到此已到極致。
第四章 解決方案及總結
本案例中，利用了多種優化技術：
降低表的重復訪問；
利用CTE提升回表效率；
利用索引提升訪問效率；
利用物化視圖降低中間結果。
每一種方式，都是直接作用在耗時步驟上面，可以最有效的提升SQL訪問效率。
