原帖由 Yong Huang 于 2009-7-22 22:59 发表
Metalink Note:223117.1 says:
"Starting with Oracle10g Release 2 the DB_FILE_MULTIBLOCK_READ_COUNT
initialization parameter is now automatically tuned to use a default value
when this parameter is not set explicitly. This default value corresponds
to the maximum I/O size that can be performed efficiently.
This value is platform-dependent and is 1MB for most platforms."
I suggest you not set this parameter.
My question about your logic is that How do you know the blocks were read into buffer cache at *one* time instead of two times or more? The way to tell is SQL trace with wait event on, so p3 for db file scattered read event can tell you.
Yong Huang
恩,我进行了如下操作
SQL> show parameter db_file_multiblock_read_count;
NAME TYPE VALUE
------------------------------------ ----------- ------
db_file_multiblock_read_count integer 3
alter system flush buffer_cache; 清空buffer cache
alter session set events '10046 trace name context forever, level 8';
select * from chris.test where col2 = 'test1';
select file#,block#,class#,a.status,b.object_name from v$bh a,dba_objects b where file# = 6 and a.status <> 'free'
and a.objd = b.data_object_id order by block# ;
file# block# class# status object_name
1 6 27 4 xcur TEST
2 6 28 1 xcur TEST
3 6 29 1 xcur TEST
4 6 30 1 xcur TEST
5 6 31 1 xcur TEST
6 6 32 1 xcur TEST
然后使用
tkprof c:\app\chris\diag\rdbms\test\test\trace\test_ora_11684.trc waits=yes output=1304 分析跟踪文件
相关输出如下,看不太明白,呵呵
********************************************************************************
count = number of times OCI procedure was executed
cpu = cpu time in seconds executing
elapsed = elapsed time in seconds executing
disk = number of physical reads of buffers from disk
query = number of buffers gotten for consistent read
current = number of buffers gotten in current mode (usually for update)
rows = number of rows processed by the fetch or execute call
********************************************************************************
SQL ID : 07bm6cj6a98vn
select *
from
chris.test where col2 = 'test1'
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 1 0.00 0.03 6 7 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3 0.00 0.03 6 7 0 1
Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: SYS
Rows Row Source Operation
------- ---------------------------------------------------
1 TABLE ACCESS FULL TEST (cr=7 pr=6 pw=6 time=0 us cost=4 size=6 card=1)
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 3 0.00 0.00
SQL*Net message from client 3 0.23 0.24
db file sequential read 1 0.01 0.01
db file scattered read 2 0.01 0.01
********************************************************************************
似乎是两次物理读取,一共读取了6个数据块(db_file_multiblock_read_coun = 3),是这样么?