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

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

【我的技术我做主】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