oracle sql优化培训

   作为一个开发人员，我们也许不会接触到复杂sql的操作，按照Tom大叔的话，我们大部分开发人员把数据库当做了一个黑盒子，而且神秘不可控制。如果老是这样的想的话，恐怕迟早有一天我们会被社会淘汰。如今，精明的DBA都在向应用靠拢了，我们这些应用程序员当然也需要加强在数据库的知识，兴许有一天你会很牛，但说起数据库你一问三不知，会被人笑道的。

   今天参加了公司组织的一个简短的oracle的培训，讲师是oracle的资深专家，讲的还可以，也顺便让我复习了一下一些基本的概念。

    当用户在客户端(sqlplus,java 程序,pl/sql)提交了一个查询后，过了一会就返回一些结果回来了，这个过程是那么神奇，那么后台(oracle server)都干了些什么了?接下来的一些过程会让我们清楚的了解到oracle做了些什么，但是在解释这些过程之请，先回顾一下基本概念。

     oracle是什么，没启动之前，他就是一推文件，数据文件，参数文件，日志文件...。启动了后就是文件+进程+内存了，这就是oracle instance。至于文件有那些文件，进程有哪些进程，内存又有那些分类，这里不详细列出了，只说一下和本文有关系的一些。

•      共享池-shared Pool。顾名思义，这个东东是oracle在运行期间共享一些东西的地方，它包括数据字典，一些参数，以及最重要的一些oracle程序。比如我们提交的查询在完成解析之前，会搜索共享池看是否有完成的查询，如果有的话，直接就用这个了。

如果在共享池中找不到和我们一样的查询，那么oracle就开始解析这个查询了，接下来oracle会做哪些事情呢？先看看这个图：

  在上图中，其中的消耗最大的是parse阶段。在上图中我不打算深入的了解具体每一步是在干什么，因为本文的目的是关于优化的。默认情况下，oracle会在parse后有一个优化器，这个优化器会去根据一些数据判断后生成一个执行计划，这个执行计划才是我们关注的东西。

   

   影响一个查询优化器的一些因素有：访问路径，join顺序，join方法。

其中访问路径有以下几种：全表扫描、rowid扫描、索引扫描、聚簇扫描、hash扫描。

join方法有以下几种： 

• Nested-loop join。内部嵌套，适合驱动表数据量不大，被驱动表有索引的连接。
• Hash join。适合一个大表一个小表的连接。
• Sort-merge join。适合没有索引，但是数据已经排好序了的。

其中hash join是一种效率相对比较高的连接方式尤其适合一个大表和一个小表连接的时候。

  更多的关于hash join参考http://www.hellodba.com/Doc/Oracle_Hash_Join.htm

  查询优化器的输出是一个执行计划，这个执行计划就是我们衡量一个查询的的相对指标，为什么说是一个相对指标呢？同样的一个业务查询，由于我们的连接方式不同，索引使用的不同会产生不同的查询计划，通过不同的查询计划可以看到谁的效率更高。但是此业务查询和彼业务查询的查询计划是没有可比性的，数据来源，数据逻辑都不一样嘛。

  查询计划是优化器的输出，这个输出是优化器自己对多种计划比较后的最优化结果。先来看看查询计划是个什么东东吧，以便有个感性的认识。

 

上图是我在pl/sql developer中一个查询计划的截图。

   查询计划就是优化器在执行sql时一系列的步骤。我们有很多种方式查看查询计划：

• EXPLAIN PLAN命令
• SQLPLUS的autotrace
• V$SQL_PLAN视图
• Automatic Workload Repository：10g的一个新工具
• SQL TRACE
• Statspack
• 以及一些客户端工具，比如pl/sql developer

  有了查询计划我们可以通过计划查看我们为表建立的索引是否管用，是否使用了我们所期待的索引，是否按照我们期待连接方式去连接，如果不一样的话那么我们就需要调整了。先解释一下上图中的解释计划。解释计划中缩进最多的是最里层，也就是最先执行的部分。

1. 使用索引IDX_EMP扫描emp表，这样获取到了rowid。
2. 使用索引IDX_EMP_S扫描emp_salary表。
3. 做一个nested loop的连接
4. 再通过rowid访问emp_salary过滤id为1001的。

这样看也许还不是很直接的感受执行计划，如果使用hints的话会直观的感受执行计划的改变。

很显然，通过hints，执行计划马上就变了，cost成本上升很快。

   更过关于hints的信息，参考http://www.dba-oracle.com/t_sql_hints_tuning.htm

 

以上的部分只是作为一个检验我们的sql的手段，真正的一些优化手段很多很多，具体根据业务而定，但是每一种手段都有他的利弊，适用条件，学习的时候切勿囫囵吞枣，否则只会越优化越差了。