1 2 3 Previous Next 38 Replies Latest reply on Nov 11, 2007 11:58 PM by 181444

    Index blocksize

      What's the best blocksize value to tablespaces of indexes? Large or small blocksize? Why?

      Thanks a lot
        • 1. Re: Index blocksize
          Greg Rahn
          Stick with the default block size for the instance. The administration overhead of tuning multiple db caches isn't worth it IMHO. You may find yourself de-tuning the db by accident.

          You may read or have read that multiple block sizes are used in TPC-C and some claim better performance with it. Do note, none of these claims (that I have seen) are for more than one operation (either scans or inserts). I also have never seen any production database workload statistics that demonstrate significant added benefit and site the effort involved to get there (cache sizing, etc), the examples given are always single operations.

          One other notable on the TPC-C claims: The TPC-C workload and schema has not changed since 1992 when the specification was approved. The workload is very well understood and run in a controlled environment. Customer production workloads vary often and the data model is constantly evolving, so database operations need to continuously adapt/react to the changes.

          I would suggest using the 8k block for you database. Its right in the middle so you do not put yourself in any edge conditions. Call it the "Goldilocks" block - not too big, not too small, just right. :)

          Greg Rahn
          • 2. Re: Index blocksize
            Hi Greg,

            As a member of the Real World Performance Group, your experience is unparalleled, and I'm hoping that you might share Oracle's experiences.
            You may find yourself de-tuning the db by accident.
            Could you elaborate, please? What sort of accidents might happen?

            What do you think about the concept of using a non-standard larger blocksize for indexes that experience primarily large index range scans or index fast-full-scans?

            I say this because Robin Schumacher showed that indexes will build "flatter" in a larger blocksize, and that more keys could be stored per data block, suggesting that it might be a worthwhile effort for some types of systems.

            Please advise. Thanks!
            • 3. Re: Index blocksize
              The more keys in an index block the greater the odds that multiple user sessions will require new keys be inserted or deleted from the block. This in turn raises the odds that redo will have to be accessed to support a consistent read that uses the index.

              For infrequently accessed objects a larger index block size insures more buffer space consumed by key entries that are not going to be referenced in a short period of time.

              Better overall database results are likely to result if the entire database uses one standard block size unless the DBA keeps close tabs on the various buffer pool utilizations.

              The actual results depends on how many pools are definded, how well the pools were sized, the application, and changes in the application usage over time. For the majority of DBA's it is probably better to have one buffer pool and to let Oracle manage it.

              IMHO -- Mark D Powell --
              • 4. Re: Index blocksize
                Hi Mark,
                For the majority of DBA's it is probably better to have one buffer pool and to let Oracle manage it.
                I agree, especially for professionals for whom being a DBA is not their full-time duty. However, a senior DBA who knows what they are doing is a different story, IMHO.
                unless the DBA keeps close tabs on the various buffer pool utilizations.

                Message was edited by:
                • 5. Re: Index blocksize
                  As usual you quoted someone out of context.

                  The full paragraph reads

                  Better overall database results are likely to result if the entire database uses one standard block size unless the DBA keeps close tabs on the various buffer pool utilizations.

                  You exactly deleted what didn;t suit you. As you usually do.
                  Don't you have any ethics???

                  Also most professional DBAs have to manage multiple databases and consequently doesn't have the luxury to micromanage a database.

                  Consequently, a senior DBA who know what he is doing doesn't waste his time on this kind of maintenance, and tries to solve problems instead of fighting symptoms.

                  Sybrand Bakker
                  Senior Oracle DBA
                  • 6. Re: Index blocksize
                    Don't you have any ethics???
                    Yeah right. This, coming from someone who just published than I'm a "drugs dealer"?

                    Ha! Your quote is right up there with "Children's do learn". . . .

                    Tell me Sybrand, how does it feel to be a well-known racist?


                    "Color me unsurprised that it’s Sybrand. He’s haunted comp.databases.oracle.* for years with his crap." - Don Seiler
                    Consequently, a senior DBA who know what he is doing doesn't waste his time on this kind of maintenance
                    How on earth would YOU know? I don't believe that your real name is Sybrand Bakker, and I don't believe that you are an Oracle DBA, much less a senior DBA.

                    You are just an anonymous troll, who offers only bigoted offensive blather . . . .
                    • 7. Re: Index blocksize
                      Richard Foote
                      Hi Don

                      Let me explain briefly why a non default 32K block size for indexes is not a particularly clever thing to implement.

                      1) It requires a separate cache, which requires an additional level of administration, which results in Oracle having to manage an additional and separate memory structure, which increases the possibility of memory being wasted by objects being unnecessarily cached or objects being aged out prematurely

                      2) Objects that use non default buffer pools are managed independently and can not be stored in either the keep or recycle pools which means again index objects can either waste memory or be aged out prematurely.

                      3) Note these non-default block size memory structures must be manually tuned (even in 11g) which strongly hints at Oracle Corp's direction for these buffer caches

                      4) You mention that 32K indexes will have a flatter structure. For many indexes, this will simply not be true. Many indexes in a 32K block size will actually have an identical height to a (say) 8K block index anyways as a 32K block can only store 4 times the data of an 8K block but an individual branch block can point to many hundreds of blocks at the subsequent level and indexes exponentially grow as the height increases.

                      5) If they don't, the index height will only likely differ by one level only

                      6) The difference in index height is very unlikely to make a significant difference to either the performance of a specific scan or to the cost of a specific index scan anyways. Indeed since 10g, Oracle doesn't even include the index blevel in it's costings anymore if the index height is less than a certain level.

                      7) It's actually the index width, not height that might make more of a difference due to the potential reduction of LIOs for very large index scans but again such large scans would likely only have a relatively small proportion of index blocks to table blocks anyways.

                      8) Say you have an 32K block index with 2 levels, a unique scan MUST read the 32K root block AND the 32K leaf block for a total of 64K. If (and it's a big if) the 8K block index had three levels, that's 8K for the root block, 8K for the additional branch block and 8K for the leaf block. Yes, that's 3 LIOs (2 of which are very very likely memory reads, if not 3) but 24K of memory, 24K of disk accesses in a worst case scenario, 24K of memory contention vs 64K minimum.

                      And 32K block size is more efficient ?

                      9) A 32K block is more likely to have contention than a 8K block

                      10) You mention the benefits of a fast full index scan with 32K blocks. A fast full index scan uses multiblock reads and Oracle will read exactly the same amount regardless of the block size (it will simply read fewer blocks at a time for the non default 32K block). There is no practical benefit or difference in FFIS between 8K and 32K, none.

                      11) You mention Robin Schumacher and how it was shown how doubling the block size, Oracle halved the number of LIOs. This of course is an obvious consequence. However, what's not so obvious and was not mentioned was the fact the actual cost of LIOs for the larger block size also increases. Robin would be far less impressed by the results if each run was actually timed and measured.

                      12) TPC-C use non default block sizes so they must be good right ?

                      Wrong. TPC-C uses artificial (and frankly outdated) workload metrics and requirements that don't accurately reflect the requirements of "real-life" databases. I can't name one working environment that would ever seriously use a TPC-C database configuration. The fact TPC-C uses non default blocks sizes should act as a warning, not as a recommendation.

                      In summary, using a non default 32K block size for all your indexes is a really bad idea, not least because if adds complexity for no practical performance benefit.

                      It's useful if you need to transport a non default block size tablespace from another database but that's about it.


                      Richard Foote
                      • 8. Re: Index blocksize
                        Hi Richard,

                        Please note these recent TPC-H benchmarks using multiple blocksizes:


                        Before you dismiss them, bear in mind that HP spent over a month doing timings before publishing these benches. There are several worldwide mega-systems (that you would recognize) who use multiple blocksizes with great success, and I’ll contact their CIO’s and see if it’s OK to disclose their names.

                        I wholeheartedly agree about the additional management overhead with multiple buffer pools, but for a very different reason. I also agree that for most DBA’s, it’s too much hassle. But for a multi-terabyte OLTP or warehouse, it has made a huge difference in improved manageability.

                        Me, I like managing my expensive RAM resources at the lowest granularity possible, and multiple buffer segregation allows me to have much more control over I/O, especially when I can morph the buffers with “alter system” statements.

                        Just curious, what do you think about using KEEP pool caching?

                        I don’t completely aggree with your observations, but I’ll add them to my multiple blocksizes notes as “not everyone agrees”.

                        Oh, do you have a link to a whitepaper on your advanced index internals that I can cite?

                        Message was edited by:
                        • 9. Re: Index blocksize
                          Don Burleson,

                          Why is it always when you stand corrected,you resort to flame and slander?
                          Don't you think it tells everything about your personality?
                          How come you never ever support your 'advice' with repeteable proof, but always show up with the authority argument ('savvy professionals'), suggesting you are the only savvy professional? Why you always come up with insults? (People who don't use multiple blocksizes are no professional DBAs).
                          Why do you always threaten with lawyers and try to discredit colleagues?
                          Why do you continue to market and spam your idiotic advice in this forum, promoting your own boasting publications (The definitive tuning Guide), ignoring the 'Terms of use' for these forums?

                          As to your insults:
                          You have now gone way too far.
                          Your insults show how much of a professional you are.
                          Actually it is a bloody shame people like you call themself 'DBA'.
                          I DEMAND you IMMEDIATELY stop insulting me and making claims for which you have no proof at all.

                          Syrbrand Bakker
                          Senior Oracle DBA
                          • 10. Re: Index blocksize
                            You have now gone way too far.
                            Nah, what you write is way too far:

                            “If there are any competent Asians, I failed to meet them.” . . . .

                            Like Jon Emmons says, I have a right to speak-out against you:

                            "More amazing to me is that Oracle doesn’t pursue and remove these posts. After all, for every person who will speak up against this racist crap there are ten who will just leave with a bad taste for the people who host the content."

                            You are a disgrace to the Oracle community, a reminder that, left unchecked, OTN will become a dictatorship of anonymous idiots and bigots.
                            How come you never ever support your 'advice' with repeteable proof
                            Here's a proof, you just changed the spelling of your fake name! Do you expect us to believe that you cannot even spell your own name correctly?

                            So, what's your "real" name today, "Syrbrand Bakker" or "Sybrand Bakker"?
                            • 11. Re: Index blocksize
                              Sybrand, I do not think Don's quoting in his response to my post was inappropriate. It even appears he conceded my points while arguing that experienced DBA's who do monitor their buffer pools can benefit from using multiple block sizes. I think it is possible but much more difficult in practice than the later referenced tests would leave you to believe.

                              I think we should all try to stick to the Oracle issues and leave past actions/words out of the current discussion.

                              IMHO -- Mark D Powell --
                              • 12. Re: Index blocksize

                                Sybrand Bakker
                                Senior Oracle DBA
                                • 13. Re: Index blocksize
                                  As long as 'Mr' Burleson continues to harass and to flame me with past contributions I am afraid this won't stop.
                                  'Mr' Burleson has a long history of spouting foul flames when demonstrated wrong.
                                  Just check his flamewars against Tom Kyte and Jonathan Lewis.

                                  Sybrand Bakker
                                  Senior Oracle DBA
                                  • 14. Re: Index blocksize
                                    Richard Foote
                                    Hi Don

                                    You said a number of times to consider large non-default block sizes for indexes that have lots of Fast Full Index Scans. Just so we can peel the layers off large blocks sizes for indexes one at a time, I'd be interested in the answer to this question.

                                    Considering Fast Full Index Scans use multiblock reads, and considering Oracle automatically adjusts the number of blocks it reads such that the total read size matches the default block size value (eg. if the db_file_multiblock_read_count = 16 and the default block size is 8K, then only 4 x 32K blocks are read), why precisely should we consider large block sizes for Fast Full Index Scans ?

                                    There is a very large white paper (which some better describe as a book ;) which is a work in progress. It's currently being expanded yet again to include 11g research. One day, it might get to see the light of day, maybe ?


                                    Richard Foote
                                    1 2 3 Previous Next