Re: Question on diff block sizes in DB
- From: "Jonathan Lewis" <jonathan@xxxxxxxxxxxxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Sun, 18 Apr 2004 21:03:15 +0100
In your case I'd worry more about the stripe size than
the block size. Think what's going to happen with a
multiblock read:
At 8 blocks per read
32K per block, you get a 256K read.
With a stripe size of 64k, That's a minimum of 4 discs
kicked into action on a single read. Now imagine you
kick off a parallel tablescan: how many devices do you
actually have for your 17GB, and how much I/O contention
will a single parallel request cause on your system.
If you are stuck with a 64K stripe size, I'd consider
keeping your multi-block read size down to 64K in
the hope of minimising contention..
As far as use of different block sizes go, I think others
have already made the key points. You have to know
what your actual data access paths are so that you can
predict the pros and cons. But as a special consideration,
can you guarantee that the boundary of a 32K Oracle data
block will always synchronize with the boundary of a 64K
stripe ? If you can't then perhaps one block out of every
two will span two stripes, and therefore activate two
disk drives. If you switch to 8K blocks then it's only
one block in eight that would have the problem.
Regards
Jonathan Lewis
http://www.jlcomp.demon.co.uk
The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html
April 2004 Iceland http://www.index.is/oracleday.php
June 2004 UK - Optimising Oracle Seminar
July 2004 USA West Coast, Optimising Oracle Seminar
August 2004 Charlotte NC, Optimising Oracle Seminar
September 2004 USA East Coast, Optimising Oracle Seminar
September2004 UK - Optimising Oracle Seminar
----- Original Message -----
From: "Mike Schmitt" <mschmitt@xxxxxxxxxxxx>
To: <oracle-l@xxxxxxxxxxxxx>
Sent: Friday, April 16, 2004 10:49 PM
Subject: Question on diff block sizes in DB
Hello list,
I was wandering if anyone had any experience or recommendations for the
following. We will be implementing a database that will be similar to a
small warehouse having some tables that will be fairly well partitioned and
be sized/grow 17G a year. Along with these we will have some smaller
lookup tables that we do not know the sizes of. I looked over the
suggested spec today and had some questions related to using different
database block sizes within the database (database will be 9.2.0.4)
All Data and Index Tablespaces reside on: 32k block size tablespaces, 256k
VM I/O read size, 64k strip width on SAN (SAN stuck at 64k strip size),
with db_file_multiblock_read_count of 8
All System, Temp, undo, lookup table tablespaces reside on: 8k block size
tablespaces, 64k VM I/O read size, 64k strip width size on SAN (SAN stuck
at 64k strip size), with db_file_multiblock_read_count of 8
Since the system will not be using transportable tablespaces, is there much
of a benefit to separating this data into different block sizes. I
initially felt that since we were only dealing with the System tablespace
and a group of lookup tables, the design/administration would be easier to
keep everything uniform(block size, VM IO). I also felt that we should at
least use the 32k block size for the temp tablespace and possibly Undo
tablespace as well.
Since I do not have any experience with using databases with different
block sizes, I was hoping someone might share their experience and give
advice against what has been spec'd
Much appreciated
Thanks
----------------------------------------------------------------
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:
