sitemap

RSS地图

收藏本站

设为首页

Oracle研究中心

当前位置:Oracle研究中心 > 运维DBA > Oracle性能优化 >

【学习笔记】Oracle优化SQL语句之执行计划详细研究笔记

时间:2016-11-24 21:40   来源:Oracle研究中心   作者:网络   点击:

天萃荷净 Oracle研究中心学习笔记:分享一篇关于Oracle数据库SQl语句优化之执行计划的笔记,说到执行计划,oracle的拥趸们自然而然会兴奋起来。在ORACLE的世界里,执行计划有着其特殊的地位,如果我们将SQL的执行过程看成一个生命,那某种程度上,执行计划就是其DNA。
在某搜索网站中,“oracle 执行计划”关键字的搜索结果与“oracle”关键字的搜索结果占比为1.7%。足可见执行计划在ORACLE中举足轻重的地位:
Oracle执行计划分析
而当我们输入“oracle执行计划”时,推荐关键字排第一的就是“ORACLE执行计划怎么看”
一个标准的执行计划大致可以分为三个部分:访问方式(表访问、索引访问等)、连接方式(NESTED LOOP、HASH JOIN等)及访问顺序(驱动表等)
分析Oracle执行计划
Oracle执行计划怎么看
我们对上述SQL稍加改动,再看执行计划:
什么情况?DEPT表不见了,执行计划居然“残缺”了:
1、          这是ORACLE的BUG吗?
2、          少了一张表,结果正确吗?
3、          ORACLE优化器如此大胆,其背后是谁在给他撑腰?
4、          ORACLE凭什么擅作主张?
 
为了回答上述问题,我们就进入今天的第一个主题:残缺的执行计划。
在展开之前,我们先做数据准备,分别创建两张表EMP及DEPT,脚本如下:
分析SQL语句执行计划
现在我们有如下一条语句:
这条语句非常简单,就是获取EMP表与DEPT表内关联后的数据量。
SQL语句分析
在看具体的执行计划之前,我们解读下在常规情况下,DB是如何处理这样的数据的
1、          分别读取emp表和DEPT表的数据;
2、          对EMP中的DEPTNO与DEPT表中的DEPTNO进行内关联;
3、          对内关联后的数据进行汇总计算;
4、          返回汇总计算结果。
也就是说会存在EMP与DEPT表的内关联,因为SQL就是这样写的。
那我们看下该语句的执行计划,如下:
ORACLE优化器果真是按照我们的预想制定了执行计划。
SQL语句分析执行计划

由于在模型分析时,我们发现DEPT表的DEPTNO字段是唯一的。于是我们需要通过如下语句为该字段创建主键:
我们再回过头来看执行计划,会发生变化吗?
ALTER TABLE DEPT ADD CONSTRAINT PK_DEPT PRIMARY KEY(DEPTNO);

如果此时的你还不能看出问题,那么我们就对比下DEPT表的主键创建先后执行计划的变化:
sql
俗话说:不比不知道,一比吓一跳。DEPT莫名其妙的被ORACLE优化器弄“丢”了。这不禁让人怀疑:这样的裁剪是否是不负责任的?也就是说,裁剪后的结果是否会因为裁剪而发生变化?在深入了解到LEFT JOIN的原理及模型结构后,你就会明白为何ORACLE优化器在DEPT表创建了基于DEPTNO字段的主键后,会做这样的裁剪。
支持ORACLE做如此大胆裁剪的理由是:
1、LEFT JOIN在没有where条件过滤的时候,是不会减少结果数据量的;
2、如果被关联的字段是被关联表的主键(或者唯一性字段),那么是不会使结果数据量增多的。
既然结果集的数据量不增加也不减少,那为何还要多访问一个表,多做一次关联呢?这就是ORACLE的精明之处:简单的就是高效的。
接下来,我们继续上面的实验(当然是基于上面的模型基础,即在DEPT表上创建了基于DEPTNO字段的主键)。这次,我们将LEFT JOIN改成INNER JOIN,看看执行计划是怎么样的:
sql
表结构和约束关系没有发生变化,消失的DEPT又回来了。
神马原因呢?LEFT JOIN是不会有数据过滤的作用的,但是INNER JOIN则有过滤的功用。
为了验证,我们准备如下数据:
sql分析
现在来看看LEFT JOIN和INNER JOIN的不同结果:
也就是说,LEFT JOIN和INNER JOIN还是有差异的,那么在什么情况下才能在执行计划中将DEPT“枪毙”掉呢?


我们对EMP和DEPT创建一个主外键约束(在创建主外键约束前,我需要删除掉empno=’003’的记录):
看看效果如何:
这样是不是已经非常明确了DEPT再度消失的原因了?因为创建了主外键,也就是等于说EMP所有的DEPTNO必须要存在DEPT表中,既然有这样的约束,那自然就不需要多此一举的关联DEPT表了。
现在我们往EMP表里面再添加一条数据:
再看看INNER JOIN的结果:
结果只有3条数据,明显刚才新增的数据是被过滤掉了,因为他的DEPTNO为null,其null并没有存在于dept表中。

而在执行计划里面,依然是没有DEPT表的:
也就是说该SQL就应该等价于如下SQL:



不对呀,说好的等价呢?难道是执行计划出了问题?还是我们对执行计划的理解错了?也或许是执行计划对我们隐藏了什么?


以上,我们都是在ORACLE的第三方开发工具PL SQL DEVELOPER里面查看的执行计划。现在我们换种方式,在SQL PLUS里面通过explain plan这种最原始的方式来查看执行计划,如下:

原来,在这个执行计划的内容中,明显的多出了“Predicate Information”,而在这部分信息里面,filter是:E.DEPTNO IS NOT NULL。
好吧,我们先把这个谓词放进SQL中,看看效果:
果然,我们发现,增加了这个谓词后,两个SQL又等价了。此时,我们会想:天哪,如果再遇到其他场景,会不会又不等价了?

在关联条件存在主外键关系约束的前提下,如下两个SQL是等价的:
不管你信不信,反正我信了


我们发现其Nullable属性为true,即可为null值。而如果我们将该属性值修改为false呢?


那么执行计划又会变成什么样的呢?
 
我们发现原来的“Predicate Information”不见了,也就没有了E.DEPTNO IS NOT NULL的谓词约束。至于原因,就不啰嗦了

 
在上面,我们在极力“宣传”着oracle是多么多么的智能化,而事实上,她的智能程度也是存在局限性的,比如她对SQL语句的取舍绝对的依赖于物理模型结构及约束,而一旦这种物理模型结构及约束不存在,那么ORACLE这位“巧妇”显然也只能“难为无米之炊”了。即便我们在SQL中进行了(唯一性)约束,ORACLE也会选择视而不见,比如如下SQL:


按照我们在上面的理解,由于在子查询D中,已经对DEPTNO进行了distinct处理,也就意味着在子查询D中,DEPTNO绝对是唯一性,即子查询D对整个SQL返回的结果是没有任何影响的,该SQL完全等价于如下SQL:

而事实上呢?执行计划会如我们所愿吗? 

Select count(1) from EMP E
 
这一次很让我们意外,ORACLE居然没有识别出子查询D的作用。由此看来,在某些时候,尤其是在错综复杂的业务逻辑面前,oracle往往束手无措,远没有程序员聪明,所以在ORACLE这位巨无霸面前,我们也大可不必妄自菲薄。

 
至此,我们可以为第一个主题做出如下总结:
1、ORACLE优化器为达性能之目的,会不择手段的简化Operation;
2、ORACLE优化器的手段之一就是充分利用数据库约束,包括但不局限于:唯一性约束、主外键参照性约束、Nullable约束;
3、在约束条件内,ORACLE会简化SQL,在Operation时不再重复约束;
4、因此,在日常模型设计时,应尽可能的建立约束,最大程度上减少重复约束带来的“非战斗性减员”,从而提升SQL性能
在上一节的最后示例中,为了更全面阐述问题,我们“抛弃”了在PL SQL DEVELOPER通过F5得到的执行计划,转而选择了最原始最古老的explain。因为我们发现:
这几列还不足够支撑我们了解ORACLE优化器的意图,或者说还不够让我们拼凑出ORACLE优化器对SQL改写后的全貌。至少我们还需要谓词(Predicate)。所以,一个完整的执行计划除了:访问方式(表访问、索引访问等)、连接方式(NESTED LOOP、HASH JOIN等)及访问顺序外,还应包括谓词(Predicate),通过结合谓词,我们更能还原ORACLE优化器对SQL做了哪些改动?

这几列还不足够支撑我们了解ORACLE优化器的意图,或者说还不够让我们拼凑出ORACLE优化器对SQL改写后的全貌。至少我们还需要谓词(Predicate)。所以,一个完整的执行计划除了:访问方式(表访问、索引访问等)、连接方式(NESTED LOOP、HASH JOIN等)及访问顺序外,还应包括谓词(Predicate),通过结合谓词,我们更能还原ORACLE优化器对SQL做了哪些改动?
那么我们能从谓词中发现什么呢?
 

我们都知道,在表的统计信息采集及时的场景下,如果某个索引字段存在条件过滤,而执行计划中没有通过索引访问,而是table access full。那么原因无非就是:该过滤条件值的数据量太大(比如超过全表数据量的20%),或者是SQL的写法不当(该字段上应用了函数、表达式等)。
其实,除了上述两种场景外,还有一种场景也会导致table access full。我们先来看一个非常简单的案例,我们在EMP.DEPTNO上创建一个索引,因为经常会遇到查询某个特定部门的员工信息。

CREATE INDEX EMP_I1 emp(DEPTNO);
SELECT * FROM EMP WHERE DEPTNO =14;
我们的如意算盘是通过索引EMP_I1来访问EMP表。而事实上,从执行计划看,却是table access full的访问方式:

因为在DEPT表中,DEPTNO的数据类型为NUMBER(2),在查deptno为14的员工信息时,我们会习惯性的写成:
尽管deptno=14的数据量为0,并且也没有在deptno上有任何的函数或者表达式。那么问题出在哪里呢?

我再来看看谓词:
很明显,在实际的执行过程中,DEPTNO是被TO_NUMBER函数包了一层,自然就走不了索引。那么是什么让ORACLE如此“昏庸”,以致“无事生非”的添加一个函数呢?

我们再看看EMP.DEPTNO的数据类型:
原来,EMP.DEPTNO的数据类型并没有同DEPT.DEPTNO保持一致,被设计成了VARCHAR2。

因此要想走索引,就有三种办法:将DEPTNO的数据类型修改为NUMBER(2)、创建TO_NUMBER(DEPTNO)的函数索引、将过滤条件有之前的DEPTNO=14修改成DEPTNO=’14’。
我们就看下第三种方案的执行计划:
 
这才是我们想要的执行计划,却不是我们想要的表模型。这三种方案孰优孰劣不在本次分享主题范围内,如有机会,再行讨论。
没错,这就是隐式强行转换的风险,而所有的字段隐式转换在执行计划中都会被“曝光”


隐式转换都是“无意为之”,有两种场景:其一是对过滤字段的数据类型“想当然”的认为;其二是对过滤值类型的错误判断。刚才的案例属于第一种,那么第二种又是怎么回事呢?以下是一个真实的案例:

系统中存在一个日志表,数据量非常大,我们对日志表按照日志时间(log_date)做了分区。在页面,要求强制按照log_date过滤,以命中分区而提高效率。但是分区+强制过滤并没有收到预期的性能效果,但是将同样的查询条件直接在DB中执行却非常快。
通过对比执行计划发现,通过页面调用执行时,并没有命中分区,而在访问谓词中,log_date字段过滤时,多出了函数INTERNAL_FUNCTION。也就是将log_date字段隐式强制转换成了timestamp。而导致这种问题的原因是JAVA数据类型与ORACLE数据类型之间的转换出现了问题。
最后通过JAVA传STRING到ORACLE,然后在SQL中将变量值TO_DATE成DATE类型解决。
 
我们也可以简单模拟下。比如我们在EMP中创建基于HIREDATE的索引:
我们现在要查找今年以来入职的员工信息,SQL如下:
CREATE INDEX EMP_I2 ON EMP(HIREDATE);

SELECT * FROM EMP WHERE HIREDATE > SYSDATE

而如果我们将date’2016-01-01’转换成timestamp,则执行计划会发生什么变化呢?

 
在这个案例中,如果不查看谓词,就很难找到性能的根源。


我们继续执行计划中的“谓词”,看看还能给我们哪些意外之喜?
在上个章节中,我们注意到,在查询今年入职的员工信息是,我们用了DATE’2016-01-01’。这种写法很少见诸于正式书籍中,因为这是非标准写法。那么将VARCHAR2转换成DATE的标准写法是什么呢?
执行计划会告诉你:
原来DATE’2016-01-01’被转换成了TO_DATE(‘2016-01-01’, ‘SYYYY-MM-DD HH24:MI:SS’),这就是为什么DATE只能支持YYYY-MM-DD格式的字符串的原因:
 


可见,ORACLE优化器会将SQL中一些非标准的写法转换成标准的朴实的写法。有的时候,最朴实的写法,最容易让人理解。
比如,当你拿到如下SQL时:
你会不会很懵菜?会不会去查资料,研究SOME的作用和用法?或许大半天后,你仍然被SOME\ANY\ALL弄得云山雾罩的
SELECT ENAME,SAL FROM EMP WHERE SAL> SOME(SELECT SAL FROM EMP WHERE DEPTNO=10);
现在,我们试着从执行计划去探究>SOME的含义

 
我们将子查询替换成具体的LIST(100,200,300),发现在执行计划中,谓词变成了SAL > 100,意思就是大于最小值。换言之,原来的SQL就是要查询大于DEPTNO=10部门最低工资的员工信息。




问题 1 怎么避免和减少回表呢

答案1::尽量通过索引过滤数据,过滤的数据越多,回表的成本就越低

问题2 执行计划在那里看的信息比较准,是在PLSQL看信息准些,还是SQLPLUS,还是说都一样
答案2:如果是查看真实的执行计划,就需要在ash或者通过SQL monitor看,信息会更丰富

问题3:关于 a left join b on a.id like b.id%的方式,有没有好的优化方法
答案3:这个要具体分析,最大的优化空间是砍掉like,在模型和业务逻辑上下功夫

问题4:some用在有等号的where子句里 any用在有大于小于号时
答案4:trace10046的信息更全面,但是由于门槛较高,一般的开发人员很难涉及。其实通过ash的执行计划是非常准确的,绝大多数的性能问题都可以通过ash或者monitor能完成分析。 10046、sql monitor也是可以的
10053能分析出如何计算成本,选择成本最低的执行计划      --------这些侧重点不一样

问题 5:一个查询1秒出结果,大概数据量是10万,但是我把它丢存储过程里执行,用insert into 一个表as那个查询,结果却要花5分多钟,这个是什么原因呢?

答案5: 你1s看到的是快速返回,你可以试着select count(1)看看效果。性能瓶颈是在返回10万数据的耗时


--------------------------------------ORACLE-DBA----------------------------------------

最权威、专业的Oracle案例资源汇总之【学习笔记】Oracle优化SQL语句之执行计划详细研究笔记

本文由大师惜分飞原创分享,网址:http://www.oracleplus.net/arch/1322.html

Oracle研究中心

关键词:

Oracle执行计划

Oracle SQL语句优化办法

Oracle SQL执行计划详解

如何学习Oracle SQL执行计划