RE: I/O and db_file_multiblock_read

From: Baumgartel, Paul <paul.baumgartel_at_credit-suisse.com>
Date: Sat, 9 Dec 2006 14:17:38 -0500
Message-ID: <D97D1FAE0521BD44820B920EDAB3BBAC1663B98C@ENYC11P32005.corpny.csfb.com>

Disk caching apparently can play havoc with results:

app/home/oracle > time dd if=/data/oracle/xxx/u01/xxx_11.dbf of=/dev/null bs=131072 48008+1 records in
48008+1 records out

real 1m21.120s
user 0m0.180s
sys 1m14.020s
app/home/oracle > time dd if=/data/oracle/xxx/u01/xxx_11.dbf of=/dev/null bs=262144 24004+1 records in
24004+1 records out

real    0m9.851s
user    0m0.020s
sys     0m9.830s

Re-running with bs=131072 completed in about 9 seconds as well. Perhaps best to use different files (of same size) for each run?

Paul Baumgartel
CREDIT SUISSE
Information Technology
DBA & Admin - NY, KIGA 1
11 Madison Avenue
New York, NY 10010
USA
Phone 212.538.1143
paul.baumgartel_at_credit-suisse.com
www.credit-suisse.com

-----Original Message-----
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org]On Behalf Of Kevin Closson Sent: Friday, December 08, 2006 6:49 PM
To: kevin.lidh_at_gmail.com; oracle-l
Subject: RE: I/O and db_file_multiblock_read_count

do the "same" thing using dd. Whatever your db_block_size is, plug it in as follows:

$ time dd if=<datafile_for_the tablespace> of=-/dev/null bs=<block_size_in_bytes*16>

then re-run:
$ time dd if=<datafile_for_the tablespace> of=-/dev/null bs=<block_size_in_bytes*128>

please let me know what you find

From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Kevin Lidh Sent: Friday, December 08, 2006 12:46 PM To: oracle-l
Subject: I/O and db_file_multiblock_read_count

I was reading an article about the appropriate setting for db_file_multiblock_read_count. I'm on a HP-UX 11.11 64-bit system with Oracle 9.2.0.7.0. The original value was 16 and I bounced the database and ran a million record full-scan test (10046 trace) and then set the value to 128 (max value) and re-ran the

Please access the attached hyperlink for an important electronic communications disclaimer:

http://www.credit-suisse.com/legal/en/disclaimer_email_ib.html

--
http://www.freelists.org/webpage/oracle-l

Received on Sat Dec 09 2006 - 13:17:38 CST