CBO( Cost BasedOptimizer)优化器是目前ORACLE默认使用的优化器，它使用统计信息、查询转换等计算各种可能的访问路径成本，并生成多种备选执行计划，最终ORACLE选择成本最低的作为最终执行计划。与旧的RBO(RuleBased Optimizer)相比，更加灵活，可根据实际情况选择最佳执行路径。

但是，由于其自身非常复杂，CBO的限制以及存在的BUG非常多，这时，作为SQL开发和优化人员，应该根据CBO特性，编写高效语句，以避免踩坑CBO优化器。

本文以三类常见的SQL优化问题来探讨基于ORACLE的高效SQL编写和优化。

ORACLE中主要使用ROWNUM来实现TOP-N分页查询，分页SQL编写有如下规则：

1. 分页查询一般需要排序，内层查询需要先ORDER BY。

   
   

2. 如果查询TOP–N行，需要两层嵌套：内层先排序，在外层嵌套查询ROWNUM，并且同一层按照WHERE ROWNUM <或<=进行过滤。

   
   

3. 如果查询第M行到第N行，需要三层嵌套：内层先排序，之后外层嵌套查询ROWNUM并且将ROWNUM取别名，比如取别名为RN，且同一层按照WHERE ROWNUM <或<=进行过滤，之后最外层RN按照WHERE rn >或>=进行过滤。

分页查询优化要点

分页查询的排序是高代价操作，如果不能避免排序，则需要所有结果集查询完毕后进行排序操作后，才能进行分页选择。如果能够避免排序，则可以充分使用到ORACLE分页查询的COUNTSTOPKEY算法，比如找前100行，则只要找到100行整条语句则可结束计算，这样就可以提升分页查询效率了。很显然，高效分页查询必须做到：

1. 避免排序：通过创建索引

2. 执行计划使用COUNT STOPKEY算法，进行分页裁剪。

   
   

错误的分页SQL写法

分页SQL编写必须遵守查询前面说的3个规则，如下例是错误的分页语句写法：

这条语句查询前20行，应该使用两层嵌套规则：最内层排序，外层查询rownum，并且在同一层用whererownum<或<=进行过滤。仔细分析这条语句，发现是两层嵌套，但是不符合“并且在同一层用whererownum<或<=进行过滤”这个条件，此语句查询rownum之后取了别名rn,在最外层进行rn过滤，可以从执行计划看到，走了全表扫描：

这里的表TM_TESTX_TEMP的列DONE_DATE有索引，但是因为使用了错误的分页SQL写法，导致执行计划无法使用COUNTSTOPKEY进行裁剪（执行计划中未出现COUNTSTOPKEY），这样ORACLE需要按照条件查询所有的结果集，从而走索引COST更大，最终走了全表扫描。

正确的分页SQL写法

如果按照规则进行SQL编写，则可以完美进行高效分页，<=分页只需要2层嵌套，done_date列有索引，根据条件done_date>to_date(‘20150916’,‘YYYYMMDD’)，只获取前20行，可高效利用索引和COUNTSTOPKEY算法，改写完成后使用索引降序扫描，执行时间从1.72s到0.01s,逻辑IO从42648到59，效率提升百倍。如下所示：

语句改写为外层取rownum的同时按照WHEREROWNUM <= 20进行过滤，而不是原来的在最外层进行过滤，符合分页SQL编写规则，执行计划变为：

修改完后，可以看到根据ORDERBY DONE_DATE DESC，执行计划走了索引降序扫描，这样避免了排序，并且使用到了COUNTSTOPKEY算法，找到前20行，则SQL运算结束，从而提高效率。

表关联SQL分页优化

以上只是单表分页查询的高效SQL编写和优化思路，如果是多表关联SQL分页，也需要遵循分页SQL编写规则，优化方式同样是利用索引消除排序，并且能够使用COUNTSTOPKEY算法，很显然，要做到这些，必须以ORDERBY列所在表为驱动表，JOIN方式为NESTEDLOOPS，这样可全部走索引并且可使用STOPKEY算法进行结果集裁剪，提高效率。如下例：

这条语句是test1和test2进行半连接，并按照test1的object_id列进行降序排列，最终返回test1的前10行数据。从子查询关联条件上看，按照object_id和object_name列关联，没有额外的过滤条件，从语句结构上看执行计划应该中表test1应该是全表扫描，如下所示：

很显然，这不是最佳执行计划：没有消除排序，两表都是全表扫描，所有结果集返回后，才进行STOPKEY（SORTORDER BY STOPKEY）。前面已经说过，对于表关联的分页查询，应该用排序键所在的表为驱动表，JOIN方式为NESTEDLOOPS，并且消除排序，根据这个思想，应该在两表的object_id列分别建立索引即可：

create indexidx_test1 on test1(object_id);

create indexidx_test2 on test2(object_id);

索引创建完毕后的执行计划如下：

现在的执行计划完全符合多表关联分页查询优化思路，以test1表为驱动表，消除排序，test1和test2之间走NESTEDLOOPS，可以从执行计划上看出，ID=4的步骤虽然E-ROWS估算为69444行，但是实际只找到10行，也就结束了，最终逻辑读从原先的2184降低到15，大幅度提升了效率。

因为分页查询要利用到STOPKEY算法，就算除关联条件外没有额外的过滤条件，也可以通过索引来提升效率。

FILTER操作是执行计划中常见的操作，这种操作有两种情况：

1. 只有一个子节点，那么就是简单过滤操作。

2. 有多个子节点，那么就是类似NESTED LOOPS操作，只不过与NESTED LOOPS差别在于，FILTER内部会构建HASH表，对于重复匹配的，不会再次进行循环查找，而是利用已有结果，提高效率。但是一旦重复匹配的较少，循环次数多，那么，FILTER操作将是严重影响性能的操作，这是经常导致性能问题的原因。

NOT IN子查询中的FILTER

对于NOTIN子查询，在11g之前经常会出现性能问题，如下例SQL所示：

针对上面的NOTIN子查询，如果子查询object_id有NULL存在，则整个查询都不会有结果，在11g之前，如果主表和子表的object_id未同时有NOTNULL约束，或都未加ISNOT NULL限制，则ORACLE会走FILTER。11g有新的ANTINA（NULLAWARE）优化，可以对子查询进行UNNEST查询转换，从而提高效率。

对于未UNNEST的子查询，走了FILTER，有至少2个子节点，执行计划还有个特点就是Predicate谓词部分有:B1这种类似绑定变量的东西，内部操作走类似NESTEDLOOPS操作,执行计划如下：

可以从执行计划上看到，FILTER有子节点ID=2和ID=3，并且ID=3部分出现绑定变量:B1，这是典型的NOTIN子查询未UNNEST的执行计划，性能很差。

11g有NULLAWARE专门针对NOTIN问题进行优化（要求参数：_optimizer_squ_bottomup、_optimizer_null_aware_antijoin同时为true），如下所示：

11g中可以走HASHJOIN RIGHT ANTI NA，其中NA就是NULLAWARE的意思，逻辑读从原先的23w降低到6105，效率提升明显。如果在11g之前，针对这种SQL的优化方式有：

1. 子查询选择条件的列增加NOT NULL约束。如上SQL需要对anti_test1和anti_test2的object_id列增加NOT NULL约束。

   
   

2. 改写SQL：对子查询选择条件的列增加IS NOT NULL条件，如下所示：

3. 改写SQL：将NOT IN改为JOIN形式。如下所示：

NOT IN子查询改为JOIN的等价形式必须是外连接+子查询表对应的选择条件ISNULL。

4. 改写SQL：将NOT IN子查询改为NOT EXISTS子查询。如下所示：

以上四种方式的执行计划都是可以走HASHJOIN RIGHT ANTI的正确计划：

当然,如果NOTIN子查询的确存在NULL，可能不返回结果，这种情况下是不可以用以上方式进行等价改写的，只有在NOTIN子查询肯定会返回结果，而且执行计划出现FILTER的时候才考虑以上方式进行优化。

OR子查询中的FILTER

再来看下常见的OR与子查询连用情况，在实际优化过程中，遇到OR与子查询连用，一般都不能unnestsubquery了，这样执行计划出现FILTER，可能会导致严重性能问题，OR与子查询连用有两种可能：

1. condition or subquery

2. subquery内部包含or,如in (select … from tab where condition1 or condition 2)

如下例所示：

上面SQL的子查询关联条件包含OR，执行计划如下：

可以看到执行计划走FILTER，子查询表DBA_OBJECTS_B被全表驱动9999次，逻辑读10M，耗时35s,性能低下。其根本原因就是因为CBO对包含OR的子查询此处没有进行unnest，导致走了FILTER。当然，在不考虑改写的情况下，可以对DBA_OBJECTS_B的OBJECT_ID和OBJECT_NAME分别建立索引，从而避免对DBA_OBJECTS_B进行上万次的全表扫描来提高效率。如下：

现在逻辑读从10M变为6341，执行时间从从35s变为0.05s，这里建立索引还是没有消除FILTER，索引被执行9999次，很显然，如果ID=3的结果行数增大，索引的扫描次数就会增多，这显然是治标不治本的方式。

针对OR子查询无法unnest导致走FILTER的问题，一般需要通过改写，改写思路如下：

1. 将OR条件改为UNION或UNION ALL。

2. 根据语义改写，彻底消除OR条件。

根据以上优化指导思想，这条语句可改写为UNION形式，如下：

将OR条件改写为两条语句，使用UNION合并，最终查询COUNT(*)，执行计划如下：

现在的执行计划两个子查询语句都可以进行UNNEST，走HASHJOINSEMI，避免了FILTER操作，最终执行时间从原来的35s变为0.04s，逻辑读从10M减少为2550，子查询都是执行一次，也避免了建立索引，子查询效率依赖于驱动表结果行数的目的。

下面再看一个彻底消除OR条件的改写案例：

这里是NOTEXISTS子查询带OR条件，执行计划如下：

同样，这里的执行计划走FILTER，耗时21s,逻辑读468w，效率低下。如何彻底改写消除OR条件呢？可以使用集合运算的思路，集合运算中NOT (A OR B) 等价于NOT A AND NOT B。则可以将OR条件改写为AND条件：

执行计划如下：

将子查询OR改写为AND后，子查询可以UNNEST，走HASHJOIN RIGHT ANTI，最终执行时间从21s到0.03s,逻辑读从468w到1450，效率提升明显。

直方图与绑定变量问题是困扰SQL性能优化的一个典型问题：一方面绑定变量是为了让执行计划共享，从而减少或避免解析，但是如果一个列分布不均，传入不同的值最佳执行计划应该不一样，比如当status=’INVALID’的时候最佳执行计划是走索引，当status=’VALID’时候最佳执行计划是要求全表扫描，遇到这种情况，必须要再次窥视传入的绑定变量值，才能走正确执行计划，因此，11G引入了AdaptiveCursorSharing(ACS)来解决这个问题，但是因为BUG多，一般情况下生产库是建议关闭的。那么还能不能解决这个问题呢？在11.2及之后答案是肯定的。在11.2的时候，我们使用SQLPATCH来解决，这个类似以前的SQLPROFILE。如下例所示：

对于表T的STATUS列分布如下：

STATUS          COUNT(*)

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

VALID              72398

INVALID                1

其中STATUS列有索引，显然，传入INVALID时候应该走索引，传入VALID时候应该走全表扫描。如果关闭了ACS，则谁先执行，后面的共享前面的执行计划。如下先执行INVALID走索引：

但是再执行VALID时候，还是一样执行计划：

可以看出，现在返回行数从1行变成72396行，但是执行计划没有变，根本原因是没有再次对绑定变量进行PEEKING，PEEKING的值还是原来的INVALID。知道这点就好办了，其实ACS的本质是使用了HINTS：BIND_AWARE，那么在11.2时候使用SQLPATCH就可以了，如下所示：

11G写法如下（dbms_sqldiag_internal这个是内部存储过程，一般不建议使用）：

declare

l_sql_text clob;

begin

SELECT sql_fulltextINTO l_sql_text FROM v$sql WHERE sql_id = bbj7tdztdu843 AND ROWNUM< 2;

sys.dbms_sqldiag_internal.i_create_patch(sql_text  => l_sql_text,

hint_text => BIND_AWARE,

name      => bind_aware_bbj7tdztdu843

,description => test_sql_patch);

end ;

/

12.2及之后可以使用官方的DBMS_SQLDIAG.create_sql_patch：

DECLARE

  l VARCHAR2(32767);

BEGIN

  l :=SYS.DBMS_SQLDIAG.create_sql_patch(

   sql_id    => bbj7tdztdu843,

   hint_text => q[BIND_AWARE],

   name      => bind_aware_bbj7tdztdu843);

END;

/

在19c里测试，使用SQLPROFILE也是有效的，11g里使用SQLPROFILE无效。

这样，我们先执行INVALID，还是走索引：

但是再执行VALID，可以看到，绑定变量以及窥视了，变成VALID，走了全表扫描，通过NOTE也可以看到走了SQLPATCH。

可以看出，使用SQLPATCH特性，可以很好地解决直方图与绑定变量的问题。

总结：本文通过“分页查询优化”、“FILTER性能杀手”、“直方图与绑定变量”这三个常见问题，探讨CBO优化器的特性以及通过编写高质量SQL语句来达到提升性能的目的。SQL语句性能，涉及的因素很多，如统计信息、索引等，更为重要的是，熟知优化器特性，从而能够编写与CBO优化器特性相匹配的SQL语句，这样才能使用到CBO优秀的特性，保证SQL语句的执行性能。