第十一章 查询优化器quiry optimizer

用户提交语句后，查询优化器会做如下工作：
1.根据可选路径和hint生成一系列计划
2.根据数据字典中的统计信息计算成本。统计信息包括数据的分布、表和索引的存储特性以及分区等情况。
成本cost是用特定计划在当前评估的资源条件下执行语句的花费。包括i/o、cpu、内存等信息。
3.优化器比较成本（或叫代价），选择成本最小的执行。

可以通过hint告诉oracle怎样执行语句

查询优化器的组件
query transformation
estimation评价
plan generation

1.query transformation
语句中的每个查询区域叫做一个查询块。
Each query portion of a statement is called a query block. The input to the query transformer is a parsed query, which is represented by a set of query blocks.

以下语句分为内部查询块和外部查询块
SELECT first_name, last_name
FROM employees
WHERE department_id
IN (SELECT department_id FROM departments WHERE location_id = 1800);

query transformer会执行一些转义工作，包括：
view merging、
predicate pushing、
Subquery Unnesting、
Query Rewrite with Materialized Views

（1）view merging的作用可以通过下面的例子说明：
In view merging, the transformer merges the query block representing the view into the containing query block.

CREATE VIEW employees_50_vw AS
SELECT employee_id, last_name, job_id, salary, commission_pct, department_id
FROM employees
WHERE department_id = 50;

然后查询：
SELECT employee_id
FROM employees_50_vw
WHERE employee_id > 150;

优化器使用view merging 转义为：
SELECT employee_id
FROM employees
WHERE department_id = 50
AND employee_id > 150;

The view merging optimization applies to views that contain only selections, projections, and joins. That is, mergeable views do not contain set operators, aggregate functions, DISTINCT, GROUP BY, CONNECT BY, and so on.

（2）Predicate Pushing
可以进一步的优化语句

如：视图是两个表union的结果，无法通过view merging优化
CREATE VIEW all_employees_vw AS
( SELECT employee_id, last_name, job_id, commission_pct, department_id
FROM employees )
UNION
( SELECT employee_id, last_name, job_id, commission_pct, department_id
FROM contract_workers );
You then query the view as follows:
SELECT last_name
FROM all_employees_vw
WHERE department_id = 50;

Because the view is a compound query, the optimizer cannot merge the view's query into the accessing query block. Instead, the optimizer can transform. the accessing statement by pushing its predicate, the WHERE clause condition department_id=50, into the view's compound query. The equivalent transformed query is as follows:

SELECT last_name
FROM ( SELECT employee_id, last_name, job_id, commission_pct, department_id
FROM employees
WHERE department_id=50
UNION
SELECT employee_id, last_name, job_id, commission_pct, department_id
FROM contract_workers
WHERE department_id=50 );

this technique improves the subplan of the unmerged view because the database can use the pushed-in predicates to access indexes or to use as filters.

（3）Subquery Unnesting In subquery unnesting
当子查询中不包含分组函数（如avg），且可以转义为非嵌套的语句，oracle进行转移来提高查询的性能。

（4）Query Rewrite with Materialized Views
当数据库发现查询中调用了物化视图时，将对物化视图进行重写，此技术提高了查询的执行效率。由于大部分的查询结果已经预先计算出来了。
当不使用物化视图的成本小于使用物化视图的成本时，oracle放弃对物化视图的改写。

2.estimation
评价决定了一个执行计划的成本。包括三个目标：
（1）selectivity可选择性
代表了某行在行集中的百分比
（2）cardinality 集的势
代表了行数
（3）cost
消耗的资源

统计信息可以提高测量的准确度

（1）
可选择性取决于统计信息是否可用
若不可用，将取决于参数optimizer_dynamic_sampling。这个参数可以设为dynamic sampling或一个内部默认值。
内部默认值取决于谓词，如：
(last_name = 'Smith') is lower than for a range predicate (last_name > 'Smith') because an equality predicate is expected to return a smaller fraction of rows

统计信息可用时，用其计算可选择性。
如果当前列上的柱状图histogram可用，estimator就不用找字段的distinct值。
柱状图反映了某一列上值的分布情况，比可选择性更适用，特别是对含严重偏斜的数据。

（2）cardinality代表了行数，可以是表、视图或者join、group by的结果等。

（3）cost是花费信息
access path即获取路径，可能是全表扫描、索引全扫描等。

全表扫描或索引全扫描：数据库从磁盘上逐个读取多个块。因此代价取决于待扫描的块数以及多块读的数量

索引扫描：取决于B树索引的层数、待扫描索引叶子块的数量、索引关键字上插入的rowid数量。
使用rowid插入的成本取决于index clustering factor。

3.plan generation 计划的生成
计划生成器罗列出不同的执行路径、连接方式和顺序，目的是选择出成本最低的计划。

（1）连接顺序
比如有t1、t2、t3三个表，顺序可能是先查t1，然后查询t2，做join连接，再查询t3，并join

（2）query subplans查询子计划
优化器将每个嵌套子查询或者未merge的视图作为一个分离的查询块，并生成子计划。数据库优化器自底向上分别查询块。
因此，数据库首先对内层的块进行优化，生成子计划；然后逐层进行此操作。
随着from中表的数量增加，产生的执行计划也会越来越多

（3）cutoff for plan selection
如果oracle发现待比较的执行计划比当前最优的计划成本低，会继续比较，如果高，则停止比较。

二、bing variable peeking 绑定变量的取出

当进行硬解析时，优化器查看绑定变量的值。
当查询中使用字面值时，优化器可以使用字面值找到最优计划。而使用绑定变量，优化器需要在不考虑字面值的情况下找到最优的执行计划。这是很困难的。
通过绑定之的消除，优化器就能决定就能像使用字面值那样决定where条件的可选择性了，因此使计划的效率更高了。

这种情况证明，并不是所有情况都适合于使用绑定变量。

1.adaptive cursor sharing
可调整的共享游标这个特性，允许一条语句生成多个执行计划。

需要注意：对于sql语句中包含了14个以上绑定变量的语句，没有可调整的共享游标。

此特性独立于参数cursor_Sharing。在用户定义的绑定变量或系统生成的绑定变量都适用。

（1）.bind-sensitive cursors
绑定敏感性游标指优化计划依赖于绑定变量的值。数据库监测绑定敏感型游标，以便找到较好的执行计划。

优化器通过以下标准来判断是否是绑定敏感型游标（bind-sensitive cursor）：
优化器对绑定值进行快速扫描（peek）来生成可选择性好的评估
在包含绑定值的列上有柱状图

例如：表中某列严重偏移，99.9%是10，剩下的是其他值（1~9）。现在我们查询9 这个值，执行计划显示是走index range scan。
由于9的可选择性比较低（小于1%），选择索引肯定是高效的。
这时我们查看v$sql，看到为这条语句生成了一个子游标，buffer gets值很小。
由于该表是严重偏移的，数据库为其创建了柱状图，柱状图使数据库将这列标记为绑定敏感型（bind-sensitive）

每次用不同的值执行这个查询语句时，数据库记录用新值执行时的统计数据，并与原有的统计数据进行比较。
如果变化很大，数据库将游标标记为bind-aware

（2）.bind-aware cursor
指中有资格为不同绑定变量值使用不同执行计划的绑定敏感型游标。
一旦一个游标被标记为bind-aware，优化器在以后选择计划时将以此绑定值及其可选择性做为依据

当一条含bind-sensitive cursor的语句执行时，数据库将决定是否将其标记为bind-aware。
如果一个游标被标记为bind-aware cursor，再次执行时，将有如下动作：
基于新的变量值生成新的执行计划
将语句的原游标标记为不共享（not shareable）。这个游标不再可用，并将优先被shared sql area aged out掉

（3）cursor merging
如果优化器创建了一个bind-aware cursor，如果这个计划和已经存在的游标一样，则优化器将进行游标合并（cursor merging）
这样，数据库通过游标的合并节省了shared sql area中的空间，增加了游标可选择性的范围以包含新的变量。
假设你所执行的查询是用了一个没有落在已知游标可选择范围中的游标，数据库进行硬解析为生成新的计划和游标。
如果这个新的计划和已存在的游标使用同一个计划，数据库将这两个游标合并，并删除旧的游标。

2.查看绑定相关的性能数据
可以在v$视图中相关信息：
v$sql 查看游标是否是bind-sensitive、bind-aware的
v$sql_cs_histogram  执行次数的分布
v$sql_cs_selectivity 如果可选择性用来检查游标共享，显示为每一个含有谓词的绑定变量存储的可选择性范围
v$sql_cs_statistics 总结优化器用来决定是否标记一个bind-aware的信息