Re: db block size

  • From: tboss@xxxxxxxxxxxxxxxxxx
  • To: oracle-l@xxxxxxxxxxxxx
  • Date: Mon, 21 Jun 2004 10:35:50 -0500 (EST)

We've recently done similar tests to diagnose some I/O issues and discovered
the following (details of test: 100,000 inserts, committing after every
record, then dropping the table).

- Insert activity was almost identical in a 2k versus 8k server.
- Insert activity took about 100% longer in a 32k server (inserting to 
a 32k tablespace).
- Insert activity took about 1300% longer when the 32k server tried
to insert to a 2k tablespace ... 40 seconds versus 9 minutes.
- Disks using Volume Manager outperformed plain file systems by
400% (14 seconds versus 42 seconds).

Matching my block size to my filesystem size (8k on Solaris in my case)
did nothing to help the inserts, but strangely made the "drop table"
run 5 times as fast.  Can anyone explain that?

My short answer to the original question posed (what db_block_size
should I pick) would be this:

- Heavy OLTP: 2k block size (but must be a high contention ... not just inserts)
- Heavy Data Warehouse/DSS: 32k block size
- *Any* other activity; go with standard block size for your OS (8k on unix,
4k on Windows boxes).

Todd


> 
> Dennis,
> 
> Both. I am testing a insert performance on Oracle database where I am
> selecting from a 1M table 1000 rows at a time and inserting into big
> table having 200M records and 4 big indexes. So just wondering if
> different block sizes can help read ahead etc ...currently database
> block size is 8k.
> Any article on db_block_size will be greatly help?=20
> (Already searched on google but still looking for specific article
> references from experts)
> 
> Thanks
> --Harvinder
> 
> -----Original Message-----
> From: oracle-l-bounce@xxxxxxxxxxxxx
> [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of DENNIS WILLIAMS
> Sent: Sunday, June 20, 2004 10:06 PM
> To: 'oracle-l@xxxxxxxxxxxxx'
> Subject: RE: db block size
> 
> Harvinder
>    Are you asking how to choose a block size? Or are you intending to
> have a
> different block size for each type of tablespace?
> 
> Dennis Williams
> DBA
> Lifetouch, Inc.
> dwilliams@xxxxxxxxxxxxx=20
> 
> -----Original Message-----
> From: oracle-l-bounce@xxxxxxxxxxxxx
> [mailto:oracle-l-bounce@xxxxxxxxxxxxx]On Behalf Of Harvinder Singh
> Sent: Sunday, June 20, 2004 7:06 PM
> To: oracle-l@xxxxxxxxxxxxx
> Subject: db block size
> 
> 
> Hi,
> 
> It will be great if u can point me to some white papers/articles =3D
> explaining how to choose the database block size for different kind of =
> =3D
> tablespaces : index ,data ,temp, undo etc...
> 
> Env: 10g on WIN2K
> 
> Thanks
> 
> --Harvinder
> 
> =3D20
> 
> =3D20
> 
> =3D20
> 
> ----------------------------------------------------------------
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> ----------------------------------------------------------------
> To unsubscribe send email to:  oracle-l-request@xxxxxxxxxxxxx
> put 'unsubscribe' in the subject line.
> --
> Archives are at //www.freelists.org/archives/oracle-l/
> FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
> -----------------------------------------------------------------
> ----------------------------------------------------------------
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> ----------------------------------------------------------------
> To unsubscribe send email to:  oracle-l-request@xxxxxxxxxxxxx
> put 'unsubscribe' in the subject line.
> --
> Archives are at //www.freelists.org/archives/oracle-l/
> FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
> -----------------------------------------------------------------
> 
> ----------------------------------------------------------------
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> ----------------------------------------------------------------
> To unsubscribe send email to:  oracle-l-request@xxxxxxxxxxxxx
> put 'unsubscribe' in the subject line.
> --
> Archives are at //www.freelists.org/archives/oracle-l/
> FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
> -----------------------------------------------------------------
> 

----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to:  oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------

Other related posts: