Oracle db_file_mulitblock_read_count参数(三)
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> alter system reset db_file_multiblock_read_count scope=spfile sid='*';
System altered.
sys@SYBO2SZ> show parameter read_count;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_file_multiblock_read_count integer 128
sys@SYBO2SZ> startup force; --->reset mbrc后需要重新启动db
sys@SYBO2SZ> show parameter read_count
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_file_multiblock_read_count integer 44
sys@SYBO2SZ> ho grep scatter /u02/database/SYBO2SZ/udump/sybo2sz_ora_24890.trc | tail
WAIT #14: nam='db file scattered read' ela= 682 file#=5 block#=21431 blocks=44 obj#=53208 tim=1337626216154292
WAIT #14: nam='db file scattered read' ela= 4497 file#=5 block#=21475 blocks=38 obj#=53208 tim=1337626216163586
WAIT #14: nam='db file scattered read' ela= 586 file#=5 block#=21515 blocks=44 obj#=53208 tim=1337626216166611
WAIT #14: nam='db file scattered read' ela= 691 file#=5 block#=21559 blocks=44 obj#=53208 tim=1337626216170137
WAIT #14: nam='db file scattered read' ela= 4860 file#=5 block#=21603 blocks=38 obj#=53208 tim=1337626216177698
WAIT #14: nam='db file scattered read' ela= 592 file#=5 block#=21643 blocks=44 obj#=53208 tim=1337626216180540
WAIT #14: nam='db file scattered read' ela= 292 file#=5 block#=21687 blocks=22 obj#=53208 tim=1337626216183493
6、演示不同MBRC所耗用的时间(session级别)
[sql]
--下面的PL/SQL代码用于测试不同的MBRC时所读的块数以及耗用的时间
scott@SYBO2SZ> get assess_mbrc.sql
1 DECLARE
2 l_count PLS_INTEGER;
3 l_time NUMBER(10,1);
4 l_starting_time PLS_INTEGER;
5 l_ending_time PLS_INTEGER;
6 l_blocks PLS_INTEGER;
7 l_starting_blocks PLS_INTEGER;
8 l_ending_blocks PLS_INTEGER;
9 l_dbfmbrc PLS_INTEGER;
10 BEGIN
11 dbms_output.put_line('dbfmbrc blocks seconds');
12 dbms_output.put_line('------- ------ -------');
13 FOR i IN 1..32
14 LOOP
15 l_dbfmbrc := i * 4;
16 EXECUTE IMMEDIATE 'ALTER SESSION SET db_file_multiblock_read_count = '||l_dbfmbrc;
17