db_file_multiblock_read_count参数对block读取和执行计划的影响

1.继续前一篇的内容，查看db_file_multiblock_read_count对全表扫描读取blocks的变化
SQL> create tablespace aluocp datafile '/disk3/oradata/oradata/ora7a/aluocp.dbf' size 100M
2 extent management local uniform size 256K;

Tablespace created.

SQL> alter table t move tablespace aluocp;

Table altered.

[@more@]

SQL> select file_id,block_id,blocks from dba_extents where segment_name='T';

FILE_ID BLOCK_ID BLOCKS
---------- ---------- ----------
4 9 32
4 41 32
4 73 32

SQL> show parameter read_count

NAME TYPE VALUE
------------------------------------ ---------------------- ------------------------------
db_file_multiblock_read_count integer 8

SQL> alter session set db_file_multiblock_read_count=16;

Session altered.

SQL> show parameter read_count

NAME TYPE VALUE
------------------------------------ ---------------------- ------------------------------
db_file_multiblock_read_count integer 16

SQL> alter session set events '10046 trace name context forever,level 12';

Session altered.

SQL> select count(*) from t;

COUNT(*)
----------
6279

SQL> alter session set events '10046 trace name context off';

Session altered.

SQL> ! grep scatt /disk3/oradata/admin/ora7a/udump/ora7a_ora_6128.trc
WAIT #1: nam='db file scattered read' ela= 311 p1=3 p2=5026 p3=7
WAIT #1: nam='db file scattered read' ela= 211 p1=3 p2=5033 p3=8
WAIT #1: nam='db file scattered read' ela= 302 p1=3 p2=5041 p3=8
WAIT #1: nam='db file scattered read' ela= 226 p1=3 p2=5049 p3=8
WAIT #1: nam='db file scattered read' ela= 219 p1=3 p2=5057 p3=8
WAIT #1: nam='db file scattered read' ela= 300 p1=3 p2=5065 p3=8
WAIT #1: nam='db file scattered read' ela= 501 p1=3 p2=5073 p3=8
WAIT #1: nam='db file scattered read' ela= 466 p1=3 p2=5081 p3=8
WAIT #1: nam='db file scattered read' ela= 470 p1=3 p2=5089 p3=8
WAIT #1: nam='db file scattered read' ela= 139 p1=3 p2=5097 p3=6
WAIT #1: nam='db file scattered read' ela= 436 p1=4 p2=10 p3=16
WAIT #1: nam='db file scattered read' ela= 376 p1=4 p2=26 p3=15
WAIT #1: nam='db file scattered read' ela= 518 p1=4 p2=41 p3=16
WAIT #1: nam='db file scattered read' ela= 305 p1=4 p2=57 p3=16
WAIT #1: nam='db file scattered read' ela= 242 p1=4 p2=73 p3=14
红色为db_file_multiblock_read_count＝8时的结果，蓝色为db_file_multiblock_read_count＝16时的结果。
注：db_file_multiblock_read_count<=128，当db_file_multiblock_read_count>32时，效果不会明显。

2.执行计划的变化
SQL> analyze table t compute statistics for table for all indexes for all indexed columns;

Table analyzed.

SQL> set autotrace traceonly explain

SQL> alter session set db_file_multiblock_read_count=16;

Session altered.

SQL> select * from t where owner='SYSTEM';

Execution Plan
-------------------------------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=8 Card=453 Bytes=38958)
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'T' (Cost=8 Card=453 Bytes=38958)
2 1 INDEX (RANGE SCAN) OF 'I_T' (NON-UNIQUE) (Cost=2 Card=453)

SQL> alter session set db_file_multiblock_read_count=32;

Session altered.

SQL> select * from t where owner='SYSTEM';

Execution Plan
------------------------------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=6 Card=453 Bytes=38958)
1 0 TABLE ACCESS (FULL) OF 'T' (Cost=6 Card=453 Bytes=38958)

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

转载于:http://blog.itpub.net/7319461/viewspace-928341/