1 2 3 Previous Next 32 Replies Latest reply on Apr 7, 2008 3:22 PM by 26741 Go to original post
      • 15. Re: Block Size

        As per one of the document Block size should be set accourding to the OS block size.

        How to determine OS block size.Its there any matrix that will tell OS block size.

        • 16. Re: Block Size
          Richard Foote
          Hi Don

          Why do you insist on quoting the KEEP and RECYCLE pool documentation when discussing multiple blocksizes ?

          You've already admitted getting multiple pools and multiple block sizes "arguments intermingled" here.

          And yet you do it again and again ...

          You do of course realise non default blocksizes can't use either the KEEP or RECYLCE pools.

          Those interested in why multiple blocksizes may not be such a good idea may find these discussions of interest:

          Larger index blocksize tablespace and multiblock reads.

          Larger index blocksize tablespace and multiblock reads part II.

          Larger index blocksize tablespace and index height.

          Larger index blocksize tablespace and small index scans.


          Richard Foote
          • 17. Re: Block Size
            Charles Hooper
            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

            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?
            I did search the documentation prior to posting my previous response to your post in this thread. I did not find in the documentation "multiple blocksizes are indeed beneficial in large databases to eliminate superfluous I/O" - that comment was included in your original response in this thread.

            I did, however, find in the documentation, and I provided references to that documentation, where Oracle states that "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." And, I found: "Note: The use of multiple block sizes in a single database instance is not encouraged because of manageability issues."

            Some of the Oracle documentation recommended selecting the default database block size wisely to minimize the frequency of row chaining:
            "[Smaller blocks, possibly < 8KB] 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." Also interesting is the recommendation against using large block sizes for indexes: "[Larger blocks] Not good for index blocks used in an OLTP environment, because they increase block contention on the index leaf blocks."

            It is unfortunate that the confusion continues. Mr. Burleson, let me ask you something. When your financial advisor recommends that you diversify your investments, do you go to the bank and request a that the teller withdraw your money such that 20% of what you receive is in pennies, 20% is in quarters, 20% is in half dollars, 20% is in U.S. paper currency, and the remaining 20% is in Euros? Of course not, because that is not what your financial advisor recommended.

            Please do not try to twist my words. I did not suggest that "eliminating superfluous I/O is a not a benefit?” Nor did I suggest “that the documentation is wrong." - just as your financial advisor did not tell you to take out 20% of your money in pennies. I quoted directly from the Oracle documentation information that would hopefully eliminate any confusion that I found with the posting, and hopefully any confusion that others found.

            Mr. Burleson, I have purchased a fairly large number of Oracle books using a portion of what is left of my paycheck after taxes. Before I purchase a book, I always research the book's author to try to make a determination if the book's contents will either help or hinder my knowledge of Oracle (I assume that I am not the only one who does this). So far, I have not purchased any books with you as an author or as a contributor.

            Think about this before posting a reply. Will your response, and your interpretation of the Oracle documentation, help someone such as myself to be more likely to purchase one of your books, less likely to purchase one of your books, or is your reputation so solidified that your comments do not make a difference one way or another?

            I try to avoid being confused as best as possible.

            The above is not meant to be insulting to you, but should be kept in mind when replying to someone's post. In your future posts in other threads, please try to convince me through the quality of your responses that I need to purchase one or more of your books.

            Charles Hooper
            IT Manager/Oracle DBA
            K&M Machine-Fabricating, Inc.
            • 18. Re: Block Size
              Greg Rahn
              The answer is always 8k.
              No, not in my experience. . . . . .
              It seems you've quoted my statement out of context.
              I wrote: "If someone has to ask what block size they need. The answer is always 8k."
              Paraphrased from the official Oracle documentation:"If you are uncertain about which block size to choose, then use a database block size of 8 KB"[1]
              And you wrote Don: "IMHO it's nonsense to question the validity of Oracle's own documentation. They wrote Oracle, they know how it works."
              So it appears that we both agree with the Oracle documentation: 8K should be used when one is uncertain or has to ask.
              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.
              Is it a myth? I haven't seen any quantification of possible gains in any official Oracle documentation. If I have overlooked this please point me to it.
              Seems to me that even you think there is little gain: "Multiple blocksizes are best when used by a seasoned DBA who is familiar with the implications of multiple data buffers, and who understands that the marginal benefits come at a cost."[2]
              So based on both of our statements I think we agree there is little to gain. "Little to gain" and "marginal benefits" mean the same to me and probably most people.

              Give the OP is uncertain and has a handle of LEARNING_DBA, I think it is probably safe to say that this person not a "seasoned DBA" so it is probably best for this person to choose 8K for a block size.
              After all, you say that it's wrong and dangerous, they should listen, right?
              I haven't used the words wrong or dangerous in this post. Where are you pulling that from? Please cite your references.

              Keep in mind the OP and keep this about topic at hand. I refuse to participate if you turn this unprofessional and will not hesitate to point out an inappropriate response to the moderators.

              [1] http://download.oracle.com/docs/cd/B28359_01/server.111/b28274/iodesign.htm
              [2] http://www.dba-oracle.com/t_multiple_blocksizes_summary.htm


              Greg Rahn
              • 19. Re: Block Size
                I followed both of your links and neither one provides a repeatable test case.

                Neither provides information on hardware
                Neither provides information on operating system and its configuration
                Neither provides the DDL to build the test case
                Neither provides the DML run
                Neither provides the test design ... how was it run, was the shared pool flushed, etc.

                In short ... neither meets the minimum level required for someone to validate the resulting analysis and opinion. And, as Charles Hooper points out, it is irrelevant to the original question that started the thread.
                • 20. Re: Block Size
                  You wrote:
                  "Not in my experience."
                  and that is precisely the reason you seem, so very often, to be a loggerheads with essentially every respected member of the international Oracle tuning community.

                  When one person tells you you are wrong ... well that's just their opinion.
                  When two, or three, or five tell you you are wrong ... you should start to pay attention.
                  When essentially the entire Oak Table membership tell you the same thing then you should consider the very real possibility that you are wrong.

                  Do you have any idea who Greg Rahn is?
                  And you are quoting Oracle docs to him?
                  Good grief!

                  My suggestion would be to reconsider what you are doing. The only place where repeating incorrect information makes sense is in politics.
                  • 21. Re: Block Size
                    Charles Hooper
                    Mr. Rahn,

                    Thanks for replying to my post and helping to clarify the confusing content.

                    By the way, very interesting articles on your website.

                    Charles Hooper
                    IT Manager/Oracle DBA
                    K&M Machine-Fabricating, Inc.
                    • 22. Re: Block Size
                      [Charles, I'm not particularly replying to you.  I wanted to add to this thread
                      and had to use the "Reply" icon so I just clicked on "Reply" against your
                      posting,  but my posting is actual to all the contributors here]

                      Would large block sizes in a seperate tablespace make sense for LOBs only,
                      eg if we use Out Of Line LOB Segments for LOBs (holding say, 100K or more
                      of data per row), when the rest of the database uses the generic 8K block size ?

                      {adding to the "100K" reference :}
                      For a LOB that is stored Out Of Line, if the typical
                      size is, say 32K or 64K, I might still stick to an 8K
                      block size. If it is, say 1M, I might consider a 16K
                      block size (or should I ?). If it is say, 100M, I
                      would go for a 32K block size. But I would also,
                      probably, define that large a LOB column as NOCACHE.
                      ?? comments ?

                      Even the repeated "case" of Transportable Tablespaces is too generalised.
                      DataWarehouses, too, may be setup with 8K block sizes , the same as the OLTP
                      databases they are built from. So we have the same block size and we can use
                      Transportable Tablespaces IF we really have data that is tranported "as is" rather
                      than extracted through other routines (PLSQL over DBLinks, ETL tools like
                      DataStage etc).

                      added comments about the 100K reference in LOBs
                      Message was edited by:
                      Hemant K Chitale
                      • 23. Re: Block Size
                        some basic guidelines are

                        For tables having more access via index or "table access by rowid" or random select small blocksize is beneficial

                        If the average row length is more then use larger blocksize for that table. This is for avoiding row chaining.

                        If table is having more "FULL TABLE SCANS" then use bigger blocksize

                        Tables that are having lots of random updates or deletes should be placed in small blocksize tablespaces

                        Tbales with LONG or LONG RAW columns should have large blocksize

                        If the average length of the LOB is more than500 bytes then it is better to store the LOB in a seperate segment (use DISABLE STORAGE IN ROW )

                        • 24. Re: Block Size
                          Thank you all for your reply.

                          Is there any OS specific things which need to be checked like block size.

                          • 25. Re: Block Size
                            The Oracle Block Size should be equal to or a multiple of the OS Block Size,
                            but not a factor of or less than it.

                            eg if the OS Block Size is 4K, you can have 4K and 8K Oracle Blocks, but
                            don't go for 2K Oracle Blocks.

                            Since the general advice is for 8K Oracle Blocks, it also makes sense to go
                            for 8K Blocks for the Filesystems holding Oracle Database Files.

                            However, LogWriter I/O is done in [generally] 512 byte writes so seperate your
                            Redo Logs. Or put them on Raw Devices.
                            • 26. Re: Block Size
                              Hi Charles,
                              I did search the documentation prior to posting my previous response to your post in this thread. I did not find in the documentation "multiple blocksizes are indeed beneficial in large databases to eliminate superfluous I/O"
                              I may have mis-quoted that, I'll check and correct it, thanks!

                              Again, I can only speak from my own experiences, and my books are heavy on pragmatism, and short on theory. They are not for everybody . . . . By the very nature of my work with some of the largest Oracle shops in the world, I'm not allowed to disclose any details, so I only report what my staff sees.

                              This argument about fixed OS block sizes dates back to Oracle7. I remember taking an Oracle database from 2k to 8k in Oracle7, and getting a huge improvement in both throughput and response time.

                              I've also tested many system where different blocksizes makes very little difference in elapsed response time.

                              I agree, that for most databases, 8k is fine, but for VLDB databases with over 100k LI's/ sec., even "Marginal" performance improvements can save their bacon.

                              I guess it's how we define "marginal". . . .

                              • 27. Re: Block Size
                                Hi Greg,
                                I haven't seen any quantification of possible gains in any official Oracle documentation.
                                Try here: http://oss.oracle.com/~mason/blocksizes/

                                It's also taught as a fact in the intro DBA class at Oracle university, where they teach guidelines for choosing the "right" blocksize . . . .

                                I refuse to participate if you turn this unprofessional
                                I only flame people who make racist remarks, fake self-appointed experts with no real-world job experience, and people to act unprofessional to me . . . . .
                                I haven't used the words wrong or dangerous in this post.
                                That was not intended to put words into your mouth. I got the impression that you did not agree with the docs and metalink, and that you had proof. If so, I was encouraging you to bring it up, and understand why it remains there.
                                "Little to gain" and "marginal benefits" mean the same to me and probably most people.
                                Ah, therein lies the rub. To a high-volume shop, a 5% response time improvement can be huge.

                                Regarding proof, I was at a client site a few months, and they complained that Oracle Corporate consulting sent-in an expert who recommended 8k blocksizes for their RAC cluster. When Oracle Corp. failed to help them, my guys changed their block size to 2k and they experienced a dramatic performance improvement.

                                The client would be glad to share this, but they would appreciate a full refund for Oracle's failed tuning engagement. In their words, Oracle corporate consulting "didn't do much", and gave them bad advice.

                                Would that be acceptable?

                                If so, please send an e-mail.

                                I'll also check and see if some of my clients would be willing to share their evidence, BUT ONLY oif you agree to publically acknowledge it, and refund those clients who were disappointed by Oracle's advice.

                                Everyone notes a change in response time with different blocksizes with different block sizes, the argument is the degree of the "marginal" benefit.

                                The response time difference happens outside Oracle, and the external disk I/O latency gives us clues:


                                BTW, how do you explain the differences in throughout and response time when using different blocksizes?
                                • 28. Re: Block Size
                                  You don't know the full disclose rules on TPC.org?

                                  To quote you "Good Grief". You are one of those folks who hides their credentials, so I have no way of determining whether you have any work experience with Oracle, so who knows? Here, fully reproduceable benchmarks using multiple bocksizes: Enjoy:



                                  These benchmarks use a non-stanard blocksize (and multiple block sizes) for a very good reason, and they spend a small fortune testing these world-record benchmarks. But don't take my word for it, call the vendor and ask.

                                  These are fully reproduceable, just run them yourself.
                                  • 29. Re: Block Size
                                    Hans Forbrich
                                    It's also taught as a fact in the intro DBA class at
                                    Oracle university, where they teach guidelines for
                                    choosing the "right" blocksize . . . .
                                    Which class? I can't seem to find the reference. Of course, the search was somewhat cursory - only check the 10gR2 DBA-1, DBA-2, SQL Tuning, Performance Tuning, NewFeat, Managing on Linux and Data Warehousing course material . I could easily have missed something.

                                    I do note that the 10g Performance Tuning course, page 9-35, states "The primary intention of the multiple block size feature is to be able to support transportable tablespaces across databases with different block sizes. It is not intended as a performance feature."

                                    And in the Data Warehousing course, on P. 14-13, they talk abouut "Larger block sizes may yield better compression, in general. " but elsewhere state "Larger block sizes may yield better compression, in general. "