生產環境sql語句調優實戰第六篇

生產環境中有大量的sql語句在執行，儘管有awr,ash做資料的收集統計，但是dba的調優工作大多數情況都是在問題已經發生後做排查的，有些sql語句可能執行的時間有1,2分鐘左右，但是sql語句本身有潛在的效能問題，透過awr是定位不到的，ash儘管能夠查到，但是我們在未知的情況下怎麼知道問題發生的精確時間點，透過sql monitor能夠查到一些實時的效能問題，但是還是需要按照自己的情況和要求來不間斷地進行效能的監控。透過一個工具一勞永逸是不現實的。
今天想做資料遷移也有些日子了，看看生產環境中有哪些sql語句出現頻繁，而且有明顯的效能問題。
關於透過shell和sql查詢效能sql的部分，可以參考如下的帖子，基本原理就是後臺做sql_monitor的監控，然後由shell進行效能資料的統計。
http://blog.itpub.net/23718752/viewspace-1253426/

透過如下的sql語句定位到目前有如下的幾個sql語句出現頻繁，執行時間較長。

select *from (select  sql_id,count(*)cnt from issue_sql where sql_date like '1408%' group by sql_id) order by cnt desc;

SQL_ID                                CNT
------------------------------ ----------
ap6dzwkwk4zrw                          26
xxxxxxxxxxxxx

一般出現效能問題的sql語句執行時間都較長。
可以看到如下的sql語句執行時間很不穩定，最長執行時間差不多在5000多秒。最短在50秒左右。

---

Instance ID	:	1	Buffer Gets IO Requests Database Time Wait Activity . 55 . 5 . 5148s
Session	:	xxxxxx(1925:53293)
SQL ID	:	ap6dzwkwk4zrw
SQL Execution ID	:	16777216
Execution Started	:	09/06/2014 16:40:45
First Refresh Time	:	09/06/2014 16:40:51
Last Refresh Time	:	09/06/2014 18:06:34
Duration	:	5149s
Module/Action	:	JDBC Thin Client/-
Service	:	xxxxxx
Program	:	JDBC Thin Client

---

Instance ID	:	1	Buffer Gets IO Requests Database Time Wait Activity . 55 . 6 . 105s
Session	:	xxxxx(7425:49389)
SQL ID	:	ap6dzwkwk4zrw
SQL Execution ID	:	16777216
Execution Started	:	09/09/2014 07:05:34
First Refresh Time	:	09/09/2014 07:05:40
Last Refresh Time	:	09/09/2014 07:07:20
Duration	:	107s
Module/Action	:	JDBC Thin Client/-
Service	:	xxxx
Program	:	JDBC Thin Client

透過檢視執行計劃，發現瓶頸在做遞迴查詢的時候。其實那個表很小的，資料不到100條，但是怎麼遞迴出來了百萬的資料呢。

->

7

..... CONNECT BY WITHOUT FILTERING (UNIQUE)

.

.

.

.

1

12M

.

.

. 99%

相關的語句如下：
SELECT DISTINCT REGEXP_SUBSTR(PARAM_VALUES, '[^,]+', 1, LEVEL) AS SOC_NAME
  FROM CM9_BATCH_CONTROL
WHERE PARAM_NAME = 'OFFER'
   AND JOB_NAME = 'xxxxxxx'
   AND JOB_REC = 'ENDDAY'
CONNECT BY REGEXP_SUBSTR((SELECT PARAM_VALUES
                           FROM CM9_BATCH_CONTROL
                          WHERE JOB_NAME = 'xxxxx'
                            AND PARAM_NAME = 'OFFER'),
                         '[^,]+',
                         1,
                         LEVEL) IS NOT NULL;


化繁為簡，先來看一下這個語句想要做什麼，透過下面的查詢，發現出現了一些列值按照逗號進行分隔。

SELECT PARAM_VALUES
                           FROM CM9_BATCH_CONTROL
                          WHERE JOB_NAME = 'xxxxx'
                            AND PARAM_NAME = 'OFFER'

PARAM_VALUES
--------------------------------------------------------------------------------
ROHOMS01,ROHOMS02,TESTVDF01,TESTVDF02

可以猜想最開始的想法應該是要解析字串，做一個行列轉行。
在這個基礎上去檢視完整的語句就清楚了。

所以按照這個思路輸出的結果應該是
SOC_NAME
--------------------------------------------------------------------------------
ROHOMS01
ROHOMS02
TESTVDF01
TESTVDF02

按照這個思路，可以給出如下的改進版本,這個語句的關鍵就在下面的標黃處。
生成一個類似dual表的虛表來存放一些需要的資料然後和基表做匹配。

SELECT CO.SOC_CD FROM (SELECT REGEXP_SUBSTR(PARAM_VALUES,'[^,]+',1,l) AS SOC_NAME
  FROM CM9_BATCH_CONTROL
      ,(SELECT LEVEL l FROM DUAL CONNECT BY LEVEL<=100)
WHERE PARAM_NAME = 'OFFER'
   AND JOB_NAME = 'xxxx'
   AND JOB_REC = 'ENDDAY'
   AND l <=LENGTH(PARAM_VALUES) - LENGTH(REPLACE(PARAM_VALUES,','))+1
)T, CSM_OFFER CO WHERE T.SOC_NAME = CO.SOC_NAME
and T.SOC_NAME is not null

來看看執行的效果怎麼樣吧。

Elapsed: 00:00:00.05

生成的資料也是按照預期的格式進行了行列轉換。