optimizer_index_cost_adj的测试 I

optimizer_index_cost_adj的测试。

关于optimizer_index_cost_adj这个参数，起初并没有太多注意，在9i下其default值100一直工作得很合适。

不过偶然看到一个家伙把DB从9i->10g后，默认的100使得很多SQL的execution plan发生了变化。这说明10g和9i的CBO算法是有变化的。因此对这个参数产生了兴趣，

[@more@]

就按Tim Gorman的观点，每个系统有它自己合适的optimizer_index_cost_adj的值。

下面来看生产系统E的optimizer_index_cost_adj的值应该设多少。

生产环境是RHEL4up4+Oracle 9i.

按照Tim Gorman的算法。这个值大致应该是V$system_event中，db file sequential read 的 average_waits除以db file scattered read的average_waits(Timed_statistics=true，否则average_waits是0值).

先看下生产系统E:

SQL> show parameter timed_statistics;

NAME TYPE

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

VALUE

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

timed_statistics boolean

TRUE

SQL> select event,average_wait from v$system_event

2 where event like 'db file s%';

EVENT

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

AVERAGE_WAIT

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

db file sequential read

0

db file scattered read

0

db file single write

1

不巧，都是0。该系统并不是很繁忙。

那再看看生产系统A，平时CPU负载在60~90%，不过可惜也都是0。最后找下来我这里的系统没有一台不是0的。这个，似乎我们的SQL还是不错的，至少没有过多的IO耗费。

看来这个值的调校只有在

Eygle有一篇文章是讲optimizer_index_cost_adj与成本计算，那来按他的方式来找这个optimizer_index_cost_adj的临界值。

测试表的结构。

SQL> desc tst2_country;

Name Null? Type

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

COUNTRY_CO NOT NULL VARCHAR2(5)

COUNTRY_EN VARCHAR2(30)

COUNTRY_NA VARCHAR2(30)

EXAM_MARK VARCHAR2(1)

HIGH_LOW VARCHAR2(1)

测试表的资料数

SQL> select count(*) from tst2_country;

COUNT(*)

----------

245

Index：indx_tst2 tst2_country(country_co)

分析table

SQL> analyze table tst2_country compute statistics;

Table analyzed.

SQL> show parameter optimizer;

NAME TYPE

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

VALUE

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

optimizer_dynamic_sampling integer

1

optimizer_features_enable string

9.2.0

optimizer_index_caching integer

0

optimizer_index_cost_adj integer

100

optimizer_max_permutations integer

NAME TYPE

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

VALUE

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

2000

optimizer_mode string

CHOOSE

---------------------------------------------------------------------下面开始测试。

SQL> set autotrace traceonly;

SQL> select * from tst2_country where country_co=304;

Execution Plan

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

0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1 Bytes=59)

1 0 TABLE ACCESS (FULL) OF 'TST2_COUNTRY' (Cost=2 Card=1 Bytes

=59)

Statistics

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

0 recursive calls

0 db block gets

6 consistent gets

0 physical reads

0 redo size

841 bytes sent via SQL*Net to client

655 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

1 rows processed

SQL> select /*+index(tst2_country indx_tst2)*/ * from tst2_country where country_co=304;

Execution Plan

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

0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1 Bytes=59)

1 0 TABLE ACCESS (BY INDEX ROWID) OF 'TST2_COUNTRY' (Cost=2 Ca

rd=1 Bytes=59)

2 1 INDEX (FULL SCAN) OF 'INDX_TST2' (NON-UNIQUE) (Cost=1 Ca

rd=1)

Statistics

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

0 recursive calls

0 db block gets

3 consistent gets

0 physical reads

0 redo size

841 bytes sent via SQL*Net to client

655 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

1 rows processed

以上2笔，Index方式Consistent gets=3, FTS方式Consistent gets=6。从logic IO的角度看，INDEX方式应该更好一点。

从cost看，2者的cost都是2。可能最终表达结果有精简过，实质上我理解：精简前的值FTS方式会更小点，所以CBO选择了FTS。

下面是更改optimizer_index_cost_adj的测试：

设为50：

SQL> alter session set optimizer_index_cost_adj=50;

Session altered.

SQL> show parameter optimizer_index;

NAME TYPE

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

VALUE

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

optimizer_index_caching integer

0

optimizer_index_cost_adj integer

50

再来看走index的情况：

SQL> select /*+index(tst2_country indx_tst2)*/ * from tst2_country where country_co=304;

Execution Plan

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

0 SELECT STATEMENT Optimizer=CHOOSE (Cost=1 Card=1 Bytes=59)

1 0 TABLE ACCESS (BY INDEX ROWID) OF 'TST2_COUNTRY' (Cost=1 Ca

rd=1 Bytes=59)

2 1 INDEX (FULL SCAN) OF 'INDX_TST2' (NON-UNIQUE) (Cost=1 Ca

rd=1)

Statistics

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

0 recursive calls

0 db block gets

3 consistent gets

0 physical reads

0 redo size

841 bytes sent via SQL*Net to client

655 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

1 rows processed

现在的cost变成1了。

再看不+hint的情况：

SQL> select * from tst2_country where country_co=304;

Execution Plan

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

0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1 Bytes=59)

1 0 TABLE ACCESS (FULL) OF 'TST2_COUNTRY' (Cost=2 Card=1 Bytes

=59)

Statistics

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

0 recursive calls

0 db block gets

6 consistent gets

1 physical reads

0 redo size

841 bytes sent via SQL*Net to client

655 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

1 rows processed

FTS。Cost=2, 产生了physical reads, 我想是FTS的block比较容易被清出data buffer。

没想到Oracle没选cost=1的方式。

下面清除data buffer 和shared_pool再看

SQL> alter system set events='immediate trace name flush_cache';

System altered.

SQL> alter system flush shared_pool;

System altered.

SQL> select * from tst2_country where country_co=304;

Execution Plan

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

0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1 Bytes=59)

1 0 TABLE ACCESS (FULL) OF 'TST2_COUNTRY' (Cost=2 Card=1 Bytes

=59)

Statistics

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

260 recursive calls

0 db block gets

43 consistent gets

12 physical reads

0 redo size

841 bytes sent via SQL*Net to client

655 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

4 sorts (memory)

0 sorts (disk)

1 rows processed

+hint的情况

SQL> alter system set events='immediate trace name flush_cache';

System altered.

SQL> alter system flush shared_pool;

System altered.

SQL> select /*+index(tst2_country indx_tst2)*/ * from tst2_country where country_co=304;

Execution Plan

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

0 SELECT STATEMENT Optimizer=CHOOSE (Cost=1 Card=1 Bytes=59)

1 0 TABLE ACCESS (BY INDEX ROWID) OF 'TST2_COUNTRY' (Cost=1 Ca

rd=1 Bytes=59)

2 1 INDEX (FULL SCAN) OF 'INDX_TST2' (NON-UNIQUE) (Cost=1 Ca

rd=1)

Statistics

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

260 recursive calls

0 db block gets

40 consistent gets

8 physical reads

0 redo size

841 bytes sent via SQL*Net to client

655 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

4 sorts (memory)

0 sorts (disk)

1 rows processed

依然走FTS。Oracle 竟然没有选cost比较小的PLAN.

本来是要测optimizer_index_cost_adj的选值的，没想到测出这个来。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/10856805/viewspace-997021/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/10856805/viewspace-997021/