SQL优化是数据优化的重要方面，本文将分析Oracle自身的CBO优化，即基于成本的优化方法。Oracle为了自动的优化sql语句需要各种统计数据作为优化基础。外面会通过sql的追踪来分析sql的执行过程，消耗的资源信息。对于数据库的性能问题往往是在系统部署一段时间之后出现的，即大量用户开始使用该系统，系统的数据处理量和各种计算复杂性增加的时候，这个时候往往会追溯到系统的初始设计阶段，所以我们还是要在编码阶段就编写高效的sql语句。我在网上看到了很多关于sql优化的文章，但是不尽人意，有的很笼统的描述有的根本还是错误的方法，所以我重新将我的学习过程分享出来。
一、SQL查询处理过程详解
查询处理与查询优化是两个相关联的概念，查询处理时执行SQL语句获取数据的过程，而查询优化是通过分析SQL语句以及其他资源获得最佳执行计划的过程。在这里最佳的执行计划。我指的是消耗资源最少的计划，例如包含有数据库服务器的CPU和系统I/O。一条SQL 的执行分为3个阶段：语法分析阶段、语句优化阶段、查询执行阶段。
1.1 语法分析阶段
语法分析是在SGA中完成的，（SGA是指系统全局区，包括数据库缓冲区、重做日志缓冲区、共享池、java池、大池、流池），在这里将sql语句分解为关系代数查询，也就是通过这些关系代数查询来验证这个sql的语法有没有写错，关键字是否正确等。
1.2 语句优化阶段
这是这3个步骤中最关键的一个地方了，oracle默认使用的是基于CBO来选择最好的执行计划，你可能会问，啥是CBO？，好吧！CBO其实就是基于成本的优化程序，也就是会将对成本消耗评估，将消耗的cpu执行周期、内存、I/O速率等资源转换为时间成本。时间最少的当然就是最好的了。例如Oracle的解析也分为硬解析和软解析， 对于不同的oracle版本，硬解析的次数也不同，在oracle12中，硬解析的次数为19次，在oracle11g中硬解析的次数为59次。
在做这个阶段，Oracle会将语法分析树转换为一个逻辑查询，然后将逻辑查询转换为物理查询计划。而且这个物理查询计划还不止一种，因为优化器往往会生成好几个有效的查询计划，然后会根据这些计划来做出成本消耗评估。注意，这里只是做义工评估，并没有把每一种计划都去执行一遍。那么oracle是依据什么来评估的呢？一般会按照如下因素进行评估：a、查询中涉及的连接操作以及连接顺序 b、操作执行的算法 c、数据读取的方式，例如读内存还是磁盘 d、查询各操作之间的数据传递方式。
一条sql语句进来，到最终对sql语句生成执行计划之前，需要经历一个过程，如下图所示（嗨呀，随手画的图， 画得比较丑呀！）
1.3 查询执行
查询执行时最简单的一个步骤了，只需要将刚才步骤2的物理查询计划进行执行即可，然后将处理的数据返回给用户。
二、基于成本的优化
2.1 优化方式
优化方式的含义是为满足SQL优化的目标而选择的优化方式，在默认情况下，是以SQL语句的吞吐量作为优化的目标。
下面提供三种优化方式来满足不同的查询需求：
1、All_Rows：默认方式，优化的目标是实现查询的最大吞吐量
2、FIRST_ROWS_n：优化输出查询的前n行数据，目标是满足快速的响应需求
3、FIRST_ROWS：使用CBO的成本优化尽快输出查询的前几行数据，满足最小响应时间的需求
oracle提供了三种级别上的优化：实例级、会话级、语句级。
查询当前数据库的CBO优化方式：
可以看出我当前的数据库的优化方式是实现查询的最大吞吐量。
2.2 优化器工作过程
CBO通过4个步骤步骤完成SQL的优化
1、根据统计数据转换SQL语句 ： 也就是指CBO认为转换后的语句查询会更高效，所以将你的sql语句转换为另外一种形式，例如你写的OR转换为 UNION ALL，将between转换为>=和<=等。
2、根据资源情况选访问路径：指访问某个路径的数据所消耗的资源。
3、根据统计数据选择连接方法： 如果涉及多个表，CBO会根据统计数据以及表的键的信息来选择连接的方法，在多个连接方法中选择计算成本最低的一个作为最佳连接方法。
4、确定连接次序：指涉及的数据行的数目来确定最好的连接次序。
2.3 统计数据
--查看gather_stats_job的当前运行状态
--查询用户scott拥有表的统计分析情况：sample_size表示采样行数
select last_analyzed，table_name，owner，num_rows，sample_size from dba_tables where owner='SCOTT'；
--为模式scott的所有表统计数据(手工收集)
execute dbms_stats.gather_schema_stats(ownname => 'scott')；
三、主动优化SQL语句
3.1 优化查询
1、优化查询：explain，对于使用索引查询，使用like的时候只有%不在第一个位置才会有效，使用多列查询的时候，只有查询条件中使用了这些字段中的第一个字段时，索引才会被引用，or查询条件时，前后两个条件中的列都是索引时，查询中才会使用索引。
2、优化数据库结构，将字段很多的表分解为多个表，增加中间表，增加冗余字段，优化插入速度，禁用唯一性检查，使用批量插入，禁止外键检查，禁止自动提交，优化表optimize
3、优化数据库的服务器，硬件：内存，io， 优化参数。
4、使用绑定变量：我们都知道，在Oracle中是分为了硬解析和软解析的，在SGA中，共享池就是存放解析后的SQL语句，此时的共享池包含SQL语句的最终执行计划。如果有相同的是SQL查询语句，就不需要再次解析SQL语句了，而是直接从共享池中执行SQL语句的执行计划。使用共享池就是为了避免硬解析的发生，因为每次去进行硬解析的时候都需要重新去分析语句的语法语义，然后通过CBO优化生成的最终执行计划，这样就很消耗CPU的资源。使用绑定变量，也就是我们在java开发中常见的给一个sql语句加一个？来执行，然后再传入参数。
例如： select ename，job，sal from scott.emp where deptno=？
然后我们再把参数传入，这样不仅可以防止SQL注入，而且可以对SQL进行优化。
5、消除子查询：对于一些嵌套的子查询，将嵌套的sql语句，例如：
这样的一条sql语句每次需要执行N*M次操作，具体数值你可以使用下文中是sql跟踪进行性能分析。
优化后的语句为：
优化后的这条sql只需要进行N+M此操作即可，其伸缩性更强，计算结果也不会呈指数增长。虽然初步看起来优化后的sql语句似乎更长一点，如果你在质疑到底对不对，你可以使用我们接下来讲到的SQL语句分析工具来进行对比，大家可以通过其执行计划来验证。
3.2 SQL语句优化工具
使用explain plan for 指令来获得SQL语句的执行计划，所以我们先来创建一个执行这个指令所需要的表，在oracle的安装目录中，我们需要找到utlxplan.sql这个文件，然后执行。我这里的这个文件的路径位于E：\oracle\app\product\11.2.0\dbhome_1\RDBMS\ADMIN\utlxplan.sql，执行命令如下：
表已创建
查看这个表结构：
然后我们通过这个命令来分析SQL语句的执行：
SQL> explain plan for
2 select count(*) from scott.emp；
Explained
我们来查看一下plan_table表中的sql语句执行计划信息：
我们可以看到，这是一个全表扫描的，表明是emp。
如果我们想要更深入的对这条sql进行分析怎么办，例如想要知道这个的访问对象、消耗的CPU等信息。那么我们可以启用SQL追踪。
1、使用autotrace指令
使用该指令可以跟踪SQL语句并分析其执行步骤，统计信息如物理读数据量、磁盘和内存排序数据量。
具体的操作命令如下：
来看一下这个生成好的文件（部分内容，因为生成的内容比较多，所以这里不完全贴上来，需要查看的朋友可以自己去执行一个sql追踪然后查看）：
在这段输出中，可以看出，SQL语句被执行了38次，总共耗时0.01秒，语句被执行了48次，话费时间是0.17秒，在解析和执行期间没有磁盘I/O和缓冲区读取操作，fetch操作执行了70次，耗时0.09秒，涉及了9次磁盘读取以及171次缓冲区读取操作，总共读取了0个数据库块，涉及50行数据。
在库缓存中丢失的命中次数是22次，说明有22次硬解析出现。最后说明是47个用户SQL语句，42个内部SQL语句总共涉及89个SQL语句。
四、被动优化SQL
在程序打包后，或者系统运行后如何来优化SQL语句，一般就是建立或删除索引、建立分区表等操作，下面指给出一些思路，具体的实现还是需要在实际工作中才能领会。
1、使用分区表
2、创建压缩表：原理就是，将表中重复的数据去掉，采用算法来替换这些重复的值，在需要的时候，用算法去重建这些重复的数据，从而实现对表的压缩。
语句为；
3、创建压缩索引：原理同压缩表，主要就是去掉索引中的重复值，尤其对于大表，可以减少存储空间并增强查询性能。
语句为：
4、保持CBO的稳定性，创建存储大纲，分为三种； 数据库级别的存储大纲、会话级别的存储大纲、SQL语句级别的存储大纲
5、使用V$SQL视图
例如可以查询消耗磁盘I/O最多的语句，缓冲区读取次数最多的SQL语句等。
--查询自实例启动以来磁盘IO最多的sql语句
五、索引类型及使用时机
说到数据库的优化，不得不提的就是索引了，下面详细来讲解一下oracle的索引类型及其使用时机。
1、B-树索引
B-树索引是Oracle默认的索引类型。叶子节点包含索引的实际值和该索引条目的行ID。分为根节点、分支节点、叶子节点3个部分，其中根节点位于索引的最顶端。在叶子节点中存储了实际的索引列的值和该列对应的记录的行ID，它是唯一的Oracle指针，指向该行的物理位置，叶子节点其实就是一个双向链表，每个叶子节点包含一个指向下一个和上一个叶子节点的指针，这样在一定范围内便利用索引以搜索需要的记录。
2、位图索引
位图索引使用位图标识索引的列值，它适用于没有大量数据更新、删除和插入操作的数据仓库。因为使用位图索引时，每个位图索引项与表中大量的行有关联，当表中有大量的增删改操作的时候，位图索引页需要相应的改变，而且索引会占用一定的磁盘空间，并且索引在更新的时候受影响的索引行需要锁定。
例如我们执行如下语句：
SELECT EMPNO，ENAME，job，SAL FROM scott.emp WHERE JOB='SALESMAN'；
目的就是在emp中查出职位为salesman的员工信息，这里我们为其建立位图索引，结构如下图所示（纯手工绘图）：
创建位图索引的语句为：
create bitmap index emp_job_bitmap_idx on emp(job)；
3、反向键索引
是值在创建索引过程中对索引列创建的索引键值的字节反向，使用反向键索引的好处是将值连续插入到索引中时反向键能避免争用。使用反向键索引使得每个键值被颠倒了顺序，将索引的键值分散开。
例如：
46892 ----> 29864
Horoscope ---> eposcoroH
创建反向键索引需要使用reverse关键字。
create index emp_sal_reverse_idx on emp(sal) reverse；
4、基于函数的索引
用户查询时，如果查询语句的where子句中有函数存在，oracle将使用函数索引加快查询速度。
create index dept_dname_idx on dept9UPPER（dname)）；
如上所示，我们创建了一个基于表dept中列dname的函数索引，创建该索引时首先将列dname中的值转换为大写，然后对大写的dname创建索引，放入索引表。资源当用户需要进行如下查询的时候就会极大的提高查询速度。
select UPPER(dname) from scott.dept where UPPER(dname) ='SALES'；
六、SGA详解
Oracle的SGA是指系统全局区，它包括数据库缓冲区、重做日志缓冲区、共享池、java池、大池、流池。要优化SGA就是要调整这些数据库组件的参数 ，这些组件就是实例优化的操作对象，从而提高系统的运行效率，如提高用户查询的响应事件等。
数据库缓冲区：存放用户从库中读取的数据，用户查找数据会先在这里进行查找，如果没有才会去读数据库文件，所以该区域的设置不能过小。
重做日志缓冲区：这里放置用户改变的数据，所有变化了的数据和需要回滚的数据都暂时保存在这里。
共享池：包括数据字典高速缓存和库高速缓存，库高速缓存存放oracle解析的SQL语句、PL/SQL过程、包以及各种控制结构，如表、库缓冲句柄等。
java池：执行java代码的区域，是为运行JVM分配的一段固定大小的内存。
大池：该内存区提供大型的内存分配，在共享服务器连接模式下提供会话区，在使用RMAN备份是也使用该内存区作为磁盘IO的数据缓冲区。
流池：流内存，为oracle流专用的内存池，流是指oracle数据库中的一个数据共享。
对于数据库的优化是一个很深入的内容了，例如还有可以优化重做日志缓冲区、优化共享池优化PGA内存等方面的内容，
日志缓冲区中将缓冲写入到日志文件中的方式有每隔3秒提交、数据大于1MB的时候、检验点发生时、当DBWR进程将数据库高速缓冲区中的数据写到数据文件前，日志缓冲区的优化就是调整log_buffer_pace或者将不同的文件放在不同的磁盘上以避免冲突。
PGA是一个程序全局区，可以作为大规模的数据排序，而不需要去使用虚拟内存而占用操作系统的交换区。
更为详细的内容在本文就不再说明，感兴趣的朋友可以自行查阅相关资料。学习一些SQL的底层，可以更好的修炼内功。
核心关注：拓步ERP系统平台是覆盖了众多的业务领域、行业应用，蕴涵了丰富的ERP管理思想，集成了ERP软件业务管理理念，功能涉及供应链、成本、制造、CRM、HR等众多业务领域的管理，全面涵盖了企业关注ERP管理系统的核心领域，是众多中小企业信息化建设首选的ERP管理软件信赖品牌。
转载请注明出处：拓步ERP资讯网http://www.toberp.com/
相关文章
