I was reading over these 3 articles:
At my last company we only used MS SQL Server and MySQL, and if a server was strictly a database server we would setup the HDD to use 16k block sizes.
I work for a different company running Oracle, we have a small issue every once in a while where the database will lock up for about 2 to 3 seconds when a query reads about 700K rows; we know it's a coding issue where a
where statement needs to be added.
This article shows some metics for running 8k to 16k on various tables of size and weight:
Then I read something very interesting:
Block Size Advantages
- Good for small rows with lots of random access.
- Permits reading several rows into the buffer cache with a single I/O (depending on row size and block size).
Block Size Disadvantages
- Not recommended for large rows. There might only be a few rows stored for each block, or worse, row chaining if a single row does not fit into a block.
- Not good for index blocks used in an OLTP environment, because they increase block contention on the index leaf blocks.
Can I split up table reads and specify block size to use?
I thought block size is consistent across the disk and set when the drive is partitioned.
We have a 3-tier SAN, the Oracle server is virtual if that makes a difference.