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语句生成执行计划之前，需要经历一个过程，如下图所示（嗨呀，随手画的图, 画得比较丑呀!）

基于CBO的SQL优化和Oracle实例优化

1.3 查询执行

查询执行时最简单的一个步骤了，只需要将刚才步骤2的物理查询计划进行执行即可，然后将处理的数据返回给用户。

二、基于成本的优化

2.1 优化方式

优化方式的含义是为满足SQL优化的目标而选择的优化方式，在默认情况下，是以SQL语句的吞吐量作为优化的目标。

下面提供三种优化方式来满足不同的查询需求：

1、All_Rows:默认方式，优化的目标是实现查询的最大吞吐量

2、FIRST_ROWS_n:优化输出查询的前n行数据，目标是满足快速的响应需求

3、FIRST_ROWS:使用CBO的成本优化尽快输出查询的前几行数据，满足最小响应时间的需求

oracle提供了三种级别上的优化：实例级、会话级、语句级。

查询当前数据库的CBO优化方式：

SQL> show parameter optimizer_mode;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
optimizer_mode string ALL_ROWS

可以看出我当前的数据库的优化方式是实现查询的最大吞吐量。

2.2 优化器工作过程

CBO通过4个步骤步骤完成SQL的优化

1、根据统计数据转换SQL语句：也就是指CBO认为转换后的语句查询会更高效，所以将你的sql语句转换为另外一种形式，例如你写的OR转换为 UNION ALL，将between转换为>=和<=等。

2、根据资源情况选访问路径：指访问某个路径的数据所消耗的资源。

3、根据统计数据选择连接方法：如果涉及多个表，CBO会根据统计数据以及表的键的信息来选择连接的方法，在多个连接方法中选择计算成本最低的一个作为最佳连接方法。

4、确定连接次序：指涉及的数据行的数目来确定最好的连接次序。

2.3 统计数据

--查看gather_stats_job的当前运行状态

select job_name,state,owner from dba_scheduler_jobs;

--查询用户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语句，例如：

select  * from  scott.emp e1  where  e1.sal>
(select avg(sal)  from  scott.emp e2 where e2.deptno=e1.deptno);

这样的一条sql语句每次需要执行N*M次操作，具体数值你可以使用下文中是sql跟踪进行性能分析。

优化后的语句为：

select *
from scott.emp e1,(select e2.deptno  ,avg(e2.sal)  avg_sal from scott.emp e2  group by deptno)  d
where e1.deptno=d.deptno and e1.sal >d.avg_sal

优化后的这条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> desc  plan_table;
SQL> desc  plan_table
Name              Type           Nullable Default Comments
----------------- -------------- -------- ------- --------
STATEMENT_ID      VARCHAR2(30)   Y
PLAN_ID           NUMBER         Y
TIMESTAMP         DATE           Y
REMARKS           VARCHAR2(4000) Y
OPERATION         VARCHAR2(30)   Y
OPTIONS           VARCHAR2(255)  Y
OBJECT_NODE       VARCHAR2(128)  Y
OBJECT_OWNER      VARCHAR2(30)   Y
OBJECT_NAME       VARCHAR2(30)   Y
OBJECT_ALIAS      VARCHAR2(65)   Y
OBJECT_INSTANCE   INTEGER        Y
OBJECT_TYPE       VARCHAR2(30)   Y
OPTIMIZER         VARCHAR2(255)  Y
SEARCH_COLUMNS    NUMBER         Y
ID                INTEGER        Y
PARENT_ID         INTEGER        Y
DEPTH             INTEGER        Y
POSITION          INTEGER        Y
COST              INTEGER        Y
CARDINALITY       INTEGER        Y
BYTES             INTEGER        Y
OTHER_TAG         VARCHAR2(255)  Y
PARTITION_START   VARCHAR2(255)  Y
PARTITION_STOP    VARCHAR2(255)  Y
PARTITION_ID      INTEGER        Y
OTHER             LONG           Y
DISTRIBUTION      VARCHAR2(30)   Y
CPU_COST          INTEGER        Y
IO_COST           INTEGER        Y
TEMP_SPACE        INTEGER        Y
ACCESS_PREDICATES VARCHAR2(4000) Y
FILTER_PREDICATES VARCHAR2(4000) Y
PROJECTION        VARCHAR2(4000) Y
TIME              INTEGER        Y
QBLOCK_NAME       VARCHAR2(30)   Y
OTHER_XML         CLOB           Y

然后我们通过这个命令来分析SQL语句的执行：

SQL> explain plan for
2 select count(*) from scott.emp;
Explained

我们来查看一下plan_table表中的sql语句执行计划信息：

SQL> col id for 999
SQL> col operation for a20
SQL> col options for a20
SQL> col object_name for a20
SQL> select id,operation,options,object_name,options from plan_table;
ID OPERATION OPTIONS OBJECT_NAME OPTIONS
--- -------------------- -------------------- -------------------- --------------------
0 SELECT STATEMENT
1 SORT AGGREGATE AGGREGATE
2 INDEX FULL SCAN PK_EMP FULL SCAN

我们可以看到，这是一个全表扫描的，表明是emp。

如果我们想要更深入的对这条sql进行分析怎么办，例如想要知道这个的访问对象、消耗的CPU等信息。那么我们可以启用SQL追踪。

1、使用autotrace指令

使用该指令可以跟踪SQL语句并分析其执行步骤，统计信息如物理读数据量、磁盘和内存排序数据量。

具体的操作命令如下：

SQL> show user;

SQL> select sid,serial# from v$session where username='SYS'

2 ;

SID SERIAL#

---------- ----------

7 1056

67 875

68 660

--开启追踪

SQL> exec dbms_system.set_sql_trace_in_session(68,660,true);

PL/SQL procedure successfully completed

--执行语句

SQL> select * from scott.emp where sal>2000;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

----- ---------- --------- ----- ----------- --------- --------- ------

7566 JONES MANAGER 7839 1981/4/2 2975.00 20

7698 BLAKE MANAGER 7839 1981/5/1 2850.00 30

7782 CLARK MANAGER 7839 1981/6/9 2450.00 10

7788 SCOTT ANALYST 7566 1987/4/19 3000.00 20

7839 KING PRESIDENT 1981/11/17 5000.00 10

7902 FORD ANALYST 7566 1981/12/3 3000.00 20

6 rows selected

--关闭追踪

SQL> exec dbms_system.set_sql_trace_in_session(68,660,false);

PL/SQL procedure successfully completed

--查询trace文件存放在那个目录

SQL> show parameter dump_dest;

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

background_dump_dest string e:\app\thinkive\diag\rdbms\orcl\orcl\trace

core_dump_dest string e:\app\thinkive\diag\rdbms\orcl\orcl\cdump

user_dump_dest string e:\app\thinkive\diag\rdbms\orcl\orcl\trace

SQL>

分析语句：使用tkprof命令

PS E:\app\thinkive\product\11.2.0\dbhome_1\BIN> tkprof E:\app\thinkive\diag\rdbms\orcl\orcl\trace\orcl_ora_14816.trc
D:\arc148.txt
TKPROF: Release 11.2.0.1.0 - Development on Tue Dec 5 16:14:58 2017
Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.
PS E:\app\thinkive\product\11.2.0\dbhome_1\BIN>

SQL> @E:\oracle\app\product\11.2.0\dbhome_1\RDBMS\ADMIN\utlxplan.sql

来看一下这个生成好的文件（部分内容，因为生成的内容比较多，所以这里不完全贴上来，需要查看的朋友可以自己去执行一个sql追踪然后查看）：

OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse       38      0.00       0.01          0          0          0           0
Execute     48      0.07       0.17          0          0          0           2
Fetch       70      0.01       0.09          9        171          0          48
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total      156      0.09       0.28          9        171          0          50

Misses in library cache during parse: 22
Misses in library cache during execute: 21

Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  db file sequential read                         5        0.04          0.05

   47  user  SQL statements in session.
   42  internal SQL statements in session.
   89  SQL statements in session.
********************************************************************************

在这段输出中，可以看出，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语句。

Trace file: E:\app\thinkive\diag\rdbms\orcl\orcl\trace\orcl_ora_14816.trc
Trace file compatibility: 11.1.0.7
Sort options: default

       1  session in tracefile.
      47  user  SQL statements in trace file.
      42  internal SQL statements in trace file.
      89  SQL statements in trace file.
      33  unique SQL statements in trace file.
    1345  lines in trace file.
     749  elapsed seconds in trace file.

四、被动优化SQL

在程序打包后，或者系统运行后如何来优化SQL语句，一般就是建立或删除索引、建立分区表等操作，下面指给出一些思路，具体的实现还是需要在实际工作中才能领会。

1、使用分区表

2、创建压缩表：原理就是，将表中重复的数据去掉，采用算法来替换这些重复的值，在需要的时候，用算法去重建这些重复的数据，从而实现对表的压缩。

语句为;

create  table compress_emp
compress
tablespace users
as
select * from scott.emp;

3、创建压缩索引：原理同压缩表，主要就是去掉索引中的重复值，尤其对于大表，可以减少存储空间并增强查询性能。

语句为：

create  index  compress_emp_ename_idx
on compress_emp(ename)
compress;

4、保持CBO的稳定性，创建存储大纲，分为三种; 数据库级别的存储大纲、会话级别的存储大纲、SQL语句级别的存储大纲

5、使用V$SQL视图

例如可以查询消耗磁盘I/O最多的语句，缓冲区读取次数最多的SQL语句等。

--查询自实例启动以来磁盘IO最多的sql语句

SQL>  select sql_text，executions,disk_reads from v$sql   where  disk_reads>&number  order by disk_reads desc;
SQL_TEXT                                                                         EXECUTIONS DISK_READS
-------------------------------------------------------------------------------- ---------- ----------
call dbms_stats.gather_database_stats_job_proc (  )                                       1       8883
call dbms_space.auto_space_advisor_job_proc (  )                                          1       4214
delete from sys.wri$_optstat_histgrm_history                                 whe          1       3688
select substrb(dump(val,16,0,32),1,120) ep, cnt from (select /*+ no_parallel(t)           1       2898
select o.name, o.owner# from obj$ o, type$ t  where o.oid$ = t.tvoid and  bitand          1       2810
SELECT space_usage_kbytes  FROM  v$sysaux_occupants  WHERE occupant_name = 'SQL_          1       2328
DECLARE job BINARY_INTEGER := :job; next_date DATE := :mydate;  broken BOOLEAN :        211       1862
begin dbms_feature_usage_internal.exec_db_usage_sampling(:bind1); end;                    1       1739
DECLARE job BINARY_INTEGER := :job;  next_date TIMESTAMP WITH TIME ZONE := :myda          2       1603
select owner, segment_name, blocks from dba_segments where tablespace_name = :ts          1       1589
select /*+ index(idl_ub1$ i_idl_ub11) +*/ piece#,length,piece from idl_ub1$ wher        458       1502
 create table "SH".DBMS_TABCOMP_TEMP_UNCMP tablespace "EXAMPLE" nologging as sel          1       1472
select count(*) cnt from "SH".DBMS_TABCOMP_TEMP_UNCMP                                     1       1438
delete from WRH$_SYSMETRIC_HISTORY tab where (:beg_snap <= tab.snap_id and                1       1412
delete from sys.wri$_optstat_histhead_history                                whe          1       1349
select substrb(dump(val,16,0,32),1,120) ep, cnt from (select /*+ no_parallel(t)           1       1270
BEGIN    prvt_advisor.delete_expired_tasks;  END;                                         2       1179
/* SQL Analyze(1) */ select /*+ full(t) no_parallel(t) no_parallel_index(t) dbms          1        989
select count(*) cnt from "SH".DBMS_TABCOMP_TEMP_CMP                                       1        755
BEGIN DBMS_FEATURE_XDB(:feature_boolean, :aux_cnt, :feature_info);  END;                  1        738
SQL_TEXT                                                                         EXECUTIONS DISK_READS
-------------------------------------------------------------------------------- ---------- ----------
 select s.synonym_name as object_name, o.object_type   from sys.all_synonyms s,           1        730
select count(*)     from xdb.xdb$resource e, sys.user$ u     where to_number(utl          1        727
SELECT count(*), sum(blocks) FROM dba_segments where    OWNER = 'SYS' and TABLES          1        724
/* SQL Analyze(1) */ select /*+ full(t) no_parallel(t) no_parallel_index(t) dbms          1        722
select OBJOID,  CLSOID, RUNTIME, PRI, JOBTYPE,  SCHLIM,  WT, INST,  RUNNOW, ENQ_          3        697
SELECT /* DS_SVC */ /*+ cursor_sharing_exact dynamic_sampling(0) no_sql_tune no_         11        686
select count(1), count(1), null from sys.view$ v where bitand(v.property, 32) =           1        683
delete from WRH$_SQL_PLAN tab where (:beg_snap <= tab.snap_id and         tab.sn          1        659
select /*+ rule */ bucket, endpoint, col#, epvalue from histgrm$ where obj#=:1 a       2743        608
begin dbms_stats.gather_schema_stats(ownname => 'scott'); end;                            1        415
SELECT /* OPT_DYN_SAMP */ /*+ ALL_ROWS IGNORE_WHERE_CLAUSE NO_PARALLEL(SAMPLESUB          2        401
31 rows selected

SQL>

五、索引类型及使用时机

说到数据库的优化，不得不提的就是索引了，下面详细来讲解一下oracle的索引类型及其使用时机。

1、B-树索引

B-树索引是Oracle默认的索引类型。叶子节点包含索引的实际值和该索引条目的行ID。分为根节点、分支节点、叶子节点3个部分，其中根节点位于索引的最顶端。在叶子节点中存储了实际的索引列的值和该列对应的记录的行ID，它是唯一的Oracle指针，指向该行的物理位置，叶子节点其实就是一个双向链表，每个叶子节点包含一个指向下一个和上一个叶子节点的指针，这样在一定范围内便利用索引以搜索需要的记录。