效能下降的不定時炸彈_過舊的sql_profile

最近這一週以來，生產環境像是得了重病的病人一樣，小問題沒有修好，大問題不斷。IO的等待極為嚴重。資料庫的負載達到了幾十倍，上百倍。
weblogic和tuxedo在很大程度上都受到了影響，導致業務響應極為緩慢。
在排查了中介軟體部門，資料庫，儲存，網路，作業系統等各個層面，也發現了儲存的一些小問題，問題比較大的就是資料庫這邊的一個sql語句，每執行一次需要7分多鐘，按理說這種型別的語句執行7分鐘左右可能不是太大的問題。但是瞭解到這個sql語句在所有的中介軟體層面都需要頻繁的傳送sql請求，比如有20個weblgoic,那麼可能就同時會傳送20個sql請求，這個問題一下子就變得有些嚴重了。
更有些讓人納悶的是，透過檢視sql語句，發現裡面新增了一些hint,這些Hint在認真的校驗之後，都沒有問題，根據產品線的反饋，這些都是反覆驗證的最優執行。裡面有一個5千多萬條資料的表，需要走全表掃描，在Hint裡面加了一些指定的處理，都是很合理的處理。
我抓取到top sql以後，檢視執行計劃就有些納悶，為什麼執行計劃和Hint裡面的有很大出入，Hint中指定了使用全表掃描，並行，但是生產環境的執行計劃卻是用了索引，沒有使用並行。
反覆的驗證之後，但是也沒留意，自己主觀的認為沒有走並行可能是資源使用受限，沒有多餘的並行資源可用了，所以沒有走並行，走索引掃描，可能是Oracle認為透過索引掃描的代價更低。
但是自己的認識確實是錯了。我顯示透過sql monitor得到了對於那個的執行報告，然後生成了一個sql tuning advisor的報告，裡面給的兩個建議，一個是新增對應的索引，讓一些資料的過濾更加高效，這個也需要斟酌，畢竟新增額外的索引會對dml產生一定的影響，可能原有的一些執行計劃會受到影響，得不償失。 另外一個建議就是增加並行。這個地方我就有些納悶了，本來已經增加了並行，但是似乎沒有產生什麼效果。
最後檢視sql_profile的配置時，自己才恍然大悟。sql語句沒有走hint指定的執行計劃，很大程度上是因為已經配置了對應的sql_profile，這個sql_profile裡面指定了資料的訪問需要走索引，不走並行等等。按照問題排查的思路，我們這幾個月對沒有處理過這個sql語句。最後一檢視建立的時間讓人大跌眼鏡，這個sql_profile是在去年的10月份建立的，那個時候裡面的資料要少很多。按照當時的分析和資料量，訪問索引可能效率更高，代價更低。但是過了快一年了，結果資料量增加了好幾倍，系統的負載也上來了，原來的profiel就產生了負面的影響，本來影響還不明顯，慢慢的隨著資料量的不斷增大，問題越來越嚴重，導致了隔了一年以後終於爆發了。
所以說過舊的profile真是一個不定式炸彈，在系統的升級過程中，有些執行計劃確實要好好斟酌。oracle畢竟沒有那麼有遠見的判斷你資料的增長情況，它只是在當前的情況下能分析出最友，代價最低的一些建議。但是需要我們自己來判斷。

當然了這個問題的處理也不是一句話就解決的事，我們需要做好充分的準備，首先需要備份原有的profile，萬一效能更加糟糕了怎麼辦，至少我們可以讓執行計劃不會更加糟糕。恢復回去。
這個時候就可以使用dbms_sqltune裡面的幾個小功能。
這個功能是建立一個sqlprofiel備份的表，所有的profile的備份資料可以放在這個表中

SQL> exec DBMS_SQLTUNE.CREATE_STGTAB_SQLPROF(table_name=>'STAGE',schema_name=>'N1');

PL/SQL procedure successfully completed.
使用如下的命令可以直接把當前的profile資料備份到表中

SQL> exec dbms_sqltune.PACK_STGTAB_SQLPROF(PROFILE_NAME=>'SYS_SQLPROF_01419cd955b40000',STAGING_TABLE_NAME=>'STAGE',STAGING_SCHEMA_OWNER=>'N1');

PL/SQL procedure successfully completed.

有了這些資料，就可以匯入到別的庫上或者直接恢復。
當然了在目前的庫上我們先不需要這個profiel了，就需要直接刪除它。
exec dbms_sqltune.drop_sql_profile('SYS_SQLPROF_01419cd955b40000');
這樣處理之後，就等效能能夠有質的飛躍了。