当前位置:Gxlcms > mysql > 【我的技术我做主】oracle调优笔记(揭开传言的面纱)

【我的技术我做主】oracle调优笔记(揭开传言的面纱)

时间:2021-07-01 10:21:17 帮助过:31人阅读

【我的技术我做主】oracle调优笔记(揭开传言的面纱) 一、oracle的不解之缘 别人高考报志愿,都是因为热爱那门专业,所以选择了大学的专业。还有些人报志愿是看到了未来长远的发展比较好,所以选择了大学的专业。而我呢高考志愿是如何选择的呢?家里人没啥文

【我的技术我做主】oracle调优笔记(揭开传言的面纱)

一、oracle的不解之缘

别人高考报志愿,都是因为热爱那门专业,所以选择了大学的专业。还有些人报志愿是看到了未来长远的发展比较好,所以选择了大学的专业。而我呢高考志愿是如何选择的呢?家里人没啥文化,父母全是普通的老百姓,自然也没有人帮我参考报啥专业。于是和母亲商量上网查查吧,哪个专业比较好?搜着搜着,看到了一条“某互联网公司招聘数据库专业人员,年薪10W”,我毫不犹豫的报了我的大学专业《数据库设计与开发》。现在回想起来,我自己都觉得可笑,就因为那1条招聘信息,我选择了我的大学专业。从此我便不知不觉的踏上了数据库的贼船,走上了这条不归路i_f32.gif

在网络中不断学习的过程中,我发现了个问题,网上的资料好多都是近亲,更有甚者有些资料还是双胞胎兄弟,一模一样啊,标点符号都不差,但它们真的全都标注着“原创”。前面的文章还好,最可气的是有些文章,写得这个详细、排版这个漂亮,给人第一感觉就是,按着一步一步做吧,肯定没有问题。但是当你花了九牛二虎之力,实验就是做不出来,肯定会有些小问题,这样的文章给我这样一个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版本中

创建演示用表:

wKiom1XHCeiyspfpAAJKYJXfpOM242.jpg

打开执行计划:

set autotrace traceonly

(1)、SQL>select * from dongbeiren where deptno NOT IN (select deptno from lipengfei);

wKioL1XHDFPi7ZmWAAVztIEdqz4314.jpg

(2)、SQL> select * from dongbeiren where not exists (select deptno from lipengfei where lipengfei.deptno=dongbeiren.deptno);

wKioL1XHDHXg-77XAAViJ5l80ME191.jpg

结论: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;

wKiom1XHCvnClTnqAAW62iVvkJk785.jpg

结论:oracle10g中,在使用in时,指定条件字段不为null时,使用in和exists性能是一样的,因为它们走的全是ANTI的算法。

(二)、oracle11g版本中

创建演示用表:

wKioL1XHHLniccqpAAJxy3gC3zo573.jpg

打开执行计划:

set autotrace traceonly

(1)、SQL>select * from dongbeiren where deptno NOT IN ( select deptno from lipengfei );

wKiom1XHC47BMhz1AAOjIZAUEZU552.jpg

(2)、SQL> select * from dongbeiren where not exists ( select deptno from lipengfei where lipengfei.deptno=dongbeiren.deptno);

wKiom1XHDIjSerF-AAPe5m8k3Vg520.jpg

结论:oracle11g中,in和exists都可以用到anti算法,执行计划一样,性能一样。

2、网络上一直流传着“表的连接顺序”会影响SQL性能??这是真的吗??

(一)、oracle10g版本中

创建演示用表:

wKiom1XHDXPyUdXCAAKB1kwpDiQ183.jpg

打开执行计划:

set autotrace traceonly

(1)、SQL> select count(*) from lipengfei_big,lipengfei_small;

wKioL1XHD52SmW-HAAUSzqScZ9I162.jpg

(2)、SQL> select count(*) from lipengfei_small,lipengfei_big;

wKioL1XHD72TW33AAAT5fltq2S8258.jpg

结论:oracle10g中,在做表关联时,大表和小表的连接顺序对性能没有影响,是一样的。

(二)、oracle9i版本中(没有9i的实验环境,在10g中使用oracle hint模拟9i环境的RBO优化器)

打开执行计划:

set autotrace traceonly

(1)、SQL> select /*+rule*/ count(*) from lipengfei_big,lipengfei_small;

wKiom1XHDjHiRN6SAAP9Zc6Bf_I889.jpg

(2)、SQL> select /*+rule*/ count(*) from lipengfei_small,lipengfei_big;

wKiom1XHDl6RiF5jAAPbBs2V4qY894.jpg

结论:oracle9i中,显然上一条SQL性能好于下一条SQL,表连接顺序的说法有点过时了,那是基于规则的RBO时代的。oracle10g开始此说法就无效了,因为CBO的时代来了。

3、网络上一直流传着“count(列)比count(*)”更快??这是真的吗??

(一)、基本操作

创建演示用表:

wKiom1XHDtySPPNhAAKH74TwhZo520.jpg

打开执行计划:

set autotrace traceonly

(1)、SQL> select count(*) from lipengfei;

/

wKioL1XHEPmAaq-JAAQKNKoxPkQ342.jpg

(2)、SQL> select count(object_id) from lipengfei;

/

wKiom1XHDxWC7HXXAARJAuqFejM604.jpg

结论:从上面的执行计划来看,count(列)和count(*)是一样快的。

在object_id字段上创建索引:

(3)、SQL> create index idx_object_id on lipengfei(object_id);

wKioL1XHEV7jlaVJAACSrwVhpjQ282.jpg

(4)、SQL> select count(*) from lipengfei;

/

wKiom1XHD33hKBFAAAQKNKoxPkQ529.jpg

(5)、SQL> select count(object_id) from lipengfei;

/

wKiom1XHD5rDwzm1AARWrNV0pEU796.jpg

结论:现在使用COUNT(列比COUNT(*)要快,因为COUNT(*)不能用到索引,而COUNT(列)可以???

在object_id字段上增加非空约束:

(6)、SQL>alter table lipengfei modify object_id not null;

wKiom1XHD92Tuj9hAABi6eLZfOg261.jpg

(7)、SQL> select count(*) from lipengfei;

/

wKioL1XHEe3AxI9NAAQesmL46qI008.jpg

(8)、SQL> select count(object_id) from lipengfei;

/

wKioL1XHEgPxTuySAAQ0W87SIvc194.jpg

结论:其实count(列)和count(*)一样快,当索引列是非空的,count(*)可用到索引,此时一样快!真的如此吗???

(二)、额外赠送:count()用法注意

我想说的是:count(列)和count(*)两者根本没有可比性,这两个语句根本就不等价,性能比较首先要考虑写法等价!!!请看下面的小例子:

创建演示用表:

wKiom1XHEHvz5hq4AABTyDHrL38992.jpg

查看lipengfei表中数据情况(共14条):

wKioL1XHEovwf8AlAAQPn85TMPA349.jpg

(1)、SQL> select count(*) from lipengfei;

wKiom1XHEKrhDSCYAABPrD3p6Uw930.jpg

(2)、SQL> select count(COMM) from lipengfei;

wKiom1XHEMeDVuW-AABYYldyyXE419.jpg

结论:当使用count(列)统计时,null值不参加统计,count(*)统计当前表中有多少条记录。所以我说count(列)和count(*)两种写法,根本就不等价。

4、有些朋友喜欢把一些功能封装成自定义function,以达到代码的复用、SQL语句书写整洁,但是这样真的好吗???

(一)、基本操作

创建演示用表并初始化:

wKioL1XHE2-iZK9_AAKaPsZ_rAY813.jpg

创建函数(通过性别id,返回对应性别名称)

wKioL1XHE4WS7-uHAAFPySOS1LQ223.jpg

打开执行计划:

set autotrace traceonly statistics

以2种方法实现:查询people表信息,同时通过sex 表,得到对应人员的性别信息:

(1)、SQL> select sex_id, xing || ' ' || ming xingming, get_sex_detail(sex_id) xingbie from people;

/

wKiom1XHEaLDvFMHAAI9eNMt84o979.jpg

(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;

/

wKioL1XHE7aDQ03eAAJongMOay4997.jpg

结论:在SQL中如果调用自定义function,很明示比较影响性能,增加了逻辑读。如果可能的话,尽量不要在SQL中调用自定义function。世上没有什么是绝对的,如果必须要用自定义function实现一些功能,那么也可以使用自定义function,均衡利益后,决策权在你手中兄弟!

5、ResultSet中真的需要返回表中的全部字段吗?

创建演示用表:

wKioL1XHFBTRAR4BAABNrJAtpKo615.jpg


创建复合索引

wKiom1XHEiGwWPlyAAB4AeJtxbA736.jpg

打开执行计划:

set autotrace traceonly

(1)、(结果集中只包含object_id,object_type两个字段)

SQL> select object_id,object_type from lipengfei where object_id=28;

wKioL1XHFD3BwfPZAARVZ4xbZtc379.jpg

(2)、(结果集中包含表的所有字段)

SQL> select * from lipengfei where object_id=28;

wKioL1XHFEvzSAJHAASXmbG9LNs842.jpg

结论:很显示(1)的性能要更好些,而且没有回表(TABLE ACCESS BY INDEX ROWID),直接从Index中拿了结果集(结果集中的字段正好是索引的组合字段)。所以查询之前请考虑一下,ResultSet中真的需要返回表中的全部字段吗???

6、分区表一定比普通表效率好吗????

创建演示用分区表并初始化:

wKiom1XHEtPDjnL2AAOy8yoP0Rw442.jpg

在分区表的2个字段上,分别创建索引

wKioL1XHFN_DILtkAADMxw07oxs122.jpg

创建演示用普通表并初始化:

wKiom1XHEvLxdr06AAE1LZuLdqw065.jpg

在普通表的2个字段上,分别创建索引

wKiom1XHEwGgEkPTAADO12jWc0c065.jpg

打开执行计划:

set autotrace traceonly statistics

(1)、SQL> select * from part_lipengfei where col2=8 ;

wKioL1XHFROSz5LiAAHtzbae37Q497.jpg

(2)、SQL> select * from norm_lipengfei where col2=8 ;

wKioL1XHFSDS4fNxAAHvEVmvMdU728.jpg

(3)、SQL> select * from part_lipengfei where col3=8 ;

wKiom1XHEy7Q_qtkAAHsLcZSFZE055.jpg

(4)、SQL> select * from norm_lipengfei where col3=8 ;

wKioL1XHFT3QQnt0AAHm5uHagBw281.jpg

结论:世上没有什么是绝对的,通过上面的实验,不难看出分区表没有普通表效率好。

7、MAX()和MIN()的索引调优

创建演示用表:

wKiom1XHE8vjpqHkAABisClc3VY126.jpg

设置OBJECT_ID字段为主键:

wKioL1XHFeCxMpTkAAEOJuaIO8Q410.jpg

打开执行计划:

set autotrace on

2种方法查询出lipengfei表中的最大object_id、最小object_id:

(1)、SQL> select min(object_id),max(object_id) from lipengfei;

wKioL1XHFkDhfGLAAAR73SRH2Os135.jpg

(2)、SQL> select max, min from (select max(object_id) max from lipengfei ) a,(select min(object_id) min from lipengfei) b;

wKiom1XHFFuwmt3wAAXWHaLgd-I214.jpg

结论:有的时候,只是简单的变换一下写法,SQL的性能就不一样了,很显示(2)要比(1)性能要好很多。

8、索引存在的意义就是加快查询速度,但它是把双刃剑

创建演示用表:

wKioL1XHFuSCsKveAAGanGYld1A685.jpg

创建索引(lipengfei1表上6个单列索引,lipengfei2表上2个单列索引,lipengfei3没有索引):

wKiom1XHFQWS_QNjAALUEpSUD_U414.jpg

打开操作时间:

set timing on

(1)、insert into lipengfei1 select * from lipengfei_temp;

commit;

wKiom1XHFRajI8CnAAC3CZO2lZ4269.jpg


(2)、insert into lipengfei2 select * from lipengfei_temp;

commit;

wKioL1XHFyPxECzeAACx27NueGg019.jpg


(3)、insert into lipengfei3 select * from lipengfei_temp;

commit;

wKiom1XHFTKAM6zvAAC4ywlPuDY648.jpg

结论:可看看出,表上的索引越少,insert操作越快。索引的建立是按需求来的,不可随意建立,索引是把双刃剑。

9、如何使用count()统计,速度最快????

创建演示用表:

wKioL1XV4o3jMF_YAAHxdupinl0654.jpg

往演示表中填充数据

wKiom1XV4KHyNH1gAAGoEEV-Hfw432.jpg

分析表的静态统计信息、查看表的行数、占了多少个数据库块

wKioL1XV4tHzCIuaAAE-pFQWNPA797.jpg

依次访问count(*)、count(字段1)....count(字段n)的执行速度

wKiom1XV4NzRiGX0AANYWCXfHDQ249.jpg

结果如下图

wKioL1XV4wKxgCucAAHM8STbh_U763.jpg

结论:列的偏移量决定性能,当访问位置越靠后的列,访问的开销越大。count(*)的算法与列偏移量无关,所以count(*)最快。

10、sql调优过程中提到避免"回表",具体是指什么意思????

创建演示用表及索引

wKioL1XZmaSxcEsrAADOZr1jZDE842.jpg

打开执行计划:

set autotrace traceonly

(1)、select object_id,object_type from lipengfei where object_id=28;

/

wKiom1XZl9XjHZ4lAAQln3gYKas659.jpg

(2)、select * from lipengfei where object_id=28;

/

wKiom1XZl_uQWXcbAARch0mpG1U688.jpg

结论:

(1)、执行完sql后取结果集时,要回到表中查一下,比完全从索引中查多了结果集一步。

(2)、与第(1)情况相反,反之如果字段能从索引中全部获取那就不会回表。

(3)、在指执行计划里显示的"TABLE ACCESS BY INDEX ROWID"。

(4)、虽然只查询索引里的列,但是需要回表过滤掉其他行。


结束语:

为什么有那么多大牛,有些问题他们一眼就可以定位?我觉得那些所谓的一眼定位问题,多数是他们之前做过类似的活。所谓经历的多了,懂得也就多了。当你有了足够的知识量时,你也可以很轻松的处理问题。但世上有一种病叫做拖延症,这种病已经漫延了全世界所有人类的身上i_f30.gif有的时候你想做事和你去做事,这是两码事!所以我想说本篇文章:

送给喜欢写SQL、对SQL感兴趣的朋友们、

送给正在学习Oracle开发方向的朋友们、

送给想做数据分析的朋友们、

送给对数据分析感兴趣的朋友们、

送给想和做总是不同步的朋友们、

送给想学习的朋友们、

送给和我一样想靠技术多挣工资的朋友们t_0028.gif

附:本篇文章中的代码,全部手工测试过没有问题。如果朋友们在操作过程中发现报错,请好好检查一下代码。

本人非常喜欢和大家分享我所学到的知识,希望可以交到更多的朋友,特别感谢一直关注我博客的新老朋友们!


51cto十周年博客活动正在进行,你也来参加吧~

活动地址http://51ctoblog.blog.51cto.com/26414/1679643


人气教程排行