CBO学习---第1章--What do You Mean by Cost
CBO学习---第1章--What do You Mean by Cost
第1章 Cost的含义（What do You Mean by Cost?）
Cost（代价/成本）的两方面含义：
(1)explain plan工具产生的神奇数字
(2)执行SQL的实际资源消耗
其实Cost就是优化器执行计算的结果.
本书主要学习：优化器如何产生Cost的.
1.1优化器选项
CBO（基于代价/成本的优化器）的3个变体（varians）
通过参数optimizer_mode控制
查看和修改方式：
[sql]
SQL> show parameter optimizer_mode
NAME TYPE VALUE
------------------------------------ ---------------------- ----------
optimizer_mode string ALL_ROWS
SQL> alter session set optimizer_mode=first_rows;
SQL> show parameter optimizer_mode
NAME TYPE VALUE
------------------------------------ ---------------------- ----------
optimizer_mode string FIRST_ROWS
参数optimizer_mode的可用值：
[sql] all_rows --最短时间内完成语句的执行计划 first_rows_N --N=1、10、100、1000，彻底分析第一个join order，估计返回的总行数，重新优化，先返回前N行，再继续执行。 first_rows --9i中已过时，快速返回第一行。其中有多个高层约束：避免merge join和hash jion，除非只能跟全表扫描的第二张表进行nested loop连接。 rule --已废弃，内部部分语句有hint/*+rule*/ choose --介于role和all_rows之间
参数optimizer_dynamic_sampling，控制对没有统计信息的表是否进行动态采样，2是(10g默认)，1否(9i默认)
#脚本分析，代码包附件中
关于optimizer_mode=first_rows的cost分析，路径\ch_01_cost\下
[sql] first_rows.sql first_rows_08.txt first_rows_09.txt first_rows_10.txt
代码中构建了一个10000行的表，表上有主键和一个普通列的B树索引；
optimizer_mode=first_rows下，执行6种查询，体现不同版本下，不同的条件对优化器的影响而产生不同的Cost值.
代码中，隐藏参数“_sort_elimination_cost_ratio”--不走sort的cost与走sort的cost的比率控制参数，具体控制如下：
如果不走sort的cost/走sort的cost > _sort_elimination_cost_ratio,那么执行计划会走排序.
如果不走sort的cost/走sort的cost < _sort_elimination_cost_ratio,那么执行计划不会走排序.
_optimizer_cost_model=choose,10g将自动激活cpu costing的模式，即当没有收集system statistics时，默认采用noworkload system statistics
1.2Cost的定义
有时CBO会产生错误的Cost，其原因：
(1)Cost model某些假设不适用
(2)数据统计信息可用，但误导
(3)数据统计信息不可用
(4)不了解硬件性能
(5)不了解当前负载（workload）
(6)代码bug
CBO进化历程：
(1)8i的opt只计算IO次数（the # of IO requests）
(2)9i引入CPU costing
[sql]
CPU costing model:
Cost = (
#SRds * sreadtim +
#MRds * mreadtim +
#CPUCycles / cpuspeed
) / sreadtim
[sql]
---------------------------------------------------------------------------------
[sql]
#SRDs - number of single block reads
#MRDs - number of multi block reads
#CPUCycles - number of CPU Cycles
sreadtim - single block read time 单位毫秒
mreadtim - multi block read time 单位毫秒
cpuspeed - CPU cycles per second 单位MHz
(3)10g引入offline optimizer
以profile形式存储关键的统计信息，通过提示方式，帮助online optimizer加强查询
1.3变型（transformation）和代价计算（costing）
SQL变型：在计算cost之前，将SQL语句等价变形。
变形准则：
（1）SQL不能变型
（2）如果能变型，就变型
（3）能变形，但代价太大，就不变型
隐形参数“_complex_view_merging”，false（8i），true（9i），控制SQL中有view存在时，是否将view定义读入SQL，再进行等价变型和优化。（8i中该参数设置成true，有时也控制不住）
#脚本分析，代码包附件中
路径\ch_01_cost\下
[sql] view_merge_01.sql view_merge_01_08.trc view_merge_01_08.txt view_merge_01_09.trc view_merge_01_09.txt view_merge_01_10.trc view_merge_01_10.txt
通过修改参数“_complex_view_merging”和加入hint来控制SQL语句的执行计划
在执行优化之前，还要执行一些操作，如下：
谓词推进（predicate pushing）:通过传递闭包（transitive closure）产生谓词和从约束中产生谓词
非嵌套子查询（subquery unnesting）
星型变型（star transformation）
附件：
/2013/0719/20130719095917100.rar
