054.【实验：参数DB_FILE_MULTIBLOCK_READ_COUNT及optimizer_index_cost_adj对优化器的影响】...

--1.查看参数设置
SQL> show parameters db_file_multiblock_read_count

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_file_multiblock_read_count integer 32
SQL> show parameters optimizer_index_cost_adj

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
optimizer_index_cost_adj integer 100

--2.收集统计信息
begin
dbms_stats.gather_table_stats(ownname => 'scott',tabname => 't_db_cache');
end;


【注】select count(*),max(t.deptno),min(t.deptno) from scott.DEPT_SORT t;
COUNT(*) MAX(T.DEPTNO) MIN(T.DEPTNO)
43105 43149 10

【实验：db_file_multiblock_read_count对优化器的影响】

1.参数db_file_multiblock_read_count=32
SQL> select * from scott.DEPT_SORT t where t.deptno<140;

103 rows selected.

Elapsed: 00:00:00.03

Execution Plan
----------------------------------------------------------
Plan hash value: 615620829

-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 95 | 3515 | 54 (2)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| DEPT_SORT | 95 | 3515 | 54 (2)| 00:00:01 |
-------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter("T"."DEPTNO"<140)


Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
265 consistent gets
0 physical reads
0 redo size
4767 bytes sent via SQL*Net to client
451 bytes received via SQL*Net from client
8 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
103 rows processed

2.SQL> alter session set db_file_multiblock_read_count=16;
SQL> select * from scott.DEPT_SORT t where t.deptno<140;

103 rows selected.

Elapsed: 00:00:00.01

Execution Plan
----------------------------------------------------------
Plan hash value: 2272872484

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

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| T
ime |

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

| 0 | SELECT STATEMENT | | 95 | 3515 | 57 (0)| 0
0:00:01 |

| 1 | TABLE ACCESS BY INDEX ROWID| DEPT_SORT | 95 | 3515 | 57 (0)| 0
0:00:01 |

|* 2 | INDEX RANGE SCAN | IND_DEP | 95 | | 2 (0)| 0
0:00:01 |

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


Predicate Information (identified by operation id):
---------------------------------------------------

2 - access("T"."DEPTNO"<140)


Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
49 consistent gets
0 physical reads
0 redo size
5479 bytes sent via SQL*Net to client
451 bytes received via SQL*Net from client
8 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
103 rows processed

SQL> select /*+no_index(t)*/ * from scott.DEPT_SORT t where t.deptno<140;

103 rows selected.

Elapsed: 00:00:00.02

Execution Plan
----------------------------------------------------------
Plan hash value: 615620829

-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 95 | 3515 | 61 (2)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| DEPT_SORT | 95 | 3515 | 61 (2)| 00:00:01 |
-------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter("T"."DEPTNO"<140)


Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
265 consistent gets
0 physical reads
0 redo size
4767 bytes sent via SQL*Net to client
451 bytes received via SQL*Net from client
8 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
103 rows processed
【小结】db_file_multiblock_read_count对全表扫描成本的影响
SQL:select * from scott.DEPT_SORT t where t.deptno<140;
1.使用索引的COST：57
2.全表扫描：
1）db_file_multiblock_read_count=32时COST为54
2）db_file_multiblock_read_count=16时COST为61
3.所以当db_file_multiblock_read_count=32时优化器选择全表扫描，当db_file_multiblock_read_count=16时优化器选择走索引


【实验：optimizer_index_cost_adj对优化器的影响】
SQL> alter session set db_file_multiblock_read_count=32;

Session altered.

Elapsed: 00:00:00.00
SQL> alter session set optimizer_index_cost_adj=50;

Session altered.

Elapsed: 00:00:00.08
SQL> select * from scott.DEPT_SORT t where t.deptno<140;

103 rows selected.

Elapsed: 00:00:00.01

Execution Plan
----------------------------------------------------------
Plan hash value: 2272872484

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

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| T
ime |

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

| 0 | SELECT STATEMENT | | 95 | 3515 | 29 (0)| 0
0:00:01 |

| 1 | TABLE ACCESS BY INDEX ROWID| DEPT_SORT | 95 | 3515 | 29 (0)| 0
0:00:01 |

|* 2 | INDEX RANGE SCAN | IND_DEP | 95 | | 1 (0)| 0
0:00:01 |

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


Predicate Information (identified by operation id):
---------------------------------------------------

2 - access("T"."DEPTNO"<140)


Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
49 consistent gets
0 physical reads
0 redo size
5479 bytes sent via SQL*Net to client
451 bytes received via SQL*Net from client
8 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
103 rows processed

SQL> alter session set optimizer_index_cost_adj=90;

Session altered.

Elapsed: 00:00:00.00
SQL> select * from scott.DEPT_SORT t where t.deptno<140;

103 rows selected.

Elapsed: 00:00:00.00

Execution Plan
----------------------------------------------------------
Plan hash value: 2272872484

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

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| T
ime |

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

| 0 | SELECT STATEMENT | | 95 | 3515 | 51 (0)| 0
0:00:01 |

| 1 | TABLE ACCESS BY INDEX ROWID| DEPT_SORT | 95 | 3515 | 51 (0)| 0
0:00:01 |

|* 2 | INDEX RANGE SCAN | IND_DEP | 95 | | 2 (0)| 0
0:00:01 |

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


Predicate Information (identified by operation id):
---------------------------------------------------

2 - access("T"."DEPTNO"<140)


Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
49 consistent gets
0 physical reads
0 redo size
5479 bytes sent via SQL*Net to client
451 bytes received via SQL*Net from client
8 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
103 rows processed
【小结】optimizer_index_cost_adj对索引成本的影响
1.optimizer_index_cost_adj=100时走索引的成本为57
2.optimizer_index_cost_adj=50时走索引的成本为29（约等于57*0.5）
3.optimizer_index_cost_adj=90时走索引的成本为51（约等于57*0.9）

【总结】
1.db_file_multiblock_read_count能提高全表扫描的效率。同时影响全表扫描的成本计算，参数值越大，优化就会更多地选择全表扫描。
2.optimizer_index_cost_adj影响的是使用索引时的成本计算。走索引的成本=原成本*（optimizer_index_cost_adj/100）。该参数使优化器更多地选择索引。
3.这两个参数应该配合使用。通过测试决定这两个参数的值，以达到系统的最佳性能。

[@more@]

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

转载于:http://blog.itpub.net/7901922/viewspace-1060038/