Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 1 down vote favorite

From the logical data separation point, there are tablespaces that consist of segments that consist of extend that consist of oracle blocks that consist of OS blocks. What logical structure actually hold data of concrete record (where the data of all fields of the same record reside side by side in filesystem). In the world of MySQL, there's data page (8, 16 KB) that holds one of more records at the same physical address.

Also, how to determine what size if the best? The more such pages exist the more data fragmentation will appear, and that might affect performance.

share|improve this question

1 Answer

up vote 3 down vote accepted

The block is the structure that holds row data. The Logical Storage Structure documentation has a detailed overview of all this.

Note that data for a single row can reside on more than one block (this is called row chaining), so it need not be contiguous on disk. Row migration can also leave just a "forwarding address" in the row's original block that points to the row's new block of residence.

All I/O to and from the SGA is done in block-sized chunks (possibly more than one block at a time, but, for the SGA, never smaller than a block).
The block size of the SYSTEM tablespace determines the database's "natural" block size. You can have other tablespaces with different block sizes but:

  • you can't mix block sizes inside a same tablespace
  • you will need to set up separate cache areas for each block size (you can't have for example 8k blocks and 16k blocks in the same block cache)

So in general, it is simpler to use only one block size, if only to avoid the administrative and tuning overhead of managing the size of multiple block buffers.

How you determine the optimal block size for a database is a compromise just like pretty much everything else.
Smaller blocks means your SGA is more granular, but each block has relatively more overhead. And wide rows in small blocks will be more likely to chain or migrate which can be a source of performance problems.
Larger blocks means less chances of chaining (assuming PCTFREE and PCTUSED are reasonably set). But it also means large single-block I/Os and less granularity in your SGA. Since a change in any row of one block requires the whole block to be written out to disk, larger block sizes could lead to more write I/O volume than smaller ones.
Another factor to take into account is that traditional (not bigfile) datafiles are limited to 4M blocks. That's about 32G for 8k blocks, but only 8G for 2k blocks. Since the number of files per (non-bigfile) tablespace is limited (to 1022), this could be important. (There is also a maximum of 65533 files per database, possibly less. See physical database limits.)

Transactional systems tend to use relatively smaller block sizes than datawarehousing systems, but that really isn't anything like a hard and fast rule. (e.g. current Oracle best practice guidelines on Exadata is 8k blocks regardless of scenarios.).

share|improve this answer
Thanks :) But why "a change in any row of one block requires the whole block to be written out to disk"? I always thought, if an update occurs to the row in block and some data of the record cannot fit in the same block then the remaining data will be placed somewhere else in some other block that has empty space (with a pointer where to the block where it was placed). It would be costly to override the whole block (for example other rows that didn't changed). – Centurion Sep 17 '12 at 10:53
I guess such behavior could be made in IOT tables where data of the rows are organized by index, but then an update to one row could trigger the shift of all data in all rows in other blocks. – Centurion Sep 17 '12 at 10:55
If anything in a block changes (header, ITL, table directory, row directory or actual row data), the whole block is dirty and needs to be written. Updating just one column of one row requires the writeout of the whole block. In other words, you never write less than one block's worth of data. If you get a chained or migrated row, you'll modify at least two blocks, so write out at least two also. – Mat Sep 17 '12 at 11:12
Indeed, same thing for index blocks or IOT blocks. A block split will write two whole leaf blocks, plus maintenance of the higher levels. – Mat Sep 17 '12 at 11:15

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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