1 2 3 Previous Next 93 Replies Latest reply: Oct 27, 2009 12:09 PM by Billy~Verreynne RSS

    Finding appropriate block size?

    Yasu
      Hi All,

      I believe this might be basic question, How to find appropriate block size for building an database to an specific application?

      I had seen always default 8K block size is used every where(Around 300-350 databases i have seen till now)....but why and how do they estimate this block size blindly before creating production database.

      Also in the same way how memory settings are finalized before creating database?

      -Yasser
        • 1. Re: Finding appropriate block size?
          EdStevens
          YasserRACDBA wrote:
          Hi All,

          I believe this might be basic question, How to find appropriate block size for building an database to an specific application?

          I had seen always default 8K block size is used every where(Around 300-350 databases i have seen till now)....but why and how do they estimate this block size blindly before creating production database.
          I think you'll find a lot of discussion on 'optimal' block size on the web, even in this very forum, but the general conclusion seem to be that 8k works best for most applications. I don't think it's so much 'estimate blindly' as it is a realization that there is a valid reason why 8k is the default, and that there is seldom significant gain to be had by varying from that. That's not to say there aren't situations that could justify a different block size, but it is generally not considered 'low hanging fruit'.
          >
          Also in the same way how memory settings are finalized before creating database?
          All memory settings are tunable after db creation and would be done to respond to specific constraints, so it is usually of little value trying to get them 'right' before ever creating a db.


          All in all, if you haven't even created a db yet, there are far more important design considerations than worrying about block size and memory settings.
          >
          -Yasser
          • 2. Re: Finding appropriate block size?
            Aman....
            YasserRACDBA wrote:
            Hi All,

            I believe this might be basic question, How to find appropriate block size for building an database to an specific application?

            I had seen always default 8K block size is used every where(Around 300-350 databases i have seen till now)....but why and how do they estimate this block size blindly before creating production database.

            Also in the same way how memory settings are finalized before creating database?

            -Yasser
            Oracle block size doesn't really need to be 2,4,8 and so only. You can very well use 5,7,3k as well for it. But the best practice is that the size should be either multiple of equivalent to the o/s page size which in the current o/s is 4k. So using 8k is a good practice. I am not able to get the idea with the statement of yours,"v
            ". What does it mean blindly? There is nothing blindly. Using a bigger block size or size different from the current default of whatever oracle version would need serious benchmarkings done to make sure that its actually usable. This is the main reason that its not required to play around with the size very often.Unfortunately, there was a thread over here some time which is deleted now. In that, Charles and Greg presented very good test cases showing that using 8k is the most optimal choice in most of the cases. Going larger than may or may not help. For "may help", you need to crosscheck strongly.

            HTH
            Aman....
            • 3. Re: Finding appropriate block size?
              Yasu
              Thanks ....but how did you came to an conclusion that 8K block size works best for most applications??Any test case??
              All in all, if you haven't even created a db yet, there are far more important design considerations than worrying about block size and memory settings.
              My bad luck that till now i have not been involved in any db creation yet, hence having many basic doubts and treid to grasp information from WEB but of no use...at last came to OTN forum to get from experts and experienced people like you.

              Could you please elaborate about design consideration helpful for in-experienced DBA like me.

              -Yasser
              • 4. Re: Finding appropriate block size?
                Yasu
                OK i understand but how do benchmarking goes for considering block size??

                I mentioned BLINDLY word because before implementing in production how dba use to find appropriate block size for that application.

                Right now i dont have any clear picture about benchmarking database, can you please help in this if you have done this in hand before please...
                Charles and Greg presented very good test cases showing that using 8k is the most optimal choice in most of the cases.
                Again my bad luck that i lost these post before having a look. Is there any chance to find these test cases??

                -Yasser

                Edited by: YasserRACDBA on Oct 15, 2009 12:36 PM
                • 5. Re: Finding appropriate block size?
                  AliD
                  It's a common practice to accept the 8k default block size as it is optimal in most cases. Then if based on your requirements you reach the conclusion that for example an index will benefit from 2k block size, you create a tablespace with block size=2 and put the index on that tablespace.

                  To find out when to increase or decrease block size may improve performance reach to documents.
                  • 6. Re: Finding appropriate block size?
                    Aman....
                    YasserRACDBA wrote:
                    OK i understand but how do benchmarking goes for considering block size??

                    I mentioned BLINDLY word because before implementing in production how dba use to find appropriate block size for that application.
                    Well, as I mentioned, it needs benchmarking. And if you are not able to do so, the best is to stick with the default size. More read intensive databases may need some playing around with the block size but this statement is not authorative at all, at least not from me. So I would suggest to be on the safeside, the default would help you.
                    >
                    Right now i dont have any clear picture about benchmarking database, can you please help in this if you have done this in hand before please...
                    Charles and Greg presented very good test cases showing that using 8k is the most optimal choice in most of the cases.
                    Again my bad luck that i lost these post before having a look. Is there any chance to find these test cases??
                    See this summary which JL has done over his blog,
                    http://jonathanlewis.wordpress.com/2008/07/19/block-sizes/

                    HTH
                    Aman....
                    • 7. Re: Finding appropriate block size?
                      108476
                      Hi Yasser,

                      There are a lot of myths about chosing an optimal blocksize.

                      It depends of the specific SQL workload for your database:

                      - Contention reduction - small rows in a large block perform worse under heavy DML than large rows in a small blocksize.

                      - Reduced row chaining - Placing large object rows (BLOB, CLOB) into a tablespace space with a larger blocksize can greatly reduce row chaining and improve I/O.

                      - Faster updates - Heavy insert/update tables can see faster performance when segregated into another blocksize which is mapped to a small data buffer cache. Smaller data buffer caches often see faster throughput performance.

                      - Reduced Pinging - RAC can perform far faster with smaller blocksizes, reducing cache fusion overhead.

                      - Less RAM waste - Moving random access small row tables to a smaller blocksize (with a corresponding small blocksize buffer) will reduce buffer waste and improve the chance that other data blocks will remain in the cache.

                      - Faster scans - Tables and indexes that require full scans can see faster performance when placed in a large blocksize

                      Again, it depending on your database, you may see a reason to deploy multiple blocksizes:

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

                      *****************************************************************************
                      Right now i dont have any clear picture about benchmarking database
                      Benchmark tests are a great way to see what blocksize is best for you, I wrote this book that may help:

                      http://www.rampant-books.com/book_0701_database_benchmarking.htm

                      Hope this helps . . .

                      Donald K. Burleson
                      Oracle Press author
                      Author of "Oracle Tuning: The Definitive Reference"
                      http://www.rampant-books.com/t_oracle_tuning_book.htm
                      "Time flies like an arrow; Fruit flies like a banana".
                      • 8. Re: Finding appropriate block size?
                        Charles Hooper
                        Aman.... wrote:
                        YasserRACDBA wrote:
                        Right now i dont have any clear picture about benchmarking database, can you please help in this if you have done this in hand before please...


                        Charles and Greg presented very good test cases showing that using 8k is the most optimal choice in most of the cases.

                        Again my bad luck that i lost these post before having a look. Is there any chance to find these test cases??
                        See this summary which JL has done over his blog,
                        http://jonathanlewis.wordpress.com/2008/07/19/block-sizes/

                        HTH
                        Aman....
                        Yasser,

                        The link provided by Aman contains a PDF file which includes the benchmark tests I performed on an 8KB and a 16KB databases. You will also find a number of other test cases in that PDF file such as automatic DB_FILE_MULTIBLOCK_READ_COUNT and a test case that Jonathan Lewis put together to demonstrate a bug in a 16KB ASSM tablespace which is not present in an 8KB ASSM tablespace (my second test case in the post also demonstrates this problem).

                        Some of Greg Rahn's posts which relate to the OTN thread may be found here:
                        http://structureddata.org/2008/09/08/understanding-performance/
                        http://structureddata.org/2008/08/14/automatic-db_file_multiblock_read_count/

                        Charles Hooper
                        IT Manager/Oracle DBA
                        K&M Machine-Fabricating, Inc.
                        • 9. Re: Finding appropriate block size?
                          Yasu
                          Thanks a lot Charles...

                          I am amazed about your patience and capability to create test cases,do you have your blog???

                          Will try to implement and study your test case, but did you uploaded scripts for this test case somewhere to download? OR i need to manually copy it and perform?

                          By the way excellent approach and methdology you have done!!! Thanks a lot for sharing it.

                          -Yasser
                          • 10. Re: Finding appropriate block size?
                            Yasu
                            Thanks Aman.

                            -Yasser
                            • 11. Re: Finding appropriate block size?
                              Yasu
                              Thanks Burleson.

                              -Yasser
                              • 12. Re: Finding appropriate block size?
                                Charles Hooper
                                Yasser,

                                Thank you for the compliments.

                                I do not have a blog, at least not one that is publicly assessible. In my opinion, just by scanning www.orana.com , there are much better blogs already publicly available, many of which also offer test cases which allow you to determine if specific problems/benefits are present in your environment and to help with comprehension of the material presented.

                                A couple people in the very long thread offered to host the scripts that I provided, but I do not know if those scripts are still available. Your best bet is to copy the scripts from the PDF file.

                                Charles Hooper
                                IT Manager/Oracle DBA
                                K&M Machine-Fabricating, Inc.
                                • 13. Re: Finding appropriate block size?
                                  Yasu
                                  I will copy code from PDF and perform it, but please help me if in case i get lost in benchmarking.

                                  Couple of un-official questions for you and please do not take it personally or feel bad....

                                  How did you gain so much of knowledge in Oracle and are there any motivation rules in you??Please suggest if any paths to follow..

                                  How do you manage updating knowledge in Oracle faster and parallel with IT Manager role(Saw your designation in your signature)?

                                  None of these above questions are mandatory, you can freely ignore it.

                                  -Yasser
                                  • 14. Re: Finding appropriate block size?
                                    Charles Hooper
                                    Yasser,

                                    I have been very fortunate to buy and read several very high quality Oracle books which not only correctly state the way something works, but also manage to provide a logical, reasoned explanation for why things happen as they do, when it is appropriate, and when it is not. While not the first book I read on the topic of Oracle, the book “Oracle Performance Tuning 101” by Gaja Vaidyanatha marked the start of logical reasoning in performance tuning exercises for me. A couple years later I learned that Gaja was a member of the Oaktable Network. I read the book “Expert Oracle One on One” by Tom Kyte and was impressed with the test cases presented in the book which help readers understand the logic of why Oracle behaves as it does, and I also enjoyed the performance tuning stories in the book. A couple years later I found Tom Kyte’s “Expert Oracle Database Architecture” book at a book store and bought it without a second thought; some repetition from his previous book, fewer performance tuning storing, but a lot of great, logically reasoned information. A couple years later I learned that Tom was a member of the Oaktable Network. I read the book “Optimizing Oracle Performance” by Cary Millsap, a book that once again marked a distinct turning point in the method I used for performance tuning – the logic made all of the book easy to understand. A couple years later I learned that Cary was a member of the Oaktable Network. I read the book “Cost-Based Oracle Fundamentals” by Jonathan Lewis, a book by its title seemed to be too much of a beginner’s book until I read the review by Tom Kyte. Needless to say, the book also marked a turning point in the way I approach problem solving through logical reasoning, asking and answering the question – “What is Oracle thinking”. Jonathan is a member of the Oaktable Network, a pattern is starting to develop here. At this point I started looking for anything written in book or blog form by members of the Oaktable Network. I found Richard Foote’s blog, which some how managed to make Oracle indexes interesting for me - probably through the use of logic and test cases which allowed me to reproduce what I reading about. I found Jonathan Lewis’ blog, which covers so many interesting topics about Oracle, all of which leverage logical approaches to help understanding. I also found the blogs of Kevin Closson, Greg Rahn, Tanel Poder, and a number of other members of the Oaktable Network. The draw to the performance tuning side of Oracle administration was primarily for a search for the elusive condition known as Compulsive Tuning Disorder, which was coined in the book written by Gaja. There were, of course, many other books which contributed to my knowledge – I reviewed at least 8 of the Oracle related books on the amazon.com website.

                                    Motivation… it is interesting to read what people write about Oracle. Sometimes what is written directly contradicts what one knows about Oracle. In such cases, it may be a fun exercise to determine if what was written is correct (and why it is logically correct), or why it is wrong (and why it is logically incorrect). Take, for example, the “Top 5 Timed Events” seen in this book (no, I have not read this book, I bumped into it a couple times when performing Google searches):
                                    http://books.google.com/books?id=bxHDtttb0ZAC&pg=PA17#v=onepage&q=&f=false
                                    The text of the book states that the “Top 5 Timed Events” shown indicates a CPU Constrained Database (side note: if a database is a series of files stored physically on a disk, can it ever be CPU constrained?). From the “Top 5 Timed Events”, we see that there were 4,851 waits on the CPU for a total time of 4,042 seconds, and this represented 55.76% of the wait time. Someone reading the book might be left thinking one of:
                                    * “That obviously means that the CPU is overwhelmed!”
                                    * “Wow 4,851 wait events on the CPU, that sure is a lot!”
                                    * “Wow wait events on the CPU, I didn’t know that was possible?”
                                    * “Hey, something is wrong with this ‘Top 5 Timed Events’ output as Oracle never reports the number of waits on CPU.”
                                    * “Something is really wrong with this ‘Top 5 Timed Events’ output as we do not know the number of CPUs in the server (what if there are 32 CPUs), the time range of the statics, and why the average time for a single block read is more than a second!”
                                    A Google search then might take place to determine if anyone else reports the number of waits for the CPU in an Oracle instance:
                                    http://www.google.com/search?num=100&q=Event+Waits+Time+CPU+time+4%2C851+4%2C042
                                    So, it must be correct… or is it? What does the documentation show?

                                    Another page from the same book:
                                    http://books.google.com/books?id=bxHDtttb0ZAC&pg=PA28#v=onepage&q=&f=false
                                    Shows the command:
                                    alter system set optimizer_index_cost_adj=20 scope = pfile;
                                    Someone reading the book might be left thinking one of:
                                    * That looks like an easy to implement solution.
                                    * I thought that it was only possible to alter parameters in the spfile with an ALTER SYSTEM command, neat.
                                    * That command will never execute, and should return an “ORA-00922: missing or invalid option” error.
                                    * Why would the author suggest a value of 20 for OPTIMIZER_INDEX_COST_ADJ and not 1, 5, 10, 12, 50, or 100? Are there any side effects? Why isn’t the author recommending the use of system (CPU) statistics to correct the cost of full table scans?
                                    A Google search finds this book (I have not read this book either, just bumped into it during a search) by a different author which also shows that it is possible to alter the pfile through an ALTER SYSTEM command:
                                    http://books.google.com/books?id=ufz5-hXw2_UC&pg=PA158#v=onepage&q=&f=false
                                    So, it must be correct… or is it? What does the documentation show?

                                    Regarding the question of updating my knowledge, I read a lot of books on a wide range of subjects including Oracle, programming, Windows and Linux administration, ERP systems, Microsoft Exchange, telephone systems, etc. I also try to follow Oracle blogs and answer questions in this and other forums (there are a lot of very smart people out there contributing to forums, and I feel fortunate to learn from those people). As long as the book or blog offers logical reasoning, it is fairly easy to tie new material into one’s pre-existing knowledge.

                                    Charles Hooper
                                    IT Manager/Oracle DBA
                                    K&M Machine-Fabricating, Inc.
                                    1 2 3 Previous Next