Oracle db_file_mulitblock_read_count参数(四)
EXECUTE IMMEDIATE 'ALTER SYSTEM SET EVENTS ''IMMEDIATE TRACE NAME FLUSH_CACHE''';
18 SELECT value INTO l_starting_blocks
19 FROM v$mystat ms JOIN v$statname USING (statisti
c#)
20 WHERE name = 'physical reads';
21 l_starting_time := dbms_utility.get_time();
22 SELECT count(*) INTO l_count FROM big_table;
23 l_ending_time := dbms_utility.get_time();
24 SELECT value INTO l_ending_blocks
25 FROM v$mystat ms JOIN v$statname USING (statistic#)
26 WHERE name = 'physical reads';
27 l_time := l_ending_time-l_starting_time;
28 l_blocks := l_ending_blocks-l_starting_blocks;
29 dbms_output.put_line(l_dbfmbrc||' '||l_blocks||' '||to_char(l_time)||'hsec');
30 END LOOP;
31* END;
32 /
dbfmbrc blocks seconds
------- ------ -------
4 2119 18hsec
8 2093 15hsec
12 2093 7hsec
16 2093 8hsec
20 2093 9hsec
24 2093 8hsec
28 2093 9hsec
32 2093 8hsec
36 2093 8hsec
40 2093 8hsec
44 2093 9hsec
48 2093 9hsec
52 2093 9hsec
56 2093 8hsec
60 2093 9hsec
64 2093 8hsec
68 2093 9hsec
72 2093 8hsec
76 2093 9hsec
80 2093 9hsec
84 2093 9hsec
88 2093 8hsec
92 2093 9hsec
96 2093 9hsec
100 2093 9hsec
104 2093 8hsec
108 2093 9hsec
112 2093 8hsec
116 2093 9hsec
120 2093 8hsec
124 2093 9hsec
128 2093 9hsec
PL/SQL procedure successfully completed.
--从上面的结果可以看出，当设置MBRC为16时，所读取的块数以及所耗用的时间基本上不再受到MBRC的影响。
--由此可知，单纯增加MBRC的值也不能够彻底地解决db file scattered read。
--到底如何设置多少，还是按照
Oracle的建议保留缺省值。再在缺省值的基础之上作相应调整。
--Reference:[841444.1] [473740.1] [1398860.1] [291239.1]