【我的技术我做主】oracle调优笔记(揭开传言的面纱)
一、oracle的不解之缘
别人高考报志愿,都是因为热爱那门专业,所以选择了大学的专业。还有些人报志愿是看到了未来长远的发展比较好,所以选择了大学的专业。而我呢高考志愿是如何选择的呢?家里人没啥文化,父母全是普通的老百姓,自然也没有人帮我参考报啥专业。于是和母亲商量上网查查吧,哪个专业比较好?搜着搜着,看到了一条“某互联网公司招聘数据库专业人员,年薪10W”,我毫不犹豫的报了我的大学专业《数据库设计与开发》。现在回想起来,我自己都觉得可笑,就因为那1条招聘信息,我选择了我的大学专业。从此我便不知不觉的踏上了数据库的贼船,走上了这条不归路。
在网络中不断学习的过程中,我发现了个问题,网上的资料好多都是近亲,更有甚者有些资料还是双胞胎兄弟,一模一样啊,标点符号都不差,但它们真的全都标注着“原创”。前面的文章还好,最可气的是有些文章,写得这个详细、排版这个漂亮,给人第一感觉就是,按着一步一步做吧,肯定没有问题。但是当你花了九牛二虎之力,实验就是做不出来,肯定会有些小问题,这样的文章给我这样一个oracle初学者留下了永恒的伤。
仔细算算,从大二的第一次oracle课到现在,大约有6个年头了。回想当初学习oracle的经历,挺有感触的。每当我写博文的时候,我尽可能的把实验内容写全、实验截图贴全。希望可以帮到需要的朋友们,这么多年从业的道路上,每当有兄弟找到我问东西的时候,只要是我会的,我都会尽可能得去帮忙。
下面进入本篇文章的主题吧,和大家分享一些oracle库中sql语句调优的小知识点吧,写得比较基础、易懂。
二、实战操作及截图说明
(一)、要说SQL调优,就必须要简单说一下,SQL语句的执行过程:
1、检查语法(检查语法,如果有错误,直接返回,省着往下执行浪费资源)
2、检查语义(有没有这个表、当前用户有没有操作权限,如果没有直接返回)
3、hash计算(把当前的sql语句转换成oracle可以理解的hash值)
4、解析
4.1、软解析(当一条sq1语句执行过,就会保存在library cache中,当再一次执行与此条sql相同的语句时,不经过cpu计算,直接调用share pool)
4.2、硬解析(当软解析不成功进,经过cpu计算)
5、执行(有顺序的)
select name from aa where id=1(先计算条件,再对比表,减少查找范围,触发索引就是先看where条件部分)
(二)、优化器
Oracle在执行一个SQL之前,首先要分析一下语句的执行计划,然后再按执行计划去执行。分析语句的执行计划的工作是由优化器来完成的。不同的情况,一条SQL可能有多种执行计划,但在某一时点,一定只有一种执行计划是最优的,花费时间是最少的。
Oracle的优化器有两种优化方式,即基于规则的优化方式(Rule-Based Optimization,简称为RBO)和基于代价的优化方式(Cost-Based Optimization,简称为CBO)
RBO方式:优化器在分析SQL语句时,所遵循的是Oracle内部预定的一些规则。比如我们常见的,当一个where子句中的一列有索引时去走索引。(10g之前默认方式)
注意:走索引不一定就是优的,比如一个表只有两行数据,一次IO就可以完成全表的检索,而此时走索引时则需要两次IO,这时全表扫描(full table scan)是最好的。
CBO方式:它是看语句的代价(Cost),这里的代价主要指Cpu和内存。优化器在判断是否用这种方式时,主要参照的是表及索引的统计信息。统计信息给出表的大小、有多少行、每行的长度等信息。这些统计信息起初在库内是没有的,是做analyze后才出现的,很多的时侯过期统计信息会令优化器做出一个错误的执行计划,因些应及时更新这些信息。(10g开始默认方式)
(三)、实验操作及理论总结(由于不同版本的oracle库,优化器的不同其底层实现的算法也不同,在实验中我会尽可能的模拟出9i、10g、11g的例子)
1、网络上一直流传着“exists比in的效率好”??这是真的吗??
(一)、oracle10g版本中
创建演示用表:
打开执行计划:
set autotrace traceonly
(1)、SQL>select * from dongbeiren where deptno NOT IN (select deptno from lipengfei);
(2)、SQL> select * from dongbeiren where not exists (select deptno from lipengfei where lipengfei.deptno=dongbeiren.deptno);
结论:oracle10g中,使用in和exists使用的算法不同,很显示exists的逻辑读要少些,性能要好一些。
(3)、SQL> select * from dongbeiren where deptno NOT IN ( select deptno from lipengfei where deptno is not null) and deptno is not null;
结论:oracle10g中,在使用in时,指定条件字段不为null时,使用in和exists性能是一样的,因为它们走的全是ANTI的算法。
(二)、oracle11g版本中
打开执行计划:
set autotrace traceonly
(1)、SQL>select * from dongbeiren where deptno NOT IN ( select deptno from lipengfei );
(2)、SQL> select * from dongbeiren where not exists ( select deptno from lipengfei where lipengfei.deptno=dongbeiren.deptno);
结论:oracle11g中,in和exists都可以用到anti算法,执行计划一样,性能一样。
2、网络上一直流传着“表的连接顺序”会影响SQL性能??这是真的吗??
(一)、oracle10g版本中
创建演示用表:
打开执行计划:
set autotrace traceonly
(1)、SQL> select count(*) from lipengfei_big,lipengfei_small;
(2)、SQL> select count(*) from lipengfei_small,lipengfei_big;
结论:oracle10g中,在做表关联时,大表和小表的连接顺序对性能没有影响,是一样的。
(二)、oracle9i版本中(没有9i的实验环境,在10g中使用oracle hint模拟9i环境的RBO优化器)
打开执行计划:
set autotrace traceonly
(1)、SQL> select /*+rule*/ count(*) from lipengfei_big,lipengfei_small;
(2)、SQL> select /*+rule*/ count(*) from lipengfei_small,lipengfei_big;
结论:oracle9i中,显然上一条SQL性能好于下一条SQL,表连接顺序的说法有点过时了,那是基于规则的RBO时代的。oracle10g开始此说法就无效了,因为CBO的时代来了。
3、网络上一直流传着“count(列)比count(*)”更快??这是真的吗??
(一)、基本操作
创建演示用表:
打开执行计划:
set autotrace traceonly
(1)、SQL> select count(*) from lipengfei;
/
(2)、SQL> select count(object_id) from lipengfei;
/
结论:从上面的执行计划来看,count(列)和count(*)是一样快的。
在object_id字段上创建索引:
(3)、SQL> create index idx_object_id on lipengfei(object_id);
(4)、SQL> select count(*) from lipengfei;
/
(5)、SQL> select count(object_id) from lipengfei;
/
结论:现在使用COUNT(列比COUNT(*)要快,因为COUNT(*)不能用到索引,而COUNT(列)可以???
在object_id字段上增加非空约束:
(6)、SQL>alter table lipengfei modify object_id not null;
(7)、SQL> select count(*) from lipengfei;
/
(8)、SQL> select count(object_id) from lipengfei;
/
结论:其实count(列)和count(*)一样快,当索引列是非空的,count(*)可用到索引,此时一样快!真的如此吗???
(二)、额外赠送:count()用法注意
我想说的是:count(列)和count(*)两者根本没有可比性,这两个语句根本就不等价,性能比较首先要考虑写法等价!!!请看下面的小例子:
创建演示用表:
查看lipengfei表中数据情况(共14条):
(1)、SQL> select count(*) from lipengfei;
(2)、SQL> select count(COMM) from lipengfei;
结论:当使用count(列)统计时,null值不参加统计,count(*)统计当前表中有多少条记录。所以我说count(列)和count(*)两种写法,根本就不等价。
4、有些朋友喜欢把一些功能封装成自定义function,以达到代码的复用、SQL语句书写整洁,但是这样真的好吗???
(一)、基本操作
创建演示用表并初始化:
创建函数(通过性别id,返回对应性别名称)
打开执行计划:
set autotrace traceonly statistics
以2种方法实现:查询people表信息,同时通过sex 表,得到对应人员的性别信息:
(1)、SQL> select sex_id, xing || ' ' || ming xingming, get_sex_detail(sex_id) xingbie from people;
/
(2)、SQL> select p.sex_id, p.xing || ' ' || p.ming xingming, sex.name xingbie from people p, sex where sex.sex_id = p.sex_id;
/
结论:在SQL中如果调用自定义function,很明示比较影响性能,增加了逻辑读。如果可能的话,尽量不要在SQL中调用自定义function。世上没有什么是绝对的,如果必须要用自定义function实现一些功能,那么也可以使用自定义function,均衡利益后,决策权在你手中兄弟!
5、ResultSet中真的需要返回表中的全部字段吗?
创建演示用表:
创建复合索引
打开执行计划:
set autotrace traceonly
(1)、(结果集中只包含object_id,object_type两个字段)
SQL> select object_id,object_type from lipengfei where object_id=28;
(2)、(结果集中包含表的所有字段)
SQL> select * from lipengfei where object_id=28;
结论:很显示(1)的性能要更好些,而且没有回表(TABLE ACCESS BY INDEX ROWID),直接从Index中拿了结果集(结果集中的字段正好是索引的组合字段)。所以查询之前请考虑一下,ResultSet中真的需要返回表中的全部字段吗???
6、分区表一定比普通表效率好吗????
创建演示用分区表并初始化:
在分区表的2个字段上,分别创建索引
创建演示用普通表并初始化:
在普通表的2个字段上,分别创建索引
打开执行计划:
set autotrace traceonly statistics
(1)、SQL> select * from part_lipengfei where col2=8 ;
(2)、SQL> select * from norm_lipengfei where col2=8 ;
(3)、SQL> select * from part_lipengfei where col3=8 ;
(4)、SQL> select * from norm_lipengfei where col3=8 ;
结论:世上没有什么是绝对的,通过上面的实验,不难看出分区表没有普通表效率好。
7、MAX()和MIN()的索引调优
创建演示用表:
设置OBJECT_ID字段为主键:
打开执行计划:
set autotrace on
2种方法查询出lipengfei表中的最大object_id、最小object_id:
(1)、SQL> select min(object_id),max(object_id) from lipengfei;
(2)、SQL> select max, min from (select max(object_id) max from lipengfei ) a,(select min(object_id) min from lipengfei) b;
结论:有的时候,只是简单的变换一下写法,SQL的性能就不一样了,很显示(2)要比(1)性能要好很多。
8、索引存在的意义就是加快查询速度,但它是把双刃剑
创建演示用表:
创建索引(lipengfei1表上6个单列索引,lipengfei2表上2个单列索引,lipengfei3没有索引):
打开操作时间:
set timing on
(1)、insert into lipengfei1 select * from lipengfei_temp;
commit;
(2)、insert into lipengfei2 select * from lipengfei_temp;
commit;
(3)、insert into lipengfei3 select * from lipengfei_temp;
commit;
结论:可看看出,表上的索引越少,insert操作越快。索引的建立是按需求来的,不可随意建立,索引是把双刃剑。
9、如何使用count()统计,速度最快????
创建演示用表:
往演示表中填充数据
分析表的静态统计信息、查看表的行数、占了多少个数据库块
依次访问count(*)、count(字段1)....count(字段n)的执行速度
结果如下图
结论:列的偏移量决定性能,当访问位置越靠后的列,访问的开销越大。count(*)的算法与列偏移量无关,所以count(*)最快。
10、sql调优过程中提到避免"回表",具体是指什么意思????
创建演示用表及索引
打开执行计划:
set autotrace traceonly
(1)、select object_id,object_type from lipengfei where object_id=28;
/
(2)、select * from lipengfei where object_id=28;
/
结论:
(1)、执行完sql后取结果集时,要回到表中查一下,比完全从索引中查多了结果集一步。
(2)、与第(1)情况相反,反之如果字段能从索引中全部获取那就不会回表。
(3)、在指执行计划里显示的"TABLE ACCESS BY INDEX ROWID"。
(4)、虽然只查询索引里的列,但是需要回表过滤掉其他行。
结束语:
为什么有那么多大牛,有些问题他们一眼就可以定位?我觉得那些所谓的一眼定位问题,多数是他们之前做过类似的活。所谓经历的多了,懂得也就多了。当你有了足够的知识量时,你也可以很轻松的处理问题。但世上有一种病叫做拖延症,这种病已经漫延了全世界所有人类的身上。有的时候你想做事和你去做事,这是两码事!所以我想说本篇文章:
送给喜欢写SQL、对SQL感兴趣的朋友们、
送给正在学习Oracle开发方向的朋友们、
送给想做数据分析的朋友们、
送给对数据分析感兴趣的朋友们、
送给想和做总是不同步的朋友们、
送给想学习的朋友们、
附:本篇文章中的代码,全部手工测试过没有问题。如果朋友们在操作过程中发现报错,请好好检查一下代码。
本人非常喜欢和大家分享我所学到的知识,希望可以交到更多的朋友,特别感谢一直关注我博客的新老朋友们!
“51cto十周年博客活动”正在进行,你也来参加吧~
活动地址:http://51ctoblog.blog.51cto.com/26414/1679643
转载于:https://blog.51cto.com/51power/1683100