关于oracle优化器的环境设置，有很多参数都会影响优化器的工作方式，具体的参数可见http://docs.oracle.com/cd/B28359_01/server.111/b28274/optimops.htm#BABDECGJ，共有CURSOR_SHARING，DB_FILE_MULTIBLOCK_READ_COUNT，OPTIMIZER_INDEX_CACHING，OPTIMIZER_INDEX_COST_ADJ，OPTIMIZER_MODE，PGA_AGGREGATE_TARGET，STAR_TRANSFORMATION_ENABLED，这么几个参数，其中OPTIMIZER_INDEX_CACHING，OPTIMIZER_INDEX_COST_ADJ 是比较难以理解的。本文就自己的学习经验与大家共享。
1. 老外的理解
此参数2与优化器中的cost理解息息相关，故首先我们来看看老外对CBO优化器的理解，详见http://www.docin.com/p-360645801.html，或者 http://blog.itpub.net/7185924/viewspace-837454/。
文章不在赘述，总结了几点
1.oracle的cost=IO + CPU/1000 + NetIO*1.5。这里的IO代表物理IO，CPU代表逻辑IO，NetIO表示从远程数据库上请求的逻辑IO（老外的论文中分布式就不讨论了）
2.逻辑IO包含了 consistent gets 和 db block gets
3.IO分两种，单块顺序读，多块随机读
4.buffer cache中遵循的是LRU算法，即读入的块会放在LRU chain 的头部(MRU)端，但是全表扫描会产生一个问题，就是表太大了，把整个buffer cache都冲刷了一遍，所以在7后面的版本中全表扫的算法是把块读入LRU的尾部(LRU)端。不过如果表比较小的话，是仍然按原来的算法放在MRU端的，小的条件是小于max(2%*buffer cache,20)(注：此为11.2文档的说法),表现形式为隐含参数_SMALL_TABLE_THRESHOLD。Oracle还提供了一个手动的写法就是cache/nocache使用MRU端算法，但需要表小于CACHE_SIZE_THESHOLD参数(没找到这个参数的文档，是不是已经不用了？)，否则cache是无效的。
4.优化器如何计算逻辑读与物理读呢？对于全表扫描，逻辑读=全表的块数，物理读=逻辑读/DB_FILE_MULTIBLOCK_READ_COUNT,很好算吧，但对于索引扫描(除了快速索引扫描)就不好算了，因为是单块读，不能用DB_FILE_MULTIBLOCK_READ_COUNT,所以从8.0版本开始引入了OPTIMIZER_INDEX_CACHING参数帮助计算从逻辑读到物理读的转换。计算公式变成了：
CALCULATED-LOGICAL-READS * (1 – (OPTIMIZER_INDEX_CACHING /100)) = CALCULATED-PHYSICAL-READS= COST
5. OPTIMIZER_INDEX_COST_ADJ是用来告诉oracle比较全表扫描的开销。公式如下
PREVIOUS-COST * (OPTIMIZER_INDEX_COST_ADJ/ 100) = FINAL-COST
默认值是100，也就是两者没有区别，等价
6.怎么调试OPTIMIZER_INDEX_COST_ADJ，老外给出的建议是如下语句：
SELECT sum(case whenEVENT='db file sequential read' then AVERAGE_WAIT else 0 end)*100/
sum(case when EVENT='db file scatteredread' then AVERAGE_WAIT else 0 end) optimizer_index_cost_adj
FROM V$SYSTEM_EVENT
7.本人的理解：从上述描述中可以看出，CBO计算开销其实不会去查看要使用的块是否已经缓存在buffer cache中，这点对于大表的全表扫描，没有太多影响，因为绝大多数情况是不缓存的，也就是逻辑读/ DB_FILE_MULTIBLOCK_READ_COUNT=物理读，而对于索引扫描计算开销有较大影响，因为块都经常缓存在buffer cache中，所以逻辑读=物理读这样的公式显然没有考虑到缓存的作用，故oracle引入了这两个参数来帮助oracle优化器的计算比较接近的开销。
2. 实验环境
SQL> selectversion from v$instance;
VERSION
-----------------
11.1.0.6.0
SQL> showparameter optimizer_mode;
NAME TYPE VALUE
----------------------------------------------- ------------------------------
optimizer_mode string ALL_ROWS
3. 实验1：CBO开销的计算与是否缓存无关
SQL> altersession set nls_language=american;
Session altered.
SQL> droptable t;
Table dropped.
SQL> createtable t as select * from dba_objects;
Table created.
SQL> createindex idx_i on t(object_id);
Index created.
SQL> altersystem flush buffer_cache;
System altered.
SQL> setautotrace on;
SQL> selectcount(1) from t where object_id<=80000;
COUNT(1)
----------
68887
Execution Plan
----------------------------------------------------------
Plan hash value:3352650329
-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 13 | 44 (3)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 13 | | |
|* 2 | INDEX FAST FULL SCAN| IDX_I | 61106 | 775K| 44 (3)| 00:00:01 |
-------------------------------------------------------------------------------
PredicateInformation (identified by operation id):
---------------------------------------------------
2 - filter("OBJECT_ID"<=80000)
Note
-----
- dynamic sampling used for this statement
Statistics
----------------------------------------------------------
32 recursive calls
0 db block gets
232 consistent gets
977 physical reads
0 redo size
420 bytes sent via SQL*Net to client
416 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL> selectcount(1) from t where object_id<=80000;
COUNT(1)
----------
68887
Execution Plan
----------------------------------------------------------
Plan hash value:3352650329
-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 13 | 44 (3)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 13 | | |
|* 2 | INDEX FAST FULL SCAN| IDX_I | 61106 | 775K| 44 (3)| 00:00:01 |
-------------------------------------------------------------------------------
PredicateInformation (identified by operation id):
---------------------------------------------------
2 - filter("OBJECT_ID"<=80000)
Note
-----
- dynamic sampling used for this statement
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
160 consistent gets
4 physical reads
0 redo size
420 bytes sent via SQL*Net to client
416 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
前后两次的开销都是44，没有变化。
实验2，使用optimizer_index_caching改变优化器计算开销
SQL> altersystem set optimizer_index_caching=0;
系统已更改。
SQL> selectobject_id from t where object_id <=40000;
已选择39530行。
执行计划
----------------------------------------------------------
Plan hash value:2507272253
------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 38705 | 188K| 44 (3)| 00:00:01 |
|* 1 | INDEX FAST FULL SCAN| IDX_I | 38705 | 188K| 44 (3)| 00:00:01 |
------------------------------------------------------------------------------
PredicateInformation (identified by operation id):
---------------------------------------------------
1 - filter("OBJECT_ID"<=40000)
统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
2790 consistent gets
0 physical reads
0 redo size
570025 bytes sent via SQL*Net to client
29401 bytes received via SQL*Net from client
2637 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
39530 rows processed
SQL>
SQL> altersystem set optimizer_index_caching=100;
系统已更改。
SQL> selectobject_id from t where object_id <=40000;
已选择39530行。
执行计划
----------------------------------------------------------
Plan hash value:2507272253
------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 38705 | 188K| 44 (3)| 00:00:01 |
|* 1 | INDEX FAST FULL SCAN| IDX_I | 38705 | 188K| 44 (3)| 00:00:01 |
------------------------------------------------------------------------------
PredicateInformation (identified by operation id):
---------------------------------------------------
1 - filter("OBJECT_ID"<=40000)
统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
2790 consistent gets
0 physical reads
0 redo size
570025 bytes sent via SQL*Net to client
29401 bytes received via SQL*Net from client
2637 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
39530 rows processed
通过实验我们看到，其实改变optimizer_index_caching 参数并未改变优化器对开销的计算。我回头看了下文档对optimizer_index_caching的解释发现该参数的意义，As a result, it will be more likely to picknested loops joins over hash or sort-merge joins and to pick indexes usingIN-list iterators over other indexes or full table scans. The default for thisparameter is 0, which results in default optimizer behavior.其实是优化器使用该参数之后会更加倾向于使用nested loop。下面作连接实验
SQL> altersystem set optimizer_index_caching=0;
系统已更改。
SQL> selecta.object_id
2 fromt a,t b
3 where a.object_id=b.object_id
4 anda.object_id<=80000;
已选择68887行。
执行计划
----------------------------------------------------------
Plan hash value:2093728697
--------------------------------------------------------------------------------
-------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Tim
e |
--------------------------------------------------------------------------------
-------
| 0 | SELECT STATEMENT | | 68887 | 672K| | 200 (2)| 00:
00:03 |
|* 1 | HASH JOIN | | 68887 | 672K| 1144K| 200 (2)| 00:
00:03 |
|* 2 | INDEX FAST FULL SCAN| IDX_I | 68887 | 336K| | 44 (3)| 00:
00:01 |
|* 3 | INDEX FAST FULL SCAN| IDX_I | 68887 | 336K| | 44 (3)| 00:
00:01 |
--------------------------------------------------------------------------------
-------
PredicateInformation (identified by operation id):
---------------------------------------------------
1 -access("A"."OBJECT_ID"="B"."OBJECT_ID")
2 -filter("A"."OBJECT_ID"<=80000)
3 -filter("B"."OBJECT_ID"<=80000)
统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
4902 consistent gets
0 physical reads
0 redo size
1000279 bytes sent via SQL*Net to client
50928 bytes received via SQL*Net from client
4594 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
68887 rows processed
SQL>
SQL> altersystem set optimizer_index_caching=100;
系统已更改。
SQL> selecta.object_id
2 fromt a,t b
3 where a.object_id=b.object_id
4 anda.object_id<=80000;
已选择68887行。
执行计划
----------------------------------------------------------
Plan hash value:3970101202
-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 68887 | 672K| 47 (9)| 00:00:01 |
| 1 | NESTED LOOPS | | 68887 | 672K| 47 (9)| 00:00:01 |
|* 2 | INDEX FAST FULL SCAN| IDX_I | 68887 | 336K| 44 (3)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | IDX_I | 1 | 5 | 0 (0)| 00:00:01 |
-------------------------------------------------------------------------------
PredicateInformation (identified by operation id):
---------------------------------------------------
2 -filter("A"."OBJECT_ID"<=80000)
3 -access("A"."OBJECT_ID"="B"."OBJECT_ID")
filter("B"."OBJECT_ID"<=80000)
统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
15131 consistent gets
0 physical reads
0 redo size
1000279 bytes sent via SQL*Net to client
50928 bytes received via SQL*Net from client
4594 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
68887 rows processed
通过修改参数后看到效果显著，使用nested loop能明显改善执行计划。
153
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
