Oracle db_file_mulitblock_read_count参数(二)
- 64bit Production
--查看MBRC的值
sys@SYBO2SZ> show parameter read_count;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_file_multiblock_read_count integer 16
--查看和read_count相关的隐藏参数
sys@SYBO2SZ> @hidden_para
Enter value for para: read_count
KSPPINM KSPPSTVL DESCRIB
----------------------------------- -------------------- ---------------------------------------------
_db_file_exec_read_count 48 multiblock read count for regular clients
_db_file_optimizer_read_count 8 multiblock read count for regular clients
_db_file_noncontig_mblock_read_count 11 number of noncontiguous db blocks to be prefetched
_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