Oracle中的优化器(原创)

转载

wx6323d657ef2b3 2022-09-20 17:49:43 博主文章分类：Oracle Tuning

Oracle中的RBO

Oracle的优化器有两种优化方式：

基于规则的优化方式：Rule-Based Optimization（RBO）

基于成本或者统计信息的优化方式(Cost-Based Optimization：CBO)

RBO方式：RBO自ORACLE 6版以来被采用，有着一套严格的使用规则，只要你按照它去写SQL语句，无论数据表中的内容怎样，也不会影响到你的“执行计划”，也就是说对数据不“敏感”。优化器在分析SQL语句时,所遵循的是Oracle内部预定的一些规则。比如我们常见的，当一个where子句中的一列有索引时去走索引。

RBO的规则

Rank Access Path

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

1 Single row by ROWID

2 Single row by cluster join

3 Single row by hash cluster key with unique or primary key

4 Single row by unique or primary key

5 Cluster join

6 Hash cluster key

7 Indexed cluster key

8 Composite index

9 Single-column index

10 Bounded range search on indexed columns

11 Unbounded range search on indexed columns

12 Sort-merge join

13 MAX or MIN of indexed column

14 ORDER BY on indexed column

15 Full table scan

Oracle中CBO概述

CBO方式：CBO是在ORACLE7 引入，但到ORACLE8i 中才成熟。ORACLE已经声明在ORACLE9i之后的版本中，RBO将不再支持。CPU Costing的计算方式现在默认为CPU+I/O+memory之和.可通过DBMS_XPLAN.DISPLAY_CURSOR观察更为详细的执行计划。优化器在判断是否用这种方式时,主要参照的是表及索引的统计信息。统计信息给出表的大小、有少行、每行的长度等信息。这些统计信息起初在库内是没有的，是做analyze后才出现的，很多的时侯过期统计信息会令优化器做出一个错误的执行计划,因些应及时更新这些信息。按理，CBO应该自动收集，实际却不然，有时候在CBO情况下，还必须定期对大表进行分析。

进行查询时，CBO执行了如下操作：

可以看到CBO由查询转换器、评估器和计划生成器这3个组件组成。

查询转换器

查询语句的形式会影响所产生的执行计划，查询转换器的作用就是改变查询语句的形式以产生较好的执行计划。查询转换有如下四种机制：

视图合并（View Merging）、谓词推进（Predicate Pushing）、非嵌套子查询（Subquery Unnesting）和物化视图的查询重写（Query Rewrite with Materialized Views）

视图合并：如果SQL语句中含有视图，经分析后会把视图放在独立的“视图查询块”中，每个视图会产生一个视图子计划，当为整个语句产生执行计划时，视图子计划会被直接拿来使用而不会照顾到语句的整体性，这样就很容易导致不良执行计划的生成。视图合并就是为了去掉“视图查询块”，将视图合并到一个整体的查询块中，这样就不会有视图子计划产生，执行计划的优良性得到提升。如果用户由于安全设置问题导致无法正常进行视图合并，可以使用merge any view或者merge view给用户赋予相应权限。

谓词推进：不是所有的视图都能够被合并，对于那些不能被合并的视图Oracle会将相应的谓词推进到视图查询块中，这些谓词通常是可索引的或者是过滤性较强的，从而影响视图子查询的执行计划。

非嵌套子查询：子查询和视图一样也是被放于独立查询块中的，查询转换器会将绝大多数子查询转换为连接从而合并为同一查询块，少量不能被转换为连接的子查询，会将它们的子计划安照一个高效的方式排列。

物化视图的查询重写：当query_rewrite_enabled=true时，查询转换器寻找与该查询语句相关联的物化视图，并用物化视图改写该查询语句。

“窥视”(Peeking)用户定义的绑定变量

在Oracle9i中为查询转换器增加了一个功能，就是当用户使用绑定变量时，查询转换器可以“偷窥”绑定变量的实际值。

我们知道使用绑定变量虽然可以有效的减少“硬分析”，但它带来的负面影响是优化器无法根据实际的数据分布来优化SQL，很有可能本可以走索引的SQL却做了全表扫描。“窥视”正是为了解决这个问题，但是它并没有彻底的解决，Oracle只允许第一次调用时进行“窥视”，接下来的调用即使绑定变量的值发生了变化，也仍然是使用第一次生成的执行计划，这就造成了一个错误的执行计划会被多次使用，10g中的“窥视”也是如此。然而在Oracle 11g里，对这个问题，进行了优化，使用了Adaptive Cursor Sharing，它可以产生多个共享cursor。如果结果集是90%的值，就使用cursor 1，如果是10%的值，就使用corsor 2. 在这个转换的过程中还是有可能再次产生硬解析，也因此可以产生正确的执行计划

评估器

评估器通过计算三个值来评估计划的总体成本：选择性（Selectivity）、基数（Cardinality）、成本（Cost）。

选择性：选择性是指结果集在基数中的比例，基数可以由表、视图、join或者group by语句产生。选择性是一个大于0小于1的数，0表示没有记录被选定，1表示所有记录都被选定。统计信息和直方图关系到选择性值的准确性。如：name=’Davis’，如果不存在统计信息评估器将根据所用的谓词来指定一个缺省的选择性值，此时评估器会始终认为等式谓词的选择性比不等式谓词小，如A=1的选择性小于 A>1；如果存在统计信息而不存在直方图，此时选择性值为1/count(distinct name)；如果存在统计信息也存在直方图，选择性值则为count（name）where name=’Davis’ / count（name）where name is not null。对于返回查询结果占表的行数比例比较小的话，使用索引是有效率的，如果比例较大的话，全表扫描会更快一些。

基数：通常表中的行数称为“基础基数”（Base cardinality）；当用WHERE中的条件过滤后剩下的行数称为“有效基数”（Effective cardinality）；连接操作之后产生的结果集行数称为“连接基数”（Join cardinality）；一个字段DISTINCT之后的行数称为“DISTINCT基数”；“GROUP基数”（Group cardinality）比较特殊，它与基础基数和DISTINCT基数有关，例如：group by colx则GROUP基数就等于基础基数，但是group by colx,coly的GROUP基数则大于max ( distinct cardinality of colx , distinct cardinality of coly )且小于min ( (distinct cardinality of colx * distinct cardinality of coly) , base cardinality)。

成本：就是度量资源消耗的单位。可以理解为执行表扫描、索引扫描、连接、排序等操作所消耗I/O、CPU、内存的数量。

计划生成器

计划生成器的作用就是生成大量的执行计划，然后选择其中总体成本最低的一个。

由于不同的访问路径、连接方式和连接顺序可以任意组合，虽然以不同的方式访问和处理数据，但是可以产生同样的结果，因此一个SQL可能存在大量不同的执行计划。但实际上计划生成器很少会试验所有的可能存在的执行计划，如果它发现当前执行计划的成本已经很低了，它将停止试验，相反当前计划的成本如果很高，它将继续试验其他执行计划，因此如果能使计划生成器一开始就找到成本很低的执行计划，则会大量减少所消耗的时间，这也正是我们为什么用HINTS来优化 SQL的原因之一。

CBO虽然是基于成本的优化器，但仍然允许以“时间”或者说“响应速度”为优化目标，通过设置OPTIMIZER_MODE或者对具体语句嵌入HINTS都可以指定优化目标。

在CBO下写SQL语句的注意事项

1、CBO计算各种可能“执行计划”的“代价”，即cost，从中选用cost最低的方案，作为实际运行方案。各“执行计划”的cost的计算根据，依赖于数据表中数据的统计分布，ORACLE数据库本身对该统计分布并不清楚，必须要分析表和相关的索引（使用ANALYZE命令或dbms_stats），才能搜集到CBO所需的数据。

2、使用CBO 时，编写SQL语句时，不必考虑"FROM" 子句后面的表或视图的顺序和"WHERE" 子句后面的条件顺序；ORACLE自7版以来采用的许多新技术都是基于CBO的，如星型连接排列查询，哈希连接查询，函数索引，和并行查询等。

3、如果一个语句使用RBO的执行计划确实比CBO 好，则可以通过加 " /*+ rule */" 提示，强制使用RBO。

4、使用CBO 时，SQL语句 "FROM" 子句后面的表，最好使用ANALYZE 命令分析过，如果"FROM" 子句后面的是视图，则此视图的基础表，也最好使用ANALYZE 命令分析过；否则，ORACLE 会在执行此SQL语句之前，会自动进行动态采样分析(动态采样取决于采样级别)，有可能会导致SQL语句执行缓慢。笔者个人认为，这在高并发的OLTP系统中较为常见，OLAP系统的性能则较少因为上诉原因导致。

5、使用CBO 时，SQL语句中最好不引用系统数据字典表或视图，因为系统数据字典表都未被分析过，可能导致极差的“执行计划”。但是不要擅自对数据字典表做分析，否则可能导致死锁，或系统性能严重下降。如果实在需要对数据字典进行分析，可执行如下命令：

exec dbms_stats.gather_fixed_objects_stats
6、使用CBO 时，必须保证为表和相关的索引搜集足够的统计数据。对数据经常有增、删、改的表最好定期对表和索引进行分析，可用SQL语句“analyze table xxx compute statistics for all indexes;"ORACLE掌握了充分反映实际的统计数据，才有可能做出正确的选择。
7、使用CBO 时，被索引的字段的值的数据分布，往往会影响SQL语句的执行计划。例如：表emp，共有一百万行数据，但其中的 emp.deptno列，数据只有4种不同的值，如10、20、30、40。虽然emp数据行有很多，ORACLE缺省认定表中列的值是在所有数据行均匀分布的，也就是说每种deptno值各有25万数据行与之对应。假设SQL搜索条件DEPTNO=10，利用deptno列上的索引进行数据搜索效率，往往不比全表扫描的高，ORACLE理所当然对索引“视而不见”，认为该索引的选择性不高。
我们考虑另一种情况，如果一百万数据行实际不是在4种deptno值间平均分配，其中有99万行对应着值10，5000行对应值20，3000行对应值 30，2000行对应值40。在这种数据分布图案中对除值为10外的其它deptno值搜索时，毫无疑问，如果索引能被应用，那么效率会高出很多。我们可以采用对该索引列进行单独分析，或用analyze语句对该列建立直方图，对该列搜集足够的统计数据，使ORACLE在搜索选择性较高的值能用上索引。
CBO的模式

First_Rows_n：意味着Oracle在执行SQL时，优先考虑将结果集中的前n条记录以最快的速度反馈返回来，而其他的结果并不需要同时返回。虽然返回前n条的执行计划对于整个sql执行时间有可能不是最快的，但是在返回前n条的处理上却是最快的。这种需求在一些网站搜索或者BBS分页上经常看到。
注意：oracle中还有 First_Rows这种优化器模式，但是由于灵活性较差，Oracle建议采用 First_Rows_n替代
请看下面SQL
SQL> select /*+ first_rows(10) */ * from (select /*+ first_rows(10) */ a.*,rownum r from (select /*+ first_rows(10) */ owner,object_name from t where owner='SYS' order by object_name) a where rownum<=20) where r >10
上面的SQL每次从结果集中取出10条记录，记录按字段object_name排序。需要注意的是，排序字段x必须创建有索引，否则CBO将忽略first_rows_n而使用all_rows。
当使用first_rows_n时，Oracle以最快返回前n条记录为目的，所以在处理的时候，可能后面的数据还没提取出来，前面的数据已经返回给用户了。所以对于这种分页操作，越靠前面的页，显示就欸过需要的时间越短。请看下面的例子
SQL> create table t as select * from dba_objects;
Table created.
SQL> insert /*+ append */ into t select * from t;
50468 rows created.
SQL> commit;
Commit complete.
SQL> insert /*+ append */ into t select * from t;
100936 rows created.
SQL> commit;
..........................
SQL> insert /*+ append */ into t select * from t;
807488 rows created.
SQL> commit;
Commit complete.
SQL> select count(*) from t;
COUNT(*)
----------
1614976
SQL> create index t_owner_oname_idx on t(owner,object_name);
Index created.
SQL> exec dbms_stats.gather_table_stats('HR','T',cascade=>true);
PL/SQL procedure successfully completed.
SQL> variable high number
SQL> variable low number
SQL> variable host_variable varchar2(10)

查询1至10行的数据
SQL> exec :high := 10; :low := 1; :host_variable :='SYS'
PL/SQL procedure successfully completed.
SQL> set autotrace on explain
SQL> set timing on
SQL> select * from (select a.*,rownum r from (select /*+ first_rows(10) */ owner,object_name from t where owner= :host_variable order by object_name) a where rownum<= :high ) where r > :low
OWNER    OBJECT_NAME                                       R
-------- ---------------------------------------- ----------
SYS      /1000e8d1_LinkedHashMapValueIt                    2
SYS      /1000e8d1_LinkedHashMapValueIt                    3
SYS      /1000e8d1_LinkedHashMapValueIt                    4
SYS      /1000e8d1_LinkedHashMapValueIt                    5
SYS      /1000e8d1_LinkedHashMapValueIt                    6
SYS      /1000e8d1_LinkedHashMapValueIt                    7
SYS      /1000e8d1_LinkedHashMapValueIt                    8
SYS      /1000e8d1_LinkedHashMapValueIt                    9
SYS      /1000e8d1_LinkedHashMapValueIt                   10
9 rows selected.
Elapsed: 00:00:00.01
Execution Plan
----------------------------------------------------------
Plan hash value: 2667857998
-----------------------------------------------------------------------------------------
| Id | Operation           | Name              | Rows | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |                   |    11 | 1056 |     3   (0)| 00:00:01 |
|* 1 | VIEW               |                   |    11 | 1056 |     3   (0)| 00:00:01 |
|* 2 |   COUNT STOPKEY     |                   |       |       |            |          |
|   3 |    VIEW             |                   |    11 |   913 |     3   (0)| 00:00:01 |
|* 4 |     INDEX RANGE SCAN| T_OWNER_ONAME_IDX |    11 |   341 |     3   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("R">TO_NUMBER(:LOW))
   2 - filter(ROWNUM<=TO_NUMBER(:HIGH))
   4 - access("OWNER"=:HOST_VARIABLE)
查询70000至70010行的数据
SQL> exec :high :=700010 ; :low := 700000
PL/SQL procedure successfully completed.
SQL> select * from (select a.*,rownum r from (select /*+ first_rows(10) */ owner,object_name from t where owner= :host_variable order by object_name) a where rownum<= :high ) where r > :low
OWNER    OBJECT_NAME                                       R
-------- ---------------------------------------- ----------
SYS      sun/awt/GlobalCursorManager                  700001
SYS      sun/awt/GlobalCursorManager                  700002
SYS      sun/awt/GlobalCursorManager                  700003
SYS      sun/awt/GlobalCursorManager                  700004
SYS      sun/awt/GlobalCursorManager                  700005
SYS      sun/awt/GlobalCursorManager                  700006
SYS      sun/awt/GlobalCursorManager                  700007
SYS      sun/awt/GlobalCursorManager                  700008
SYS      sun/awt/GlobalCursorManager                  700009
SYS      sun/awt/GlobalCursorManager                  700010
10 rows selected.
Elapsed: 00:00:00.31
Execution Plan
----------------------------------------------------------
Plan hash value: 2667857998
-----------------------------------------------------------------------------------------
| Id | Operation           | Name              | Rows | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |                   |    11 | 1056 |     3   (0)| 00:00:01 |
|* 1 | VIEW               |                   |    11 | 1056 |     3   (0)| 00:00:01 |
|* 2 |   COUNT STOPKEY     |                   |       |       |            |          |
|   3 |    VIEW             |                   |    11 |   913 |     3   (0)| 00:00:01 |
|* 4 |     INDEX RANGE SCAN| T_OWNER_ONAME_IDX |    11 |   341 |     3   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("R">TO_NUMBER(:LOW))
   2 - filter(ROWNUM<=TO_NUMBER(:HIGH))
   4 - access("OWNER"=:HOST_VARIABLE)
从上面的例子可以看到，返回700000至700010的数据的时间远远大于返回1至10行数据。
CBO的另一个模式是all_rows，all_rows是10g中的默认值，它将以最快的速度将SQL执行完毕，将结果集全部返回，从总体上提高查询的吞吐量。他和first_rows(n)的区别在于，all_rows强调以最快的速度将SQL执行完毕，并将所有结果集返回，而 first_rows(n)则侧重于返回前n条记录的执行时间。同样是上面的例子，我们来看看 first_rows(n)的区别 all_rows

SQL> exec :high := 10; :low := 1;
PL/SQL procedure successfully completed.
SQL> alter session set sql_trace=true ;
Session altered.
SQL> select * from (select a.*,rownum r from (select /*+ all_rows */ owner,object_name from t where owner= :host_variable order by object_name) a where rownum<= :high ) where r > :low;
OWNER    OBJECT_NAME                                       R
-------- ---------------------------------------- ----------
SYS      /1000e8d1_LinkedHashMapValueIt                    2
SYS      /1000e8d1_LinkedHashMapValueIt                    3
SYS      /1000e8d1_LinkedHashMapValueIt                    4
SYS      /1000e8d1_LinkedHashMapValueIt                    5
SYS      /1000e8d1_LinkedHashMapValueIt                    6
SYS      /1000e8d1_LinkedHashMapValueIt                    7
SYS      /1000e8d1_LinkedHashMapValueIt                    8
SYS      /1000e8d1_LinkedHashMapValueIt                    9
SYS      /1000e8d1_LinkedHashMapValueIt                   10
9 rows selected.
SQL> select * from (select a.*,rownum r from (select /*+ first_rows(10) */ owner,object_name from t where owner= :host_variable order by object_name) a where rownum<= :high ) where r > :low;
OWNER    OBJECT_NAME                                       R
-------- ---------------------------------------- ----------
SYS      /1000e8d1_LinkedHashMapValueIt                    2
SYS      /1000e8d1_LinkedHashMapValueIt                    3
SYS      /1000e8d1_LinkedHashMapValueIt                    4
SYS      /1000e8d1_LinkedHashMapValueIt                    5
SYS      /1000e8d1_LinkedHashMapValueIt                    6
SYS      /1000e8d1_LinkedHashMapValueIt                    7
SYS      /1000e8d1_LinkedHashMapValueIt                    8
SYS      /1000e8d1_LinkedHashMapValueIt                    9
SYS      /1000e8d1_LinkedHashMapValueIt                   10
9 rows selected.

SQL> alter session set sql_trace=false
Session altered.

查看trace文件如下
all_rows：
select *
from
(select a.*,rownum r from (select /*+ all_rows */ owner,object_name from t
where owner= :host_variable order by object_name) a where rownum<= :high )
where r > :low
call     count       cpu    elapsed       disk      query    current        rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse        2      0.00       0.00          0          0          0           0
Execute      2      0.00       0.00          0          0          0           0
Fetch        4      0.59       0.60          0       3826          0          19
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total        8      0.59       0.60          0       3826          0          19
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 55
Rows     Row Source Operation
------- ---------------------------------------------------
     10 VIEW (cr=3822 pr=0 pw=0 time=600823 us)
700010   COUNT STOPKEY (cr=3822 pr=0 pw=0 time=16800977 us)
700010    VIEW (cr=3822 pr=0 pw=0 time=9100729 us)
700010     INDEX RANGE SCAN T_OWNER_ONAME_IDX (cr=3822 pr=0 pw=0 time=3500528 us)(object id 52884)
first_rows(n)：

select *
from
(select a.*,rownum r from (select /*+ first_rows(10) */ owner,object_name
from t where owner= :host_variable order by object_name) a where rownum<=
:high ) where r > :low
call     count       cpu    elapsed       disk      query    current        rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse        2      0.00       0.00          0          0          0           0
Execute      2      0.00       0.00          0          0          0           0
Fetch        4      0.73       0.76          0       3826          0          19
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total        8      0.74       0.77          0       3826          0          19
Misses in library cache during parse: 2
Optimizer mode: FIRST_ROWS
Parsing user id: 55
Rows     Row Source Operation
------- ---------------------------------------------------
      9 VIEW (cr=4 pr=0 pw=0 time=783 us)
     10   COUNT STOPKEY (cr=4 pr=0 pw=0 time=805 us)
     10    VIEW (cr=4 pr=0 pw=0 time=625 us)
     10     INDEX RANGE SCAN T_OWNER_ONAME_IDX (cr=4 pr=0 pw=0 time=402 us)(object id 52884)
可以看到first_rows(n)仅仅扫描过了4个数据块，而all_rows则扫描了3822个数据块。如果将high设置为700010，low设置为700000,则会发现all_rows和first_rows(n)两者扫描的数据块数量相当接近，读者可以自行实验。