在前幾天，有個開發同僚問我一個問題，其實也算是技術救援，他說在有個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表的全表掃描。
調整後的語句如下：
from ape1_accumulators
(
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'))
)
通過調整後的執行計劃可以看出，性能的提升還是很大的。這個是測試環境的資料，如果在資料量大的時候，優勢就更加明顯了。
是以對于這個問題，起因是有個job資料處理的頻率比較高，在測試環境中很難定位出在哪有問題，而且速度也還能接受，但是在生産環境中總是會慢一些，其實深究起來還是有原因的，隻能通過各種細節去診斷發現了。