快速定位隱蔽的sql效能問題及調優

在前幾天，有個開發同事問我一個問題，其實也算是技術救援，他說在有個job資料處理的頻率比較高，在測試環境中很難定位出在哪有問題，而且速度也還能接受，但是在生產環境中總是會慢一些，希望我能在測試環境中協助他們，看看是不是sql語句出什麼問題了還是其它相關的問題。
這種類似實時監控的語句，從第一印象來說，很可能透過awr捕獲不到，如果透過ash來捕獲，因為測試環境中有幾十套測試環境在執行，就算得到某個時間點的一些sql語句，直接在報告中對映到語句對應的schema資訊還是有一些困難的。因為測試時間確實很短，有很多的語句執行了，可能不一定被ash收集到。
我和他首先做了溝通，因為我壓根不知道這是哪個應用的環境，所以先需要幾分鐘的時間來熟悉一下環境，提前準備一下。
資料庫中存在大概50套測試環境，佔用的session數大概在4000個左右。整體來看測試環境中的資料量都不大。每個環境都大概在10G-30G以內。
定位到制定的測試環境中，發現session佔用情況也不高。都是一些常規的job使用，沒有看到其它明顯的session消耗，檢視相關的鎖資訊，也沒有發現什麼問題。
簡單確認之後，發現awr在這個時候是用不了了，最多使用下ash來看，除此之外，還可以使用指令碼實時監控。
類似下面這樣的操作。
> getash.sh
I    SID   SER# USERNAME     OSUSER     STA RPID    SPID   MACHINE    PROGRAM              ELAP_SEC    TEMP_MB UNDO_MB SQL_ID        TSPS   SQL
-- ------ ------ ------------ ---------- --- ------- ------ ---------- -------------------- ----------- ------- ------- ------------- ------ -------------------------------------------------
 1     19  16945 xxxx    blwrk01   ACT 9442    9442   ccbdbprx   oracle@xxxxxx  00 05:35:02                 b9xg175fbzuk5        INSERT INTO xxxx (CYCLE_SEQ_NO, PAY

上面的語句也可以透過watch來指定頻率看到每個使用者下的資訊實時變化情況。監控的過程中確實也能看到不少的資訊變化，但是執行的時間確實很短，只能夠抓取到一部分sql語句。簡單分析了下，那些語句都沒有發現有什麼問題。
這個時候還是得靠開發協助，希望他們提示一些更細節的資訊，這個業務場景要做的事情和一些指定的資料，他們提供說使用了某個表中資源號為 x271051128的資料，這個時候透過v$sql從快取中就能夠快速定位到語句，這個時候再和ash配合起來就能夠確認是否是相關的使用者在呼叫了。
最後抓取到了幾條語句，和開發確認之後定位到一條語句，語句類似下面這樣的形式。

select owner_id,

       l3_balance_amount,

       expiration_date,

       customer_id,

       c64_1,

       l3_balance_Status,

       sys_update_date,

       sys_creation_Date

  from accumulators

where customer_id in

       (select customer_id

          from subscriber

         where prim_Resource_Val in ('x271051128'))

   and owner_type = 'P'
透過抓取執行計劃，發現subscriber表走了全表掃描。這個對應生產環境中的效能影響還是比較大的。

對於這個問題的調優，其實可以完全透過業務層面來最佳化，可以參考http://blog.itpub.net/23718752/viewspace-1312163/
問題是類似的，略有不同。我們可以引入一個更大的資源表，資源表agreement_resource和使用者表subscriber，使用索引欄位來關聯，就避免了subscriber表的全表掃描。
調整後的語句如下：

select owner_id,

       l3_balance_amount,

       expiration_date,

       customer_id,

       c64_1,

       l3_balance_Status,

       sys_update_date,

       sys_creation_Date

  from ape1_accumulators

where customer_id in

       (

       select customer_id

  from subscriber s

where (subscriber_no, PRIM_RESOURCE_TP) in

       (select agreement_no, RESOURCE_TYPE

          from agreement_resource r

         where r.resource_value in ('x271051128'))

       )

   and owner_type = 'P'
透過調整後的執行計劃可以看出，效能的提升還是很大的。這個是測試環境的資料，如果在資料量大的時候，優勢就更加明顯了。

所以對於這個問題，起因是有個job資料處理的頻率比較高，在測試環境中很難定位出在哪有問題，而且速度也還能接受，但是在生產環境中總是會慢一些，其實深究起來還是有原因的，只能透過各種細節去診斷發現了。