optimizer_index_cost_adj的測試 I

就按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