Re: db_file_multiblock_read_count and performance

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? 
> --
> http://www.freelists.org/webpage/oracle-l
> 

-- 
Regards

Wolfgang Breitling
Centrex Consulting Corporation
www.centrexcc.com
--
http://www.freelists.org/webpage/oracle-l

Other related posts: