1

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.

http://houseofbrick.com/improve-performance-as-part-of-a-sql-server-install/

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:

http://blog.dbi-services.com/do-the-block-size-matter/

Then I read something very interesting:

http://www.dba-oracle.com/s_oracle_block_size.htm

Specifically here:

Block Size Advantages

Smaller blocksize:

- Good for small rows with lots of random access.

Larger blocksize:

- Permits reading several rows into the buffer cache with a single I/O (depending on row size and block size).

Block Size Disadvantages

Smaller blocksize:

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

Larger blocksize:

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

Improve this question
3
  • "At my last company...we would setup the HDD to use 16k block sizes." ...Out of curiosity, why not 64 KB?
    – SQL_Deadwood
    Commented Jan 19, 2017 at 5:55
  • @SQL_Underworld Im not really sure, we used Dell servers R210, R410, and R720, SAS drives, im not sure if it makes a difference
    – Anthony Fornito
    Commented Jan 19, 2017 at 13:16
  • I asked because, as accurately stated in the your first link, "the recommended file allocation unit...size for SQL Server data and log drives is 64 KB...since this is one full extent (eight 8KB pages) in SQL Server." I wanted to point that out for your own knowledge and in case you ever want to pass that on to peeps at your previous company. Other allocation units have a performance impact on SQL Server (though lower than 64 KB will be less so than above 64 KB).
    – SQL_Deadwood
    Commented Jan 19, 2017 at 18:12

2 Answers 2

Reset to default
2

Oracle logical block size is orthogonal to the storage logical block size (and the file system block size, for that matter). At a minimum your Oracle block size should be a multiple of the disk block size to avoid reading unnecessary data. If your tablespaces have different block sizes, the smallest of them (block sizes) would need to meet this constraint.

Improve this answer
0
1

Can I split up table reads and specify block size to use?

You can specify the block size at the tablespace level at the time of its creation. Different tablespaces can have different block sizes. A conventional table must have the same block size for all of its segments, even if it is partitioned and partitions are in different tablespaces.

I thought was block size is consistent across the disk and set when the drive is partitioned.

Indeed, but that is a lower layer and a different block size.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.