生产环境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>

生成的数据也是按照预期的格式进行了行列转换。