生産最近晚上日終批量增加不少新sql，同樣也導緻了各種的慢，但是綜述覺得礙眼。
環境：阿裡雲ECS Oracle 11.2.0.1 ADG
最近資料庫晚上在0點到2點之間的報表任務job運作時間明顯上升，如下是截取7天快照DB TIME所看到的情況：
雖然并沒有影響到日終的進行，但是由于上次造成alert出現snapshot too old的事件以後，還是覺得需要看一下為好，索性就找了當天的0點到1點的awr報告看了一眼：
我同時檢視了之前幾天的，都如上圖所示，鶴立雞群，很明顯，2c7ut81kqjf4k 這個sql_id語句執行了300多秒，執行一次，一開始我以為查的資料比較多，加上SQL語句有些問題，會很慢，然後就将對應的sql_id的awr報告下載下傳：
執行300多秒，愣是沒有查出什麼資料來。這裡我就開始懷疑，SQL查詢資料可能數量極少，但是時間很久。然後就手動執行了這條SQL語句：
原始SQL語句如下：
果然執行343s，cost多達549M，16w邏輯讀，7216實體讀，2次記憶體排序，就查出來一條資料。
很明顯這是不正常的。
1）、然後我們來分析一下執行計劃：
問題1：其中sort join出現2次，還有一次merge join；
問題2：步驟10那裡就因為這個sort join導緻了13M的臨時表的占用，并且cost值迅速增大，看謂詞資訊發現是條件d.PROCESSINSTANCEID >= c.PROCESSINSTANCEID搞的怪，然而我非常不明白，這裡并沒有使用函數，為何會有internal function字樣的出現；
問題3：步驟7,8，很明顯，驅動表不對，ACCEPAPPLYINFO 表應該是被驅動表才對啊，這樣再加上不好的驅動nested loop，cost又是上漲一大截；
問題4：謂詞條件2,7，全部都是filter，尤其針對2，子查詢并未展開，而是直接過濾。
綜上來說，這個SQL語句改寫的餘地很大，這裡暫時沒有考慮索引的問題。
2）、然後讓我們來分析SQL語句的問題：
問題1：第一個where條件就類似于：c. ACTIVATIONTIME = max(c.ACTIVATIONTIME)再加幾個條件的等價子查詢，這其實就平白多增加了一次對c表的掃描操作；
問題2：這裡又有一個d.PROCESSINSTANCEID >= c.PROCESSINSTANCEID的條件導緻Oracle内部必須對兩張表都進行排序然後比較；
問題3：a.DATASOURCE not in ('2', '4')條件，曆來都反對有not in的操作。
問題4：(select businessdate from smsysdate) - trunc(c.ACTIVATIONTIME)，總感覺這個trunc用的不好，不過後來證明我是錯的，關鍵點不在這裡。
到了這裡分析告一段落，讓來開始進行改寫操作。
總體的大緻思路是：盡量減少排序操作和減少表的掃描操作，這個思路看似沒有什麼問題，但是我卻忽略了整體，着重了片面，導緻我停留了很長時間在這裡；
a、最開始，針對分析SQL第一個問題，第一反應就是去掉group by，如何去掉呢，就是使用分析函數。針對
select max(t.activationtime) activationtime from VBPMEXTTASK t where t.PROCESSINSTANCEID = d.id group by t.PROCESSINSTANCEID 子查詢，改寫如下：
然而其實我是在自欺欺人，雖然去掉了group by，但是效果并不理想，這不過是一個變着法的排序罷了。
b、然後就是針對問題3，not in的改寫，查詢了一下實際的表，這個DATASOURCE列隻有三個值，0,1,2，是以我就直接改成了a.DATASOURCE in ('0', '1')，企圖有所效果，可是，并沒有眷顧我，效果甚微，隻是從549M到了541M，基本沒什麼變化。
c、然後就是針對問題2的d.PROCESSINSTANCEID >= c.PROCESSINSTANCEID，想着既然這個條件隻有c和d兩個表，那我要是單獨合到一起的話怎麼樣呢，然後就有了如下改寫：
select max(c.ACTIVATIONTIME) from ACCEPAPPLYINFO a,VBPMEXTTASK c, VBPMPROCESSINFO d where d.PROCESSINSTANCEID >= c.PROCESSINSTANCEID and a.applysubno = d.BUSINESSKEY and c.PROCESSINSTANCEID = d.id group by c.PROCESSINSTANCEID 整體情況:
然而雖然檢視執行計劃cost從549M降到了289M
然而，查詢出來的結果竟然有198條資料，完全不是1條資料啊，改寫再一次失敗
分析原因，應該是我多加一個a表，導緻最後進行關聯連接配接的時候導緻更多的資料被保留下來，這很明顯不符合原SQL語義，雖然文法沒有了問題，但是語義出現了偏差。
不過意識到這個問題以後，我有些豁然開朗
我的整體思路沒有理清，雖然自己是針對片面減少了排序和時間，但是語義上出現了偏差。
然後重新理了一下思路：
減少排序是必須的，但是還有一點就是縮小所需結果集，按照集合的思想來考慮，盡可能使用更多的條件來限定使用的子查詢結果集的大小。是以進一步修改SQL的語句如下：
解讀一下就是，我先把a、b、c三張表進行關聯查詢結果，把原SQL的第一個子查詢分解掉，直接使用max函數查詢結果，然後将各條件剝離出來的結果進行group by，這樣所需的結果集就縮小了很多。然後外層再進行主查詢，使用最後一個子查詢條件進行濾過即可。
然後再一次執行SQL語句如下：
可以看到時間從343s一下降到了0.38s，cost從549M降到14199，邏輯讀從16w降到4w，實體讀從7192降到402，記憶體排序從2次變為0，變化巨大。
其實到這裡基本就可以完工了，我也本來打算就醬紫了，準備送出開發同僚，但是，吃飯的時候我看着這個執行計劃，心裡還是癢癢。
a（ACCEPAPPLYINFO） 表仍然是全表掃描，但是條件裡有
and a.applysubno = d.BUSINESSKEY
and a.busistate in ('09', '17')
and a.DATASOURCE not in ('2', '4')
這三個條件和a表有關，那為何不走索引呢，然後我就查了一下applysubno 列distinct值很多，但是由于是等值條件，無法使用也算正常；
DataSource列一共就0,1,2,單個distinct值，開始也測試了也沒有必要，何況我查詢這個表的索引的時候applysubno 列是有索引的，然而并沒有走；然後隻有busistate這個列了，沒有索引，不同的值檢視了一下有30個，也就是說這個SQL用到了2個，雖然理論上來說，選擇性并不是很好，但是還是覺得嘗試一下如何
SQL> create index idx_busistate on ACCEPAPPLYINFO(busistate);
然後再一次進行查詢操作：
見證奇迹的時刻到了
從原來的0.38s降到了0.05s，邏輯讀從14199降到1947，實體讀從402降到378，對于a表也走了索引，正是我建立的索引，謂詞條件也看到從原來的filter，變成了access。
如此，完美！！！