CBO学习----01--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> 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的可用值：

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\下

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

		CPU costing model:
			Cost = (
			#SRds * sreadtim +
			#MRds * mreadtim +
			#CPUCycles / cpuspeed
			) / sreadtim

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

			#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\下

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）

CBO全书代码