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

生産環境中有大量的sql語句在運作，盡管有awr,ash做資料的收集統計，但是dba的調優工作大多數情況都是在問題已經發生後做排查的，有些sql語句可能執行的時間有1,2分鐘左右，但是sql語句本身有潛在的性能問題，通過awr是定位不到的，ash盡管能夠查到，但是我們在未知的情況下怎麼知道問題發生的精确時間點，通過sql monitor能夠查到一些實時的性能問題，但是還是需要按照自己的情況和要求來不間斷地進行性能的監控。通過一個工具一勞永逸是不現實的。

今天想做資料遷移也有些日子了，看看生産環境中有哪些sql語句出現頻繁，而且有明顯的性能問題。

關于通過shell和sql查找性能sql的部分，可以參考如下的文章，基本原理就是背景做sql_monitor的監控，然後由shell進行性能資料的統計。

<a href="http://blog.itpub.net/23718752/viewspace-1253426/">http://blog.itpub.net/23718752/viewspace-1253426/</a>

通過如下的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

6

105s

xxxxx(7425:49389)

09/09/2014 07:05:34

09/09/2014 07:05:40

09/09/2014 07:07:20

107s

xxxx

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

<b>-&gt;</b>

7

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

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

                          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

      ,(SELECT LEVEL l FROM DUAL CONNECT BY LEVEL

   AND JOB_NAME = 'xxxx'

   AND l

)T, CSM_OFFER CO WHERE T.SOC_NAME = CO.SOC_NAME

and T.SOC_NAME is not null

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

<b>Elapsed: 00:00:00.05</b>

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