_sort_multiblock_read_count 2 multi-block read count for sort
sys@SYBO2SZ> get trace_enable_cur.sql
1* alter session set events '10046 trace name context forever,level 8';
sys@ORA11G> @trace_enable_cur
Session altered.
sys@SYBO2SZ> select count(*) from scott.big_table where owner='SYS';
COUNT(*)
----------
468066
sys@SYBO2SZ> get trace_disable_cur.sql
1* alter session set events '10046 trace name context off';
sys@SYBO2SZ> @trace_disable_cur.sql
Session altered.
sys@SYBO2SZ> @my_env
SPID SID SERIAL# USERNAME PROGRAM
------------ ---------- ---------- --------------- ------------------------------------------------
24472 1094 30 robin oracle@SZDB (TNS V1-V3)
sys@SYBO2SZ> @get_spec_sess_trace_file
Enter value for input_sid: 1094
Enter value for input_serial: 30
SID SERIAL# SPID TRACE_FILE
---------- ---------- ------------ ----------------------------------------------------------
1094 30 24472 /u02/database/SYBO2SZ/udump/sybo2sz_ora_24472.trc
--下面的blocks即为单次读取时的块数
sys@SYBO2SZ> ho grep scatter /u02/database/SYBO2SZ/udump/sybo2sz_ora_24472.trc |tail
WAIT #7: nam='db file scattered read' ela= 1491 file#=5 block#=18363 blocks=16 obj#=52884 tim=1337624821997106
WAIT #7: nam='db file scattered read' ela= 5148 file#=5 block#=18443 blocks=2 obj#=52884 tim=1337624822005413
WAIT #7: nam='db file scattered read' ela= 32363 file#=5 block#=18571 blocks=16 obj#=52884 tim=1337624822041788
WAIT #7: nam='db file scattered read' ela= 1930 file#=5 block#=18587 blocks=16 obj#=52884 tim=1337624822044227
WAIT #7: nam='db file scattered read' ela= 345 file#=5 block#=18603 blocks=16 obj#=52884 tim=1337624822045165
WAIT #7: nam='db file scattered read' ela= 1712 file#=5 block#=18619 blocks=16 obj#=52884 tim=1337624822047555
WAIT #7: nam='db file scattered read' ela= 58 file#=5 block#=18635 blocks=2 obj#=52884 tim=1337624822048219
b、MBRC大于128的情形
sys@SYBO2SZ> alter system set db_file_multiblock_read_count=256;
System altered.
sys@SYBO2SZ> show parameter read_count; --->从这个查询可知，大于128的情形并没有生效
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_file_multiblock_read_count integer 128
--对于MBRC为128的情形演示步骤同上，下面仅仅列出最终结果
--注意在使用不同的MBRC在系统级别测试前应将buffer cache清空(alter system flush buffer_cache)
sys@SYBO2SZ> ho grep scatter /u02/database/SYBO2SZ/udump/sybo2sz_ora_24750.trc |tail
WAIT #7: nam='db file scattered read' ela= 2070 file#=5 block#=20875 blocks=126 obj#=53208 tim=1337625626875769
WAIT #7: nam='db file scattered read' ela= 1991 file#=5 block#=21003 blocks=126 obj#=53208 tim=1337625626880199
WAIT #7: nam='db file scattered read' ela= 1970 file#=5 block#=21131 blocks=126 obj#=53208 tim=1337625626884412
WAIT #7: nam='db file scattered read' ela= 2078 file#=5 block#=21259 blocks=126 obj#=53208 tim=1337625626888846
WAIT #7: nam='db file scattered read' ela= 2035 file#=5 block#=21387 blocks=126 obj#=53208 tim=1337625626893039
WAIT #7: nam='db file scattered read' ela= 2040 file#=5 block#=21515 blocks=126 obj#=53208 tim=1337625626897021
WAIT #7: nam='db file scattered read' ela= 1048 file#=5 block#=21643 blocks=66 obj#=53208 tim=1337625626900379
c、MBRC为缺省值的情形
sys@SYBO2SZ> al