Re: db_file_multiblock_read_count and performance

From: Wolfgang Breitling <breitliw_at_centrexcc.com>
Date: Mon, 06 Dec 2004 13:59:50 -0700
Message-ID: <41B4C846.6010804@centrexcc.com>

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_at_comcast.net 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

Received on Mon Dec 06 2004 - 15:14:35 CST