最近一个客户的数据库从10g升级到11g,升级测试过程中发现一个功能模块速度访问变的很慢,原来10g数据库打开查询只需要1-2s,新的11g库大概要2分钟,这个很慢的sql由3个子sql用union all联合起来查询,查看执行计划发现每个子查询的执行计划和老库是一样的,都是走索引index range scan,但是每个子查询产生的cost值都比老库要大,再经过nested loop后这个cost值就变得更大了,从而导致后面的其它执行计划和老库不一致。
后来经过对比发现新库的optimizer_index_cost_adj和optimizer_index_caching这两个参数和老库不一致,新库的是默认值100和0,而老库是20和80.在网上查阅了相关资料后修改这两个参数,重跑应用,速度恢复正常。
下面是这个两个参数的说明:
默认这个两个参数值如下,一般这个值都是需要调整的
一般OLTP系统optimizer_index_cost_adj设置为10-50,optimizer_index_caching设置为90左右
OLAP系统optimizer_index_cost_adj设置为60-100
SQL> show parameter optimizer_index
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
optimizer_index_caching integer 0
optimizer_index_cost_adj integer 100
OPTIMIZER_INDEX_COST_ADJ 这个初始化参数代表一个百分比,取值范围在1到10000之间,
Oracle在选择不同的访问路径时,会对全表扫描和索引扫描进行比较评估.在比较的时候,Oracle会把索引扫描的成本转换为全表扫描的成本,和全表扫描的COST进行比较.这个转换需要一个转换因子.就是optimizer_index_cost_adj。例如当 索引扫描*optimizer_index_cost_adj<全表扫描 的时候就选择索引扫描。
可以通过 select event,average_wait from v$system_event where event like 'db file s%'语句查询顺序读和离散读的等待值,并用 sequential reads / scattered reads 的比来作为 optimizer_index_cost_adj :
select round((select average_wait
from v$system_event
where event='db file sequential read')
/(select average_wait
from v$system_event
where event='db file scattered read')* 100)
as starting_point from dual;
optimizer_index_caching表示能够在缓冲区中找到索引块的平均百分比,即是索引在缓冲区中出现的几率。默认值0 ,对cbo来说,意味着0%的数据块(使用索引访问)可以在oracle‘s SGA的buffer cache中发现。即所有的对索引的访问都将需要物理读该参数只影响CBO计算访问索引块时候的成本。
修改此参数方法,例如一个oltp系统修改如下:
alter system set optimizer_index_cost_adj=20 scope=both;
alter system set optimizer_index_caching=80 scope=both;