生产环境sql语句调优实战第六篇(r2笔记91天)

生产环境中有大量的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秒左右。



通过查看执行计划,发现瓶颈在做递归查询的时候。其实那个表很小的,数据不到100条,但是怎么递归出来了百万的数据呢。

相关的语句如下:

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

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

原文发布于微信公众号 - 杨建荣的学习笔记(jianrong-notes)

原文发表时间:2014-09-10

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏杨建荣的学习笔记

SQL*Loader-805的解决(r2笔记36天)

使用sql*loader是大型项目中数据迁移的利器。如果是外部系统,其他数据库到oracle的数据迁移,使用文本式文件是最兼容的方式。 sqlldr的加载效率是...

3158
来自专栏杨建荣的学习笔记

只言片语分析datapump的工作原理(r2第18天)

datapump是从oracle 10g推出的新的数据导入导出工具,可以说是exp/imp的加强版,主要的亮点在于服务端,结合了direct+parallel,...

2383
来自专栏杨建荣的学习笔记

深度解析dba_segments和sys.seg$中的细节差异(上) (r5笔记第27天)

今天在查看系统空间使用情况的时候,发现一个细节的问题,自己死磕了一把,还是发现了不少有价值的东西。 事情的起因是我在使用脚本在某个环境中查看每个用户所占有的空间...

3548
来自专栏杨建荣的学习笔记

不经意发现的dba_objects和dba_tables中的细节(r7笔记第56天)

今天有一个同学问我一个问题,因为白天比较忙也没有在意,在下班后坐地铁的时候抽空看了这个问题,感觉还是蛮有意思的。但是当时也没有任何答案,就准备自己回去好好实验一...

3803
来自专栏idba

死锁案例之四

一 前言 死锁,其实是一个很有意思也很有挑战的技术问题,大概每个DBA和部分开发同学都会在工作过程中遇见 。关于死锁我会持续写一个系列的案例分析,希望能够对想...

853
来自专栏乐沙弥的世界

使用带dblink方式的datapump迁移Oracle 10g到11g

      对于从Oracle 10g下迁移数据库到Oracle 11g,除了使用RMAN方式之外,我们可以使用带dblink的datapump方式来实现基于逻...

681
来自专栏杨建荣的学习笔记

简单实用的sql小技巧(第一篇) (r3笔记第36天)

今天和大家简单分享几个实用的sql小技巧。还有一些还在整理中,会不断的分享出来。 有些其实也不算是sql的技巧,可能大家在写sql语句的时候没有意识到我们可以通...

3003
来自专栏杨建荣的学习笔记

关于db link权限分配的苦旅(一) (r7笔记第42天)

昨天接到一个开发的需求,内容看起来非常简单。 申请数据库192.168.1.118:1522:TEST下用户APP_TE_FLOW_128赋予对表testore...

3426
来自专栏数据和云

触类旁通:那些关于 TBL$OR$IDX$PART$NUM 的诡异案例和知识

你是否留意过数据库中有一个奇怪的函数 TBL$OR$IDX$PART$NUM ,你是否留意过很多场景下都出现过它的身影?

1414
来自专栏杨建荣的学习笔记

一个普通数据库用户所能查到的"意料之外"的信息(r2笔记98天)

有时候限于工作环境的情况,大多数开发人员只得到了一个权限收到限制的数据库用户。 可能你都不知道你所拥有的数据库用户都能查到哪些你想象不到的数据库信息,其实你知道...

3438

扫码关注云+社区

领取腾讯云代金券