大连_OCM_彭小波 Oracle DBA

分享并记录自己在工作,学习过程中遇到的有关于Oracle和Linux的相关知识。微信:pxboracle

  • 博客访问: 1983313
  • 博文数量: 244
  • 用 户 组: 普通用户
  • 注册时间: 1970-01-01 08:00
  • 认证徽章:
个人简介

彭小波电话15041131262邮箱pxboracle@live.com,QQ群:282433884为企业、个人提供Oracle现场培训,为企业提供专业Oracle数据库技术服务:包括各种主流平台下的Oracle安装配置、故障诊断、性能调优、备份恢复、数据迁移、高可用及容灾系统的搭建及维护。

ITPUB论坛APP

ITPUB论坛APP



APP发帖 享双倍积分

文章分类

全部博文(244)

文章存档

2018年(2)

2017年(8)

2016年(23)

2015年(72)

2014年(138)

2009年(1)

微信关注

IT168企业级官微



微信号:IT168qiye



系统架构师大会



微信号:SACC2013

RWP谈SQL优化 2018-01-10 16:10:34

分类: Oracle

Oracle Real-World Performance团队是一个人数不多的天才团队,整个团队的数据库性能优化经验有超过400人年。团队成员分布在美国,中国和欧洲,不断的寻找和创造新的方法分析诊断当今世界业务系统的性能。Oracle Real-World Performance团队有着很多出类拔萃的战绩,在工作中,多次将客户系统性能提升几十上百倍,给客户系统性能提升1000倍或更多也并不罕见。
  在中国,业界同仁将Oracle Real-World Performance团队简称为RWP团队。目前RWP团队在中国共有7名成员。

    说到SQL优化,做为读者的您,头脑中第一反应是什么?索引?Hint?分区?参数?执行计划?哈哈哈有被言中吧 ;-),今天我们就来谈谈SQL优化的整体思路,希望能够对您有所启发。
1. 设定一个高的目标

 如果您把一个SQL从一个小时优化到了1分钟,您会停止工作吗?会不会考虑是否能给它优化到1秒钟?
   工作中,每个人都有压力,压力之下,很容易疏于思考。一个SQL多长时间能跑完,依赖于它跑在什么样的硬件和软件环境上。一个SQL能不能跑的更快,本质上是:它是否能够更加充分的利用硬件资源和软件能力。
    做SQL优化,给自己设定一个高的目标非常重要!
2.  去优化那些好的SQL

 

有了高的目标,接下来,还要找到那些好的SQL进行优化。那么,什么是好的SQL?
(1)   有效的 SQL

    数据库是为了执行SQL设计的,不是为了一执行就报错的无效SQL设计的。

     如果执行一个SQL,报ORA的错误,那么这是一个无效的SQL,它不应该存在于您的系统里面,当然更不应该成为您优化的对象。

    如果执行一个SQL,报ORA的错误,那么在数据库里面会是一个failure parse。如果您系统的AWR报告里面有failure parse,那么您要注意了,后果可能很严重。

(2)   您知道业务含义的SQL

    有很多时候,一些SQL和PL/SQL存储过程是根本就不需要被执行的。但是由于种种原因,那些SQL和PL/SQL存储过程存在在系统中,可能都已存在了很长时间,写那些SQL和PL/SQL存储过程的人可能早就跳槽了,为了所谓的“稳定”,没有人去动那些SQL和PL/SQL存储过程。去优化这些根本就不需要被执行的SQL和PL/SQL存储过程当然是没有任何意义的。


    所以,在优化任何一条SQL之前,应该首先知道那条SQL业务上的含义,确定它确实是需要被执行的,再去优化它。

 

(3)   构造好的SQL

    如果一个SQL语句里面有IN列表,IN列表里面有几百个值,那么那几百个值,很有可能是来源于另外一个SQL,而非人工输入。由于IN列表中值的个数有一个允许的上限,有些SQL甚至会长成下面的样子:

 

    几百几千几万个值在IN列表里面,那是不是SQL构造的不好,是不是应该先将它改成一个JOIN再去考虑其他?

 

(4)   没有编写错误的SQL

    N个表做JOIN的话,一般情况应该有N-1个JOIN条件。如果JOIN条件小于N-1个的话,就会有CARTESIAN JOIN出现,结果集里面会有重复值。在SELECT LIST里面加上DISTINCT,通常就可以使得SQL得到功能上正确的结果集。这就好比您去银行取钱,实际只要取1000块钱,可是您先取了2000块钱,再把余下的1000存回去,多此一举,虽然实际结果是对的,您确实是取了1000块钱。

    当SQL处理的数据量小的时候,这个多此一举对于响应时间的影响并不会很大。可是当SQL处理的数据量大的时候,这个影响就会完全凸显出来。还是那个取钱的例子,如果您实际只要取1000块钱,可是您先取了10001000块钱,再把余下的10000000块钱存回去。最后您也会得到1000块钱,可是银行员工为您取钱的时候数出10001000块钱的时间,和把钱存回去的时候再数好10000000块钱的时间,都是您办业务的时间,您取钱的时间就会变得相当长了。

     SQL语句中WHERE条件里面的值的数据类型,应该与相应的列的数据类型一致。否则SQL语句虽不会报错,会隐式的用函数将那个列转换成与相应的值的数据类型一致,去执行SQL。这种隐式数据类型转换,可能会导致ORA-01722的错误,可能会导致相应的列上的索引不能被使用到,可能会导致明明可以使用分区裁剪但却用不上的情况,响应时间可能差好几个数量级。


3.  给SQL一个好的执行环境


    SQL需要在好的环境上执行才能够性能好。那么什么是好的执行环境呢?

    正确的给软件打上补丁,是打造好的执行环境的第一步。明明您都花了钱买软件,明明人家软件厂家都出了补丁可以让软件跑的更好更快,为什么不打补丁呢?当然了,打补丁是个技术活,怎么正确的给软件打上补丁,肯定是要按照软件厂家的说明来,或者咨询软件厂家啦。

    使用默认的init.ora参数设置,也是打造好的执行环境的重要一环。使用默认的init.ora参数设置,意味着您是按照Oracle内部研发团队设计软件的方法去使用它,意味着您使用的是经过Oracle内部测试团队严格测试的软件。当然了,有一些特定的应用软件,比如Oracle的EBS,要求修改init.ora参数,这种情况是要修改,因为那些修改是经过应用软件厂家严格测试过的。

    如果是因为遇到bug,需要修改某些参数做为临时解决方案,那么当那个bug修复之后,您应该及时将相应的参数改回去,否则后果可能也会很严重噢。

    另外,若随意修改init.ora参数,可能会导致售后的问题。

4.  从数据库设计的角度优化SQL

 
    现在Oracle数据库软件使用的是Cost Based Optimizer(CBO),基于成本的优化器。

    本质上来讲,优化器就是一系列的算法。优化器会接受输入的信息来生成SQL的执行计划。输入的信息包括:


(1)   统计信息

    统计信息包括两个方面,系统的统计信息,和实际用户数据的统计信息。

     系统的统计信息,推荐大家使用默认设置。实际用户数据的统计信息,最重要的是要有代表性,要能够反应数据的特征。

 

(2)   约束

    NOT NULL, PK, FK, UK等等约束,若实际数据是需要符合约束的,那么那些约束应该存在于数据库里面,应该让优化器知道这些约束的存在。

    举个例子。多个表做JOIN,如果某张表只是被JOIN了,比如下面这样事儿的

 


    customer表只出现在了JOIN部分,但是并没有出现在SELECTlist里面,也没有出现在查询条件里面,也没有出现在GROUP BY和ORDER BY的部分里面。那么如果lineorder表上的JOIN key(lo_custkey)上存在外键约束的话,优化器就会知道lo_custkey = c_custkey这个JOIN总是能够JOIN的上,那么在实际执行的时候就不会去JOIN customer这个表了。执行计划可以是下面这样事儿的:

 

    您擦亮双眼看好了么,customer表压根儿就没有出现在执行计划里面!您能做的最快的JOIN就是不JOIN啊哈哈哈。这种情况我们叫做JOIN elimination,发生的前提条件是相关约束的存在。

 

(3)   Schema设计

    Schema的设计,包括数据模型,索引,分区,压缩,clustering(数据根据相应的KEY值物理上存放在一起)等等,对SQL性能都有非常重要的影响。


    有些SQL里面,一个表和自己JOIN几十次,就是因为数据模型设计得不好导致的。此时若只是专注于SQL本身,能够取得的性能提升恐怕就非常有限了。

    Schema设计是门大学问,每一个方面都可以对SQL的性能有几个数量级的影响。想做好SQL优化的话,您必须要将schema设计重视起来。

 

 

5.  从执行角度优化SQL

 
从执行的角度去优化SQL,主要是要考虑以下方面:


Access method,是通过索引访问数据,还是全表扫描。

Join方法,是Nested Loop Join,Hash Join,还是Merge Join。

Join顺序,是表A Join表B,再Join表C,还是反之。

并行执行时,生产者进程组和消费者进程组之间的数据分发方法,是hash,还是broadcast,还是其他的分发方法。

数据是否有倾斜,是否某些KEY值对应的数据特别多,其他KEY值对应的数据特别少。

 

总结
    以上几点给您提供了一个SQL优化的整体思路。整体思路总是很重要。

    那么具体的,如果一个性能差的SQL摆在您的面前,必须去优化它,要从那里入手呢?SQL Monitor Report将会是您的好朋友,欢迎一见钟情。后续我们会推出系列文章,举例说明如何用SQL Monitor Report诊断SQL的性能问题,欢迎您持续关注。

 

阅读(851) | 评论(0) | 转发(0) |
0

上一篇:RWP团队谈SQL优化

下一篇:没有了

给主人留下些什么吧!~~
评论热议
请登录后评论。

登录 注册