Performance Tuning-The Query Optimizer

SQL优化最重要的就是优化器的理解，下面笔者对官方文档自己做了一些翻译和简化，希望对大家有帮助。版本11.2。

一．      优化器环境参数

CURSOR_SHARING：EXACT|FORCE

将sql语句中的文字字符转换为绑定变量。

DB_FILE_MULTIBLOCK_READ_COUNT：

sequential 扫描时一次可以读取的块数。全表扫描所需要的IO依赖于表的大小，一次块读取量，以及是否使用了并行。系统的默认值为最大IO/db_block_size。Oracle给的建议是OLTP系统该值为4-16，DSS系统或者数据仓库应该为允许的最大值，该值越高，越会倾向于走全表扫描

OPTIMIZER_INDEX_CACHING：0-100

告诉系统有多少索引缓存在内存，让优化器更倾向于走nested loop连接

OPTIMIZER_INDEX_COST_ADJ：1-10000 默认100

告诉系统索引扫描的cost与全表扫描的cost的比例

OPTIMIZER_MODE：first_rows_[1| 10 | 100 | 1000] | first_rows | all_rows

All_rows是默认值，表示最大吞吐量。

First_row 表示最佳响应时间

此参数可以用hints表示 /*+ALL_ROWS */ , /*+ FIRST_ROWS(10) */

PGA_AGGREGATE_TARGET：

自动PGA管理参数，管理sort，hash join等工作。

OPTIMIZER_DYNAMIC_SAMPLING ：

当统计数据不可访问，使用动态抽样。

STAR_TRANSFORMATION_ENABLED：允许星型查询使用星型转移。

 

二．      优化器版本控制

1.      参数OPTIMIZER_FEATURES_ENABLE 

2.      使用hints，如：

SELECT /*+optimizer_features_enable('11.1.0.6') */ employee_id, last_name

FROM    employees

ORDER BYemployee_id;

                  

三．      优化器概述

当用户提交sql语句，优化器执行了下列步骤

1.      根据可用的access path 和 hint 生成一系列潜在的计划

2.      优化器通过在数据字典中的统计信息评估每个计划的cost，统计包含数据分布和语句访问的表，索引，分区的存储属性。

COST：是与使用资源量成比例的一个估计值。Cost包含了I/O, CPU,和memory

3.      比较计划，选择一个cost最少的计划执行。

 

优化器的操作：

1.      对表达式及环境的评估

2.      语句转换

a)        视图合并 view merging

b)        谓词推进 predicate pushing

c)        子查询反嵌套 subquery unnesting

d)        使用物化视图查询重写query rewrite with Materialized Views

3.      选择优化目标

即使用 optimizer_mode

4.      选择访问路径

5.      选择join 操作。

以上环节中语句转换都是oracle自动执行的，不要人为的干预。

 

四．      评估器estimation

选择性（selectivity）：谓词选择出来的行数占总行数的百分比，如 where employee_name=’Mary’ 记录数不会很多，5%以下的可以建议建索引。

l  当统计信息不可访问：需要使用OPTIMIZER_DYNAMIC_SAMPLING 参数动态抽样，或者是内部默认值。

l  如果字段上存在直方图（histogram ），会用直方图代替唯一值来评估，直方图可以避免数据倾斜。

基数（cardinality）：结果集中的总行数。

花费（cost）:见上文。

 

五．      优化器的访问路径access path

l  全表扫描 fulltables scan

l  Rowid扫描Rowid scan

l  索引扫描 indexscan

l  簇扫描Clusterscan

l  哈希扫描 Hashscan

l  抽样表扫描 sampletable scan

 

全表扫描

全表扫描的原因

1.      缺少索引

2.      数据量太大

3.      表太小，HWM以下的块数小于DB_FILE_MULTIBLOCK_READ_COUNT 。

4.      高度并行，检查 all_tables.degree

5.      Hints  /*+ FULL(e) */

Notes:可以考虑用cache，nocache来缓存相关表。

Notes:当需要使用全表扫描时，可以考虑使用并行查询，一般是在数据仓库这种低并发量的情况中，可以充分利用cpu的资源。

 

Rowid扫描

通常是用在索引扫描后用rowid检索数据。

 

索引扫描index scan

索引簇因子：indexclustering factor，指索引内块的已经排序的程度，排序高的，作范围扫描所需读取的块数就少，改指数影响开销。All_tables.CLUSTERING_FACTOR,该参数接近于索引块数，则表示索引的排序行较好。

 

索引唯一扫描 index unique scan

使用的索引是唯一性（unique）索引或者主键。

Hints ：/*+ INDEX()*/

 

索引范围扫描 index range scan

用到索引范围扫描的情况：

l  col1 = :b1

l  col1 < :b1

l  col1 > :b1

l  带前缀性的组合索引

l  col1 like 'ASD%' 

Hints ：/*+ INDEX()*/

 

反向索引范围扫描 IndexRange Scans Descending

Hints ：/*+ INDEX_DESC()*/

 

索引跳跃扫描 index skip scans

优化器会自己判断是否使用跳跃扫描，没有hints可以使用

 

索引全扫描 indexfull scans

1.order by的情况

Order by的所有字段都在索引里

字段的顺序要和索引完全匹配

或者是

2.sort mergejoin

查询中的所有字段必须在索引内

相关字段的查询顺序也要和索引一致

 

索引快速全扫描 fastfull index scans

条件是至少有一个字段是 not null 约束

Hints /*+INDEX_FFS(e emp_name_ix) */

 

索引连接 index joins

 

Hints /*+INDEX_JOIN */

 

位图索引 bitmap indexes

数据仓库用

 

簇访问 Cluster Access

哈希访问Hash Access

抽样表扫描Sample Tabel Scans

SELECT * FROMhr.employees SAMPLE BLOCK (10);

10代表抽样的百分比

 

六．      优化器如何选择访问路径？

1.      语句中可用的访问路径

2.      使用每个访问路径或者各个访问路径的组合的预估开销

3.      Hints

4.      统计信息

经常检查下dba_tables.last_analyzed字段

 

七．      表连接Join

表连接的顺序

对有外连接条件语句，与外部的连接操作的表必须在连接顺序的条件中的其他表后。优化器不考虑加违反这一规则的命令。

使用hints /*+ ordered*/

或者使用leading hints

例：

SELECT *

    FROM hr.employees e, hr.departments d,hr.job_history j

    WHERE e.department_id = d.department_id

      AND e.hire_date = j.start_date;

 

SELECT /*+LEADING(e j) */ *

    FROM hr.employees e, hr.departments d,hr.job_history j

    WHERE e.department_id = d.department_id

      AND e.hire_date = j.start_date;

 

 

 

嵌套循环连接Nested loop joins

适合场景

1.      连接较小的数据库

2.      访问第二张表的方式是比较有效的

Nested loop的步骤

1.      确定驱动表并指定其为outer table

2.      剩下的就是inner table

3.      Outer table的每一行都要在内部表中扫描一遍

执行计划显示如下，上面的是outertable 下面的是 inner loop

NESTED LOOPS

  outer_loop

  inner_loop

 

11g的nested loop做了优化，使用了新的实施方案，较以往的版本会有所差别

SELECTe.first_name, e.last_name, e.salary, d.department_name

    FROM hr.employees e, hr.departments d

    WHERE d.department_name IN ('Marketing','Sales')

      AND e.department_id = d.department_id;

 

SELECT /*+optimizer_features_enable('10.1.0.6') */ e.first_name, e.last_name, e.salary,d.department_name

    FROM hr.employees e, hr.departments d

    WHERE d.department_name IN ('Marketing','Sales')

      AND e.department_id = d.department_id;

 

Hints /*+use_nl(table1 table2)*/

 

哈希连接Hash Joins

什么时候使用哈希连接

大量的数据必须要连接

小表的大部分数据要连接

 

Hints /*+USE_HASH(l h) */ /*+ NO_USE_HASH(e d) */

 

Sort Merge Joins

适应场景

1.      行源依据排序了

2.      排序操作不必再执行

Hints /*+USE_MERGE(employees departments) */

/*+NO_USE_MERGE(e d) */

 

笛卡尔积CartesianJoins

 

外连接outer joins