複雜SQL效能優化的剖析(二）(r11筆記第37天)

    昨天的一篇文章複雜SQL效能優化的剖析(一）(r11筆記第36天) 分析了一個SQL語句導致的效能問題，問題也算暫時告一段落，因為這個語句的執行頻率是10分鐘左右，所以優化後（大概是2秒左右，需要下週再次確認）的提升很大。

   對於優化是一個持續的改進，我們碰到的問題，最終的原因可能五花八門，但是正如柯南所說，真相只有一個。我把這個問題和前幾天處理的一個問題結合起來，前幾天處理了一個緊急問題，也是有一個SQL語句的執行計劃發生改變，這個語句的業務比較關鍵，觸發頻率是每分鐘一次，如果一旦出現延遲，就是一個連鎖反應。目前語句的執行效率其實不大理想，每次平均要25秒左右。對於我來說，這個結果其實是不可接受的。這麼說的一個原因是據我所知，在另外一個統計庫中，執行同樣的語句只需要1秒鐘。所以對於這個問題我還是充滿資訊的。

語句其實也蠻長，但是還得列出來。

select to_char(t2.servertime,'yyyy-mm-dd hh24:mi:ss') as servertime,t2.deviceid,t2.gamechannel,
 t2.system,t2.device,t2.resolution,t2.dt,t2.appkey from (
 select r1.servertime,r1.deviceid,r1.appkey from
 (select  min(servertime) as servertime,deviceid,appkey from sdk_start where dt=:1  group by deviceid,appkey) r1
 left join
 (select deviceid,appkey from h1_active_dev) r2
 on(r1.deviceid=r2.deviceid and r1.appkey=r2.appkey)
 where r2.deviceid is null
) t1
left join
(select servertime,deviceid,gamechannel,system,device,resolution,dt,appkey from sdk_start where dt=:2 ) t2
on(t1.appkey=t2.appkey and t1.deviceid=t2.deviceid and t1.servertime=t2.servertime)主要的思路就是在表sdk_start中進行初步的過濾，得到每個裝置最早的資訊記錄，然後和另外一個總表h1_active_dev去匹配（左外連線），得到這些資訊之後再來和sdk_start重新關聯，得到儘可能詳細的資訊。兩個表的資料量都是千萬級。

  有了參考的標準，優化也有了一定的方向和章法可依據。

按照目前的執行情況，是對h1_active_dev做了全表掃描，是優化器認為目前最高效的方式。我認真比對了兩套環境，資料量相仿，索引資訊也是相似的，執行計劃卻大大不同。

其中的一個不同之處是sdk_start在當前執行效率較差的環境中，儘管是分割槽區，但是隻有一個預設分割槽pmax,但是我注意到一個情況，同樣的環境，另外一個邏輯相似（表名不同）的語句，表也沒有分割槽，執行效率也很高，也是1秒左右。所以目前我只能推斷表分割槽規範後能夠提升執行效率，但是具體情況還得測試一下才能論證。

    所以目前我更傾向於理解是執行計劃的差別，目前的情況如下：

統計庫1的執行效率較差，統計庫2的執行效率要高。

統計庫1中的SQL執行效率如下：

Enter value for 1: 87m3rru3sy2jt
PLAN_HASH_VALUE AVG_ET_SECS
--------------- -----------
     3721561291      25.965

統計庫2中的執行情況如下，存在兩個子執行計劃，但是效率都不錯。

Enter value for 1: 87m3rru3sy2jt
PLAN_HASH_VALUE AVG_ET_SECS
--------------- -----------
      690487836        .087
     3721561291        .989

我們就可以在執行計劃的深入分析之外大膽做一個嘗試，把統計庫2 的執行計劃抓取出來，替換統計庫1的執行計劃，也算是偷天換日。這個工作做起來其實也不麻煩，有了SQLT這個工具，其實就容易得多。SQLT可參考使用sqlt手工建立sql_profile(r4筆記第37天)

    但是很快做了嘗試發現問題遠沒自己想得那麼簡單，因為替換之後我可以明顯看到SQL的執行效率下降了，原本需要25秒，現在需要至少2分鐘左右。對於1分鐘執行頻率的語句來說影響會被放大，導致SQL執行效率越來越差，有點多米諾骨牌的味道。

    所以在2分鐘的嘗試中，我得出了一個初步結論，單純替換執行計劃是粗放的。因為對SQL Profile有備份，所以馬上進行了恢復，恢復為原來的執行計劃。

  那麼可以有一個很直觀的感覺，那就是表的分割槽的影響，會把語句的效能瓶頸問題放大。

  這個表有4千多萬的資料，目前只有一個預設分割槽，看來也是一個遺留問題，要把資料再次重新分佈，真不是一件簡單的事情，而且sdk_start這個表的邏輯比較特別，只需要保留近2周左右的資料即可（按照日期進行分割槽），所以就牽扯到一個資料清理的問題，目前來看申請維護時間也有些不太合適。所以線上重定義又派上用場了。

    我使用如下的語句生成了批量的分割槽語句，把近1個月的分割槽先補充出來，剩下的統統都放到預設分割槽，最後直接truncate pmax分割槽即可完成資料的清理工作。

select 'PARTITION P_'||to_char((trunc(sysdate)-30+level),'yyyymmdd')||' VALUES LESS THAN (TO_DATE('||chr(39)||(trunc(sysdate)-30+level+1)||chr(39)||', '||chr(39)||'YYYY-MM-DD HH24:MI:SS'||chr(39)||'))  '
    ||'TABLESPACE CMBI_MIN_DATA ,' from dual connect by level<30;

這個語句生成的分割槽補充資訊類似下面的形式：

PARTITION P_20161208 VALUES LESS THAN (TO_DATE('2016-12-09 00:00:00', 'YYYY-MM-DD HH24:MI:SS'))  TABLESPACE CMBI_MIN_DATA ,
PARTITION P_20161209 VALUES LESS THAN (TO_DATE('2016-12-10 00:00:00', 'YYYY-MM-DD HH24:MI:SS'))  TABLESPACE CMBI_MIN_DATA ,
PARTITION P_20161210 VALUES LESS THAN (TO_DATE('2016-12-11 00:00:00', 'YYYY-MM-DD HH24:MI:SS'))  TABLESPACE CMBI_MIN_DATA ,
我們建立了表SDK_START_BAK，索引和許可權也保持和原來的一致。

因為sdk_start沒有主鍵，所以我使用rowid的方式來完成線上重定義的過程。

判斷是否可以做線上重定義。

 exec  DBMS_REDEFINITION.CAN_REDEF_TABLE('MBI','SDK_START',2); 

開始線上重定義的過程。

 exec  DBMS_REDEFINITION.START_REDEF_TABLE('MBI','SDK_START','SDK_START_BAK',NULL,2); 

線上重定義的這個過程蠻有意思，本質上就是物化檢視的prebuilt複製，給表sdk_start_bak加上了物化檢視的殼，完成資料同步之後脫殼（刪除物化檢視）。可以看到線上重定義的過程中會建立一個sdk_start_bak的物化檢視。

OWNER      MVIEW_NAME       QUERY_LEN UP RE REFRESH_MODE REFRESH_METHOD   BUILD_MODE
---------- --------------- ---------- -- -- ------------ ---------------- ----------
MBI        SDK_START_BAK          551 N     DEMAND       FAST             PREBUILT  

整個過程也是有條不紊，先複製資料，然後建立索引。CREATE INDEX "MBI"."IDX_SDK_START_SDA_NEW" ON "MBI"."SDK_START_BAK" ("SERVERTIME", "DEVICEID", "APP
KEY") LOCAL 。。。。表sdk_start的資料量有4千多萬，整個資料複製持續了5分鐘左右。

為了保持資料的同步過程，減少GAP，可以使用如下的方式儘可能減少資料的差別。這個過程就有些類似物化檢視的快速重新整理。

 execute dbms_redefinition.sync_interim_table ('MBI','SDK_START','SDK_START_BAK');

最後是收尾階段，完成資料字典資訊的替換。

exec  DBMS_REDEFINITION.FINISH_REDEF_TABLE('MBI','SDK_START','SDK_START_BAK');

最後完成線上重定義之後，清理預設分割槽即可。

整個過程其實熟練掌握，走下來還是比較流暢的。

但是再次檢視執行計劃，發現執行效率沒有任何改變，這個時候我們不要氣餒，因為這個問題經過之前的分析，其實有更好的執行計劃，那就是統計庫2中的執行計劃，我們再次偷天換日，替換執行計劃。

可以很明顯看到語句的效能有了飛速的提高。2秒鐘即可完成。

    其實對於這個問題有3點總結：

1. 首先一個好的執行計劃是基於準確的資料統計資訊的基礎上。有些場景下Oracle分析的執行計劃可能不是最優的，我們可以在這個基礎上做一些改變。

2. 我們使用了分割槽的方式其實可以大大提高資料篩查的範圍，原來需要掃描100個分割槽的工作量，現在只相當於掃描了1個分割槽。這個提高，如果使用得當，比指數級還高。

3. 對於執行計劃的線上替換，SQLT是一個好工具，CoE提供的這個工具還是有很強大的功能，替換穩定執行計劃只是SQLT功能的冰山一角。

    最後還是開頭所說的那句話：優化是一個持續的改進，我們碰到的問題，最終的原因可能五花八門，但是正如柯南所說，真相只有一個。

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