Re: db_file_multiblock_read_count and performance
- From: Wolfgang Breitling <breitliw@xxxxxxxxxxxxx>
- To: ryan_gaffuri@xxxxxxxxxxx
- Date: Mon, 06 Dec 2004 13:59:50 -0700
To back up my claim that there is a (positive) difference. There has to
be. You are doing far fewer system calls, i.e. context switches, and
likely also far fewer Oracle internal operations. Anyway, I did my own
quick tests:
1) create a test table as a clone of dba_objects.
2) repeatedly insert into the table from itself until the table has
sufficient size (~ 500,000 rows, 6,000 blocks)
3) set dfmrc to 128 and run the following sql several times:
select avg(object_id) from test; (average(object_id) because I DO
want Oracle to read every row, but I don't want it to render those
500,000 rows; it would drown any performance difference).
4) exit the session and repeat with dfmrc=1
The performance difference was noticeable on the client and here is the
extract from the tkprof output:
with dfmrc=128:
select avg(object_id)
from
test
call count cpu elapsed disk query current
rows
------- ------ -------- ---------- ---------- ---------- ----------
----------
Parse 5 0.00 0.00 0 0 0
0
Execute 5 0.00 0.00 0 0 0
0
Fetch 10 1.44 5.77 23922 30245 0
5
------- ------ -------- ---------- ---------- ---------- ----------
----------
total 20 1.44 5.78 23922 30245 0
5
Rows Row Source Operation
------- ---------------------------------------------------
1 SORT AGGREGATE
493680 TABLE ACCESS FULL TEST
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total
Waited
---------------------------------------- Waited ----------
------------
SQL*Net message to client 10 0.00
0.00
db file scattered read 425 0.19
4.48
db file sequential read 27 0.01
0.04
SQL*Net message from client 10 52.55
67.05
with dfmrc=1:
select avg(object_id)
from
test
call count cpu elapsed disk query current
rows
------- ------ -------- ---------- ---------- ---------- ----------
----------
Parse 5 0.00 0.00 0 0 0
0
Execute 5 0.01 0.00 0 0 0
0
Fetch 10 1.94 13.74 22080 30245 0
5
------- ------ -------- ---------- ---------- ---------- ----------
----------
total 20 1.95 13.74 22080 30245 0
5
Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: 5
Rows Row Source Operation
------- ---------------------------------------------------
1 SORT AGGREGATE
493680 TABLE ACCESS FULL TEST
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total
Waited
---------------------------------------- Waited ----------
------------
SQL*Net message to client 10 0.00
0.00
db file sequential read 22080 0.00
12.15
SQL*Net message from client 9 2.76
8.75
The performance difference looks obvious to me.
PS the test system is Oracle 9.2.0.5 on an AIX 5.2 OS, non-ASSM LMT
auto-allocate. Not all multi-block IO used 128 blocks. Actually, the
maximum was 126 curiously enough. However, they were the majority.
ryan_gaffuri@xxxxxxxxxxx wrote:
> I have been testing this extensively over the last few months. I do a full
> table scan with a db_file_multiblock_read_count = 1 and then one = 128( i
> check the 10046 trace to verify i am getting this much) and I see absolutely
> no difference whatsoever in response time.
> i am doing
> select count(*)
> from heap_table;
> I have tested this on windows xp, solaris, with EMC, netapp, and regular old
> cheap off the shelf hard drives. I have tested it in 8.1.7, 9.0,9.1,9.2.
> has anyone see a response time improvement from this parameter anywhere?
> --
> //www.freelists.org/webpage/oracle-l
>
--
Regards
Wolfgang Breitling
Centrex Consulting Corporation
www.centrexcc.com
--
//www.freelists.org/webpage/oracle-l
Other related posts:
