1 2 3 Previous Next 32 Replies Latest reply: Apr 7, 2008 10:22 AM by 26741 RSS

    Block Size

    LEARNING_ORACLE
      Hi,

      How can I determine which block size is correct for my database.

      And

      Steps for chaning Block size.

      Thanks
        • 1. Re: Block Size
          299178
          Generally 8kb block size holds good for most of the database out there. To change the block size, you have to recreate the database.
          • 2. Re: Block Size
            601585
            General rule(too general...)
            - 8K on OLTP
            - 16K on 32K on DSS(DW)

            On hybrid system(OLTP+DSS)? Well...

            I would always stick to 8K standard block size.
            Benchmark! Benchmark! Benchmark!
            All you need to do is just to test and compare yourself.

            Dion Cho
            • 3. Re: Block Size
              LEARNING_ORACLE
              Thanks for your reply.

              But How can I determine.There must be few guidelines.

              How/What to test.

              How/What to compare.
              • 4. Re: Block Size
                247514
                Check Oracle Performance Tuning Guide,

                8.2.6 Choosing Data Block Size

                http://download.oracle.com/docs/cd/B19306_01/server.102/b14211/iodesign.htm#i19636
                • 5. Re: Block Size
                  601262
                  Use 8k. This is right in the middle, and won't put you in an edge condition. Call it the Goldilocks block, not to small, not to big, just right.

                  For both OLTP and DSS, 8k is an optimal size. I use 8k, always.

                  There is minimal gains to be had in messing with block sizes. Having good db design and good execution plans is a better place to worry about performance.

                  --
                  Regards,

                  Greg Rahn
                  http://structureddata.org
                  • 6. Re: Block Size
                    damorgan
                    To paraphrase Brynn Llewellyn:

                    The right size is 8K because that is the only size Oracle tests.

                    All of the stuff promoted by some people about changing block sizes based on the type of database, OLTP or OLAP, etc., still awaits laboratory testing to establish whether it is anything more substantive than aromatherapy.
                    • 7. Re: Block Size
                      108476
                      Hi,
                      There must be few guidelines.
                      Oh yes. The goal is simple; we want to maximize the amount of available RAM memory for the data buffers by setting the block sizes according to the amount of I/O experienced by the table or index. Random access of small rows suggests small block sizes, while sequential access of related rows suggests large block sizes.

                      Multiple blocksizes:
                      *****************************************************
                      the Oracle 11g Performance Tuning Guide notes that multiple blocksizes are indeed beneficial in large databases to eliminate superfluous I/O and isolate critical objects into a separate data buffer cache:

                      “With segments that have atypical access patterns, store blocks from those segments in two different buffer pools: the KEEP pool and the RECYCLE pool.

                      A segment's access pattern may be atypical if it is constantly accessed (that is, hot) or infrequently accessed (for example, a large segment accessed by a batch job only once a day).

                      Multiple buffer pools let you address these differences. You can use a KEEP buffer pool to maintain frequently accessed segments in the buffer cache, and a RECYCLE buffer pool to prevent objects from consuming unnecessary space in the cache. . .

                      By allocating objects to appropriate buffer pools, you can:

                      Reduce or eliminate I/Os
                      Isolate or limit an object to a separate cache"


                      For RAC
                      ******************************************************
                      It's pretty well established that RAC performs less pinging with 2k blocksizes:

                      http://www.rampant-books.com/t_rssd_2_segregate_allocating_objects.htm
                      ***************************************
                      How/What to test.
                      Test with a real-world workload, using SQL Tuning sets.
                      How/What to compare.
                      Measure for total consistent gets, buffer utilization and disk I/O.

                      There is a lot to it . . . .

                      ********************************************
                      The IBM Oracle Technical Brief titled "Oracle Architecture and Tuning on AIX" (November 2006) notes that careful evaluation is required before implementing multiple blocksizes:

                      While most customers only use the default database block size, it is possible to use up to 5 different database block sizes for different objects within the same database.

                      Having multiple database block sizes adds administrative complexity and (if poorly designed and implemented) can have adverse performance consequences. Therefore, using multiple block sizes should only be done after careful planning and performance evaluation.

                      The paper continues with specific examples of differing I/O patterns that are related to the database blocksize:

                      Some possible block size considerations are as follows:

                      - Tables with a relatively small row size that are predominantly accessed 1 row at a time may benefit from a smaller DB_BLOCK_SIZE, which requires a smaller I/O transfer size to move a block between disk and memory, takes up less memory per block and can potentially reduce block contention.

                      - Similarly, indexes (with small index entries) that are predominantly accessed via a matching key may benefit from a smaller DB_BLOCK_SIZE.

                      - Tables with a large row size may benefit from a large DB_BLOCK_SIZE. A larger DB_BLOCK_SIZE may allow the entire row to fit within a block and/or reduce the amount of wasted space within the block. Tables or indexes that are accessed sequentially may benefit from a larger DB_BLOCK_SIZE, because a larger block size results in a larger I/O transfer size and allows data to be read more efficiently.

                      - Tables or indexes with a high locality of reference (the probability that once a particular row/entry has been accessed, a nearby row/entry will subsequently be accessed) may benefit from a larger DB_BLOCK_SIZE, since the larger the size of the block, the more likely the nearby row/entry will be on the same block that was already read into database cache.

                      But what is Oracle's official position on multiple blocksizes. For Oracle metal-level customers we have the Oracle Metalink system which provides the official position of Oracle's own experts.


                      --------------------------------------------------------------------------------

                      Metalink Note:46757.1 titled "Notes on Choosing an Optimal DB BLOCK SIZE" says that there are some benefits from having larger blocksizes, but only under specific criteria (paraphrased from Metalink):

                      Large blocks gives more data transfer per I/O call.


                      Larger blocksizes provides less fragmentation (row chaining and row migration) of large objects (LOB, BLOB, CLOB)


                      Indexes like big blocks because index height can be lower and more space exists within the index branch nodes.


                      Moving indexes to a larger blocksize saves disk space. Oracle says "you will conserve about 4% of data storage (4GB on every 100GB) for every large index in your database by moving from a 2KB database block size to an 8KB database block size."

                      Metalink goes on to say that multiple blocksizes may benefit shops that have "mixed" block size requirements:

                      What can you do if you have mixed requirements of the above block sizes?

                      Oracle9i "Multiple Block Sizes" new feature comes into the rescue here, it allows the same database to have multiple block sizes at the same time . . . "

                      Hope this helps . . .

                      Donald K. Burleson
                      Oracle Press author

                      Message was edited by:
                      burleson
                      • 8. Re: Block Size
                        108476
                        All of the stuff promoted by some people about changing block sizes based on the type of database, OLTP or OLAP, etc., still awaits laboratory testing to establish whether it is anything more substantive than aromatherapy.
                        With all due respect, it's been tested to death.

                        I cited the Oracle documentation and Metalink notes, and it is consitent with my own experience tuning thousands of databases over the past few decades.

                        IMHO, it's nonsense to question the validity of Oracle's own documentation. They wrote Oracle, they know how it works . . . .
                        • 9. Re: Block Size
                          damorgan
                          If it has been tested to death provide a link to a source that shows the test case and the metrics. If there is a published test case where is it?

                          The only link in your first post was to a book you sell.
                          • 10. Re: Block Size
                            Charles Hooper
                            Hi,
                            There must be few guidelines.
                            Oh yes. The goal is simple; we want to maximize the
                            amount of available RAM memory for the data buffers
                            by setting the block sizes according to the amount of
                            I/O experienced by the table or index. Random access
                            of small rows suggests small block sizes, while
                            sequential access of related rows suggests large
                            block sizes.

                            Multiple blocksizes:
                            *****************************************************
                            the Oracle 11g Performance Tuning Guide notes that
                            multiple blocksizes are indeed beneficial in large
                            databases to eliminate superfluous I/O and isolate
                            critical objects into a separate data buffer cache:

                            “With segments that have atypical access patterns,
                            store blocks from those segments in two different
                            buffer pools: the KEEP pool and the RECYCLE pool.

                            A segment's access pattern may be atypical if it is
                            constantly accessed (that is, hot) or infrequently
                            accessed (for example, a large segment accessed by a
                            batch job only once a day).

                            Multiple buffer pools let you address these
                            differences. You can use a KEEP buffer pool to
                            maintain frequently accessed segments in the buffer
                            cache, and a RECYCLE buffer pool to prevent objects
                            from consuming unnecessary space in the cache. . .

                            By allocating objects to appropriate buffer pools,
                            you can:

                            Reduce or eliminate I/Os
                            Isolate or limit an object to a separate cache"


                            For RAC
                            ******************************************************

                            It's pretty well established that RAC performs less
                            pinging with 2k blocksizes:

                            http://www.rampant-books.com/t_rssd_2_segregate_alloca
                            ting_objects.htm
                            ***************************************
                            How/What to test.
                            Test with a real-world workload, using SQL Tuning
                            sets.
                            How/What to compare.
                            Measure for total consistent gets, buffer utilization
                            and disk I/O.

                            There is a lot to it . . . .

                            ********************************************
                            The IBM Oracle Technical Brief titled "Oracle
                            Architecture and Tuning on AIX" (November 2006) notes
                            that careful evaluation is required before
                            implementing multiple blocksizes:

                            While most customers only use the default database
                            block size, it is possible to use up to 5 different
                            database block sizes for different objects within the
                            same database.

                            Having multiple database block sizes adds
                            administrative complexity and (if poorly designed and
                            implemented) can have adverse performance
                            consequences. Therefore, using multiple block sizes
                            should only be done after careful planning and
                            performance evaluation.

                            The paper continues with specific examples of
                            differing I/O patterns that are related to the
                            database blocksize:

                            Some possible block size considerations are as
                            follows:

                            - Tables with a relatively small row size that are
                            predominantly accessed 1 row at a time may benefit
                            from a smaller DB_BLOCK_SIZE, which requires a
                            smaller I/O transfer size to move a block between
                            disk and memory, takes up less memory per block and
                            can potentially reduce block contention.

                            - Similarly, indexes (with small index entries) that
                            are predominantly accessed via a matching key may
                            benefit from a smaller DB_BLOCK_SIZE.

                            - Tables with a large row size may benefit from a
                            large DB_BLOCK_SIZE. A larger DB_BLOCK_SIZE may allow
                            the entire row to fit within a block and/or reduce
                            the amount of wasted space within the block. Tables
                            or indexes that are accessed sequentially may benefit
                            from a larger DB_BLOCK_SIZE, because a larger block
                            size results in a larger I/O transfer size and allows
                            data to be read more efficiently.

                            - Tables or indexes with a high locality of reference
                            (the probability that once a particular row/entry has
                            been accessed, a nearby row/entry will subsequently
                            be accessed) may benefit from a larger DB_BLOCK_SIZE,
                            since the larger the size of the block, the more
                            likely the nearby row/entry will be on the same block
                            that was already read into database cache.

                            But what is Oracle's official position on multiple
                            blocksizes. For Oracle metal-level customers we have
                            the Oracle Metalink system which provides the
                            official position of Oracle's own experts.


                            ------------------------------------------------------
                            --------------------------

                            Metalink Note:46757.1 titled "Notes on Choosing an
                            Optimal DB BLOCK SIZE" says that there are some
                            benefits from having larger blocksizes, but only
                            under specific criteria (paraphrased from Metalink):

                            Large blocks gives more data transfer per I/O call.


                            Larger blocksizes provides less fragmentation (row
                            chaining and row migration) of large objects (LOB,
                            BLOB, CLOB)


                            Indexes like big blocks because index height can be
                            lower and more space exists within the index branch
                            nodes.


                            Moving indexes to a larger blocksize saves disk
                            space. Oracle says "you will conserve about 4% of
                            data storage (4GB on every 100GB) for every large
                            index in your database by moving from a 2KB database
                            block size to an 8KB database block size."

                            Metalink goes on to say that multiple blocksizes may
                            benefit shops that have "mixed" block size
                            requirements:

                            What can you do if you have mixed requirements of the
                            above block sizes?

                            Oracle9i "Multiple Block Sizes" new feature comes
                            into the rescue here, it allows the same database to
                            have multiple block sizes at the same time . . . "

                            Hope this helps . . .

                            Donald K. Burleson
                            Oracle Press author

                            Message was edited by:
                            burleson
                            I feel compelled to respond to this post. The OP asked a simple question regarding how to determine the correct block size for his database. In my opinion, the above post was both confusing and did not answer the OPs question.

                            How is it confusing?
                            * As has been pointed out at least twice in the Oracle forums, the quote from the Oracle 11g Performance Tuning Guide regarding the use of the KEEP and RECYCLE pools "note that multiple blocksizes are indeed beneficial in large databases to eliminate superfluous I/O and isolate critical objects into a separate data buffer cache." The quoted supporting information from the Oracle documentation does not support the suggestion that multiple block sizes are beneficial in large databases. The KEEP and RECYCLE pools are defined in the DEFAULT block size, and it has been that way since at least the time when Oracle 8i was released. Interesting quotes from the Oracle documentation:
                            "Oracle Database Administrator's Guide 11g Release 1" Page 5-16 PDF page 162
                            "The initialization parameters that configure the KEEP and RECYCLE buffer pools are DB_KEEP_CACHE_SIZE and DB_RECYCLE_CACHE_SIZE. Note: Multiple buffer pools are only available for the standard block size. Non-standard block size caches have a single DEFAULT pool."

                            "Oracle Database Reference 11g Release 1" Page 6-44 PDF page 772
                            "Note: Currently, KEEP and RECYCLE pools only exist for the standard block size."

                            "Oracle Database Reference 11g Release 1" Page 1-49 PDF page 113
                            "DB_KEEP_CACHE_SIZE specifies the size of the KEEP buffer pool. The size of the buffers in the KEEP buffer pool is the primary block size (the block size defined by the DB_BLOCK_SIZE initialization parameter)."

                            "Oracle Database Reference 11g Release 1" Page 1-51 PDF page 115
                            "DB_RECYCLE_CACHE_SIZE specifies the size of the RECYCLE buffer pool. The size of the buffers in the RECYCLE pool is the primary block size (the block size defined by the DB_BLOCK_SIZE initialization parameter)."

                            "Oracle Database Concepts 11g Release 1" Page 3-11 PDF page 99
                            "Multiple block sizes are useful primarily when transporting a tablespace from an OLTP database to an enterprise data warehouse. This facilitates transport between databases of different block sizes."

                            http://download.oracle.com/docs/cd/B28359_01/server.111/b28274/iodesign.htm 8.3.6 Choosing Data Block Size
                            "Note: The use of multiple block sizes in a single database instance is not encouraged because of manageability issues."

                            http://download.oracle.com/docs/cd/B28359_01/server.111/b28274/iodesign.htm Table 8-3 Block Size Advantages and Disadvantages
                            Block Size - Larger, Disadvantages: "Wastes space in the buffer cache, if you are doing random access to small rows and have a large block size. For example, with an 8 KB block size and 50 byte row size, you waste 7,950 bytes in the buffer cache when doing random access." "Not good for index blocks used in an OLTP environment, because they increase block contention on the index leaf blocks."

                            http://download.oracle.com/docs/cd/B28359_01/server.111/b28320/initparams047.htm
                            "The value for DB_BLOCK_SIZE in effect at the time you create the database determines the size of the blocks. The value must remain set to its initial value. For Real Application Clusters, this parameter affects the maximum value of the FREELISTS storage parameter for tables and indexes."

                            As the standard block size for Oracle is 8KB, and has had that as a default for some time, there probably is not much sense in discussing the benefits of increasing from a 2KB to 8KB block size - that only confuses matters.

                            I don't know whether or not readers here have followed the interesting posts by Richard Foote on his blog, but there are several interesting articles related to larger block sizes, and the use of multiple block sizes. One such post:
                            http://richardfoote.wordpress.com/2008/03/26/store-indexes-in-a-larger-block-tablespace-height-reduction-12-myth-five-foot-one/

                            Charles Hooper
                            IT Manager/Oracle DBA
                            K&M Machine-Fabricating, Inc.
                            • 11. Re: Block Size
                              108476
                              provide a link to a source that shows the test case and the metrics.
                              Ask Oracle, they are the one's who published those documents. Here is one from Oracle:

                              http://oss.oracle.com/~mason/blocksizes/

                              And tpc.org has loads of examples, some using multiple blocksizes to achieve top results, fully reproduceable.

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

                              You can take any of these TPC test and run them yourself and see the differences.
                              The only link in your first post was to a book you sell.
                              No, it's a link to a book excerpt . . . .
                              • 12. Re: Block Size
                                601262
                                In my opinion,
                                the above post was both confusing and did not answer
                                the OPs question.
                                So true. If someone has to ask what block size they need. The answer is always 8k.

                                It's no wonder people find Oracle difficult to use - there are too many options to mess it up, and too many people recommending to deviate from the defaults when there is no reason to.

                                As I said, there is little to gain (if even any at all) in deviating from 8k, so focus your efforts on design, well written queries and good execution plans.

                                --
                                Regards,

                                Greg Rahn
                                http://structureddata.org
                                • 13. Re: Block Size
                                  108476
                                  the Oracle 11g Performance Tuning Guide regarding the use of the KEEP and RECYCLE pools "note that multiple blocksizes are indeed beneficial in large databases to eliminate superfluous I/O and isolate critical objects into a separate data buffer cache."
                                  The quoted supporting information from the Oracle documentation does not support the suggestion that multiple block sizes are beneficial in large databases.
                                  Well, it says: "multiple blocksizes are indeed beneficial in large databases to eliminate superfluous I/O"

                                  Charles, are you saying that eliminating superfluous I/O is a not a benefit? Or are you saying that the documentation is wrong?
                                  • 14. Re: Block Size
                                    108476
                                    The answer is always 8k.
                                    No, not in my experience. . . . . .
                                    As I said, there is little to gain (if even any at all) in deviating from 8k
                                    That's a myth. If it were true, all of those contradictory official documents would not say otherwise.

                                    If you says it's true, then why don't you get the Oracle official documentation or the Metalink notes changed?

                                    You work for Oracle, you should be able to prove it to them . . . . After all, you say that it's wrong and dangerous, they should listen, right?

                                    Let me know if you are successful.

                                    Bye!
                                    1 2 3 Previous Next