1 2 3 4 Previous Next 93 Replies Latest reply: Oct 27, 2009 12:09 PM by Billy~Verreynne Go to original post RSS
      • 15. Re: Finding appropriate block size?
        108476
        Hi Charles,

        What's this got to do with the OP's question?

        Since you cite my book, lets deal with your misconceptions:
        The text of the book states that the “Top 5 Timed Events” shown indicates a CPU Constrained Database
        You are misunderstanding the word "constrained". It does NOT mean that there is a bottleneck.

        Time takes time, and all databases must spend their time doing something.
        if a database is a series of files stored physically on a disk, can it ever be CPU constrained?
        If all databases had no RAM buffers, that would be true.

        However, with RAM costs falling to $1k/gig, most Oracle databases are approaching full caching.

        When you twiddle RAM to get the data, CPU consumption goes through the roof . . .

        With SSD and large buffers reducing disk I/O, I rarely see disk bottlenecks anymore . . .

        I see lots of CPU bottlenecks . . .
        Why isn’t the author recommending the use of system (CPU) statistics to correct the cost of full table scans?
        Because that book was written when 9i was out, and gather_system_stats had serious issues.

        Plus, adjusting OICA was the ONLY effective option in 9i.
        Why would the author suggest a value of 20 for OPTIMIZER_INDEX_COST_ADJ and not 1, 5, 10, 12, 50, or 100?
        BTW, Tom Kyte was asked at a NCOUG meeting (in front of 130 people) what rule of thumb he recommended for setting OICA.

        He said to start with a value between 20 and 35. . .
        So, it must be correct… or is it? What does the documentation show?
        Third party publishers make millions of dollars each year because people prefer books to the documentation

        You say that you have not read this book.  I never put too much credence into reviews by people who have not read the book!

        But none of this matters, since your response has NOTHING to do with Yasser's question, on choosing the best blocksize . . .

        PLEASE do not de-rail Yasser's thread with off-point reviews of books that you haven't read, Charles, it's not fair to Yasser.
        • 16. Re: Finding appropriate block size?
          Hoek
          But none of this matters, since your response has NOTHING to do with Yasser's question, on choosing the best blocksize . . .
          Well, with all respect, you might have missed these followup-questions from Yasser to Charles:
          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)?
          http://forums.oracle.com/forums/message.jspa?messageID=3835517#3835517

          And I want to thank Yasser for asking those questions and I thank Charles for answering in such an openheartedly manner. I had a very good and learnful read.

          (However, I do fully understand your point-of-view as well ;) )
          • 17. Re: Finding appropriate block size?
            Charles Hooper
            burleson wrote:
            Hi Charles,

            What's this got to do with the OP's question?

            Since you cite my book, lets deal with your misconceptions:
            The text of the book states that the “Top 5 Timed Events” shown indicates a CPU Constrained Database
            You are misunderstanding the word "constrained". It does NOT mean that there is a bottleneck.

            Time takes time, and all databases must spend their time doing something.
            if a database is a series of files stored physically on a disk, can it ever be CPU constrained?
            If all databases had no RAM buffers, that would be true.

            However, with RAM costs falling to $1k/gig, most Oracle databases are approaching full caching.

            When you twiddle RAM to get the data, CPU consumption goes through the roof . . .

            With SSD and large buffers reducing disk I/O, I rarely see disk bottlenecks anymore . . .

            I see lots of CPU bottlenecks . . .
            Why isn’t the author recommending the use of system (CPU) statistics to correct the cost of full table scans?
            Because that book was written when 9i was out, and gather_system_stats had serious issues.

            Plus, adjusting OICA was the ONLY effective option in 9i.
            Why would the author suggest a value of 20 for OPTIMIZER_INDEX_COST_ADJ and not 1, 5, 10, 12, 50, or 100?
            BTW, Tom Kyte was asked at a NCOUG meeting (in front of 130 people) what rule of thumb he recommended for setting OICA.

            He said to start with a value between 20 and 35. . .
            So, it must be correct… or is it? What does the documentation show?
            Third party publishers make millions of dollars each year because people prefer books to the documentation

            You say that you have not read this book.  I never put too much credence into reviews by people who have not read the book!

            But none of this matters, since your response has NOTHING to do with Yasser's question, on choosing the best blocksize . . .

            PLEASE do not de-rail Yasser's thread with off-point reviews of books that you haven't read, Charles, it's not fair to Yasser.
            Mr. Burleson,

            With due respect, your bold key appears to be stuck.

            If you very carefully re-read the entire thread, you will find that Yasser, the original poster (OP) of this thread, asked me the following question "How did you gain so much of knowledge in Oracle and are there any motivation rules in you". My reply was an attempt to directly answer his question.

            --

            It is quite possible that I am misunderstanding how you are using the term "Constrained". That reminds me of something else that motivates me - the elimination or reduction of confusion. A Google search requesting the definition of Constrained:
            http://www.google.com/search?sourceid=navclient&ie=UTF-8&rlz=1T4GGLL_enUS307US307&q=define%3aconstrained
            "•constrain - restrain: hold back "
            "•constraint - restraint: a device that retards something's motion; 'the car did not have proper restraints fitted'"
            "•Constraint - In mathematics, a constraint is a condition that a solution to an optimization problem must satisfy."
            "•constraint - A limit to the design process. Constraints may be such things as appearance, funding, space materials, and human capabilities."
            "•Forced or compelled against the will, resulting in undue sustained muscluar contraction. Eg, the horse may be constrained to bend, or flex, or to ..."

            --

            Databases do not have RAM buffers. From the 11.2 Concepts Guide (yes, it appeared in earlier concepts guides also):
            http://download.oracle.com/docs/cd/E11882_01/server.112/e10713/startup.htm
            "A database instance is a set of memory structures that manage database files. A database is a set of physical files on disk created by the CREATE DATABASE statement. The instance manages its associated data and serves the users of the database."
            "Every running Oracle database is associated with at least one Oracle database instance. Because an instance exists in memory and a database exists on disk, an instance can exist without a database and a database can exist without an instance."

            --

            Your statement "Because that book was written when 9i was out" seems to be misleading. Why? Scroll way up to the first page of that book on Google's book site, what do you see? Copyright 2005. Let's see, the Oracle 10.2.0.2 was released in March 2006 if I remember correctly, and 10.2.0.1 was released in... and 10g R1? Also note that the book states that "and Oracle10g are registered trademarks of Oracle Corporation" indicating that the author or editor was at least aware that some release version of Oracle 10g existed at the time of the book writing.

            --

            Your statement "adjusting OICA was the ONLY effective option in 9i." also seems to be a bit misleading. Isn't it possible to gather system (CPU) statistics in 9i R1?
            http://download.oracle.com/docs/cd/A91202_01/901_doc/appdev.901/a89852/dbms_s36.htm#1010477
            Are there not other ways to address the problem?

            --

            Here is a post by Tom Kyte which is probably related to your statement that Tom Kyte suggested a starting value between 20 and 35:
            http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1951680913800
            "Followup November 7, 2002 - 3pm Central time zone:
            I would prefer to use the optimizer_index_cost_adj init.ora (or session) parameter. I like the number '35'. Give that a try."

            A date of the presentation you reference would be helpful. To be clear, I am not stating that you are recalling incorrectly, but I believe that the OP is running a more recent release of Oracle. If we are willing to believe that Richard Foote correctly wrote the following blog entry based on what he heard at the latest Open World:
            http://richardfoote.wordpress.com/2009/10/16/oracle-openworld-day-5-hightlights/
            "I then attended an excellent roundtable discussion forum with the Oracle folks who write and manage the Oracle Cost Based Optimizer. Some excellent questions (including mine on the use of AUTO with method_opt ), although I wasn’t totally convinced with the answer given. However, I had to clap and clap rather loudly when they made special mention of not setting the OPTIMIZER_INDEX_COST_ADJ paramater and recommending rather strongly not to touch this parameter at all."
            It seems like suggesting the adjustment of the parameter requires at least some warning of the potential side effects.

            You are correct that I have not read this book. I did not feel that I was reviewing the book, only commenting on a couple pages that I ran into during Google searches. By the way, thanks for contributing to this thread as it help to illustrate the point I was attempting to make when answering Yasser's question about what motivates me. I do not like confusion.

            Charles Hooper
            IT Manager/Oracle DBA
            K&M Machine-Fabricating, Inc.
            • 18. Re: Finding appropriate block size?
              108476
              Hi Charles,
              I do not like confusion.
              I believe you, Charles, ambiguity is very uncomfortable to people like you.

              But sadly, just about ALL of Oracle is semi-structured and ambiguous.

              That's why there is such a wide disparity in DBA talent.
              Your statement "adjusting OICA was the ONLY effective option in 9i." also seems to be a bit misleading
              Not to any experienced DBA, they know exactly what I'm talking about . . .
              I had to clap and clap rather loudly when they made special mention of not setting the OPTIMIZER_INDEX_COST_ADJ paramater and recommending rather strongly not to touch this parameter at all.
              Sorry, that's just naive, Charles.

              Oracle has been saying making nonsense recommendations like that for years!

              If you have not yet been burned by an "official" Oracle recommendation, here are some history notes:

              - In Oracle7, Oracle told customers that they MUST migrate to the CBO. But the CBO was super-buggy and it was a major fiasco.

              - In Oracle8, Larry briefly re-named Oracle to OUS, and Oracle Coirporation told customers to start moving toward object-relational databases.

              - in Oracle9i, Oracle recommended RAC for just about everything.

              - In Apps 11i, Oracle made a big deal about moving to the CBO (*many years after they told their own customer to!),* and yet a huge number of SQL had RULE hints added!

              - In Oracle10g, they said not to use OICA anymore - But guess what, in some cases, you STILL have to adjust it . . .
              Databases do not have RAM buffers
              Gee. my database does . . .

              This reminds me of Dwight Schrute on "The Office".

              Do you realize how annoying that is, pretending that you don't understand something, just because they did not use exactly the right word?

              I did an article about your personality type:

              http://www.dba-oracle.com/art_dbazine_what_kind_dba.htm
              thanks for contributing to this thread
              Well, how about you, Charles?

              Instead of cluttering-up this thread with non sequiturs, how about you tell us how you choose the optimal blocksize for a given workload?
              • 19. Re: Finding appropriate block size?
                Jonathan Lewis
                burleson wrote:

                PLEASE do not de-rail Yasser's thread with off-point reviews of books that you haven't read, Charles, it's not fair to Yasser.
                Dear Mr. Burleson,

                You raise the interesting question of how much of a book should one read before being in a position to review it. With a book that is sufficiently bad it takes just a few pages - and I would like to offer the following review of your book(s).


                Anyone who [+*follows this link*+ | http://books.google.com/books?id=bxHDtttb0ZAC&pg=PA28#v=onepage&q=&f=false ] from Charles and then scrolls down a page will find the following in your "definitive reference" to tuning (published Aug 2006)

                <ul>
                A 9i database had experienced poor performance immediately after a new manufacturing plant was added to the existing database. Since the AWR was not available in version 9i, a standard statspack report was used to isolate the top five events which looked similar to this:
                Top 5 Wait Events
                ~~~~~~~~~~~~~~~~~                                          Wait  % Total
                Event                                        Waits     Time (cs) Wt Time
                ------------------------------------- ------------ ------------  -------
                enqueue                                     25,901      479,654    46.71
                db file scattered read                  10,579,442      197,205    29.20
                db file sequential read                    724,325      196,583     9.14
                latch free                               1,150,979       51,084     4.97
                log file parallel write                    148,932       39,822     3.88
                A review of the SQL section of the STATSPACK report revealed that almost all of the SQL was using “literals” in the WHERE clause of all queries.
                     WHERE customer_state = ‘Alabama’ and customer_type = ‘LAWYER’;
                The cursor_sharing* parameter was the only fast solution because the application was a vendor package with dynamically generated SQL and it could not easily be changed without using Optimizer Plan Stability (Stored Outlines), a very time-consuming task.

                Setting cursor_sharing=force greatly reduced the contention on the library cache and reduce CPU consumption. The end users reported a 75% improvement in overall performance.

                </ul>

                The interested reader might also want to [+*follow this link*+|http://books.google.co.uk/books?id=Wx6OmllCfIkC&lpg=PP7&dq=%22Implement%20cursor%20sharing%22&pg=PA132#v=onepage&q=%22Implement%20cursor%20sharing%22&f=false] where they could read the same story in your "Silver Bullets" book (published 2005).


                A couple of points to note:

                You say that this is a 9i database - but it's obviously an 8i Statspack report.

                The figures are clearly not self-consistent - notice line three where 196,583 centiseconds is 9.14% of the total time, whereas in line two 197,205 centiseconds is 29.20% of the time.

                You say that you decided to set cursor_sharing to force - but the report says that roughly half the wait time is spent on enqueues, a lot more is spent on I/O, and only a small fraction is spent on latch waits. Does this statspack report have anything to do with your story ?

                If you visit [+*this link*+ | http://jlcomp.demon.co.uk/review_01.html] you will find a more detailed explanation of your errors in my review of the [+*original article*+ | http://www.dbazine.com/burleson20.shtml] where this "silver bullet" was first published. I published my review in March 2005 - three months before the first book came out and 17 months before the second came out.

                The failure to correct the errors in the first book is unfortunate, the failure to do so in the second is disgraceful.

                Regards
                Jonathan Lewis
                http://jonathanlewis.wordpress.com
                http://www.jlcomp.demon.co.uk

                To post code, statspack/AWR report, execution plans or trace files, start and end the section with the tag {noformat}
                {noformat} (lowercase, curly brackets, no spaces) so that the text appears in 
                fixed format
                .
                
                "There are three classes of people. 
                <ul>
                Those who see.
                Those who see when they are shown.
                Those who do not see.
                </ul>
                Leonardo da Vinci                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            
                • 20. Re: Finding appropriate block size?
                  Charles Hooper
                  burleson wrote:
                  Hi Charles,
                  I do not like confusion.
                  I believe you, Charles, ambiguity is very uncomfortable to people like you.

                  But sadly, just about ALL of Oracle is semi-structured and ambiguous.

                  That's why there is such a wide disparity in DBA talent.
                  Your statement "adjusting OICA was the ONLY effective option in 9i." also seems to be a bit misleading
                  Not to any experienced DBA, they know exactly what I'm talking about . . .
                  I had to clap and clap rather loudly when they made special mention of not setting the OPTIMIZER_INDEX_COST_ADJ paramater and recommending rather strongly not to touch this parameter at all.
                  Sorry, that's just naive, Charles.

                  Oracle has been saying making nonsense recommendations like that for years!

                  If you have not yet been burned by an "official" Oracle recommendation, here are some history notes:

                  - In Oracle7, Oracle told customers that they MUST migrate to the CBO. But the CBO was super-buggy and it was a major fiasco.

                  - In Oracle8, Larry briefly re-named Oracle to OUS, and Oracle Coirporation told customers to start moving toward object-relational databases.

                  - in Oracle9i, Oracle recommended RAC for just about everything.

                  - In Apps 11i, Oracle made a big deal about moving to the CBO (*many years after they told their own customer to!),* and yet a huge number of SQL had RULE hints added!

                  - In Oracle10g, they said not to use OICA anymore - But guess what, in some cases, you STILL have to adjust it . . .
                  Databases do not have RAM buffers
                  Gee. my database does . . .

                  This reminds me of Dwight Schrute on "The Office".

                  Do you realize how annoying that is, pretending that you don't understand something, just because they did not use exactly the right word?

                  I did an article about your personality type:

                  http://www.dba-oracle.com/art_dbazine_what_kind_dba.htm
                  thanks for contributing to this thread
                  Well, how about you, Charles?

                  Instead of cluttering-up this thread with non sequiturs, how about you tell us how you choose the optimal blocksize for a given workload?
                  Mr. Burleson,

                  Thanks once again for contributing to this thread.

                  You might recall on this forum that there have been several previous threads discussing block sizes. In one of those threads, on April 6, 2008 I made the following statement to you.
                  Block Size

                  "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."

                  ---

                  I still have yet to purchase any of your books, and I believe that this most recent thread provides insight into why that is still the case.

                  First, a quick review of this thread:
                  1. Yasser asked the question "How to find appropriate block size for building an database to an specific application" and "how memory settings are finalized before creating database"
                  2. Ed Stevens suggested that there are a lot of existing discussions on the web regarding block size, including this forum, and that there are other more pressing design considerations if the database has yet to be created.
                  3. Aman suggested that there was a rather lengthy thread on this forum last year which included test cases by Charles (Hooper) and Greg (Rahn) which might be used to help determine if a larger or smaller block size is more suitable.
                  4. Yasser (the original poster) stated that he came too late to the forum to see the thread and asked to see the test cases from the thread from last year.
                  5. AliD stated that "It's a common practice to accept the 8k default block size as it is optimal in most cases."
                  6. Aman mentioned that benchmarking should be part of the decision process and provided a link to the summary Jonathan Lewis provided for last year's thread prior to the thread being removed due to its size (the test cases that I provided and the results of those test cases probably contributed to the size problem).
                  7. Mr. Burleson provided a couple reasons why a particular block size should be selected, including one that I believe has been demonstrated as incorrect: "Faster scans - Tables and indexes that require full scans can see faster performance when placed in a large blocksize". Mr. Burleson also included 3 links to his website, including one for the book "Oracle Tuning: The Definitive Reference" which Charles Hooper (me) mentioned in a later post.
                  8. Charles Hooper (me) mentioned that the link provided by Aman contains a PDF with the test cases from the long thread about block sizes which was on this forum last year. Charles also mentioned a couple links on Greg Rahn's blog, one of which addresses the benefit provided by Mr. Burleson which is believed to be incorrect.
                  9. Yasser (the original poster in the thread) changed the topic of discussion for the thread, asking Charles Hooper (me): "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)?"
                  10. Charles Hooper (me) attempted to answer the most recent questions asked by Yasser. Charles wrote "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)." Charles then provided 2 references to the book which was included in Mr. Burleson's signature line, demonstrating how people of different skill levels may approach a situation when presented with information which may or may not be correct.
                  11. Mr. Burleson replies "Since you cite my book, lets deal with your misconceptions." implying that Charles misunderstood what was written in Mr. Burleson's book. Mr. Burleson stated "You are misunderstanding the word 'constrained'. It does NOT mean that there is a bottleneck". Mr. Burleson talks about databases having RAM buffers, "RAM costs falling to $1k/gig, most Oracle databases are approaching full caching.", "twiddle RAM to get the data, CPU consumption goes through the roof", "SSD and large buffers reducing disk I/O, I rarely see disk bottlenecks anymore", "I see lots of CPU bottlenecks" "that book was written when 9i was out, and gather_system_stats had serious issues" "adjusting OICA was the ONLY effective option in 9i." "Tom Kyte was asked at a NCOUG meeting (in front of 130 people) what rule of thumb he recommended for setting OICA" "Third party publishers make millions of dollars each year because people prefer books to the documentation" "I never put too much credence into reviews by people who have not read the book!"
                  12. Hoek helps Mr. Burleson see that Charles Hooper (me) was attempting to answer Yasser's (the OP) most recent questions.
                  13. Charles Hooper (me) provides a definition for the word "Constrained" using a Google search to make certain that the word is not misunderstood by people reading the thread. Charles provides a definition of the word "database" using the Oracle 11.2 Concepts Guide as the term seems to have been used loosely by Mr. Burleson. Charles provides evidence that Oracle 10g R1, and possibly 10g R2 were released at the time Mr. Burleson's book was published. Charles questions whether Mr. Burleson's statement about OICA is correct regarding Oracle 9i. Charles found a reference from 2002 where Tom Kyte apparently suggested trying a value of 35 for the OPTIMIZER_INDEX_COST_ADJ parameter, thus supporting (at least partially) Mr. Burleson's claim of what he heard stated by Tom Kyte. Charles provided a link to an article about a roundtable discussion at the most recent Oracle Open World where the people who write and manage the Oracle optimizer stated not to change the OPTIMIZER_INDEX_COST_ADJ parameter. Charles attempted to clarify that he was not attempting to write a review for Mr. Burleson's book by looking at only a couple pages, he was just using it as an illustration of how people of different skill levels may approach a situation when presented with information which may or may not be correct.
                  14. Mr. Burleson stated "Charles, ambiguity is very uncomfortable to people like you" "just about ALL of Oracle is semi-structured and ambiguous" "Not to any experienced DBA, they know exactly what I'm talking about..." "Sorry, that's just naive, Charles" "Oracle has been saying making nonsense recommendations like that for years!" Mr. Burleson stated that someone at Oracle made potentially bad recommendations for Oracle 7, Oracle 8, Oracle 9i, Apps 11i, Oracle 10g. Mr. Burleson then stated that the conversation (presumably about clarity of database related terms) reminded him of the fictional character Dwight Schrute from the TV show "The Office". Mr. Burleson then stated "Do you realize how annoying that is, pretending that you don't understand something, just because they did not use exactly the right word?", apparently not considering how the discussion reached this point, where the same statement may mean entirely different things to different readers, in part due to the skill level of the reader. Mr. Burleson then stated that he created an article about Charles' personality type and provided a link to the unrelated article on his website. Mr. Burleson then stated "Instead of cluttering-up this thread with non sequiturs, how about you tell us how you choose the optimal blocksize for a given workload?"

                  In the interest of those who may be reading this thread, the definition of non sequitur
                  http://www.merriam-webster.com/dictionary/sequiturs
                  "Function: noun
                  Etymology: Latin, it does not follow
                  Date: 1540
                  1 : an inference that does not follow from the premises; specifically : a fallacy resulting from a simple conversion of a universal affirmative proposition or from the transposition of a condition and its consequent
                  2 : a statement (as a response) that does not follow logically from or is not clearly related to anything previously said"

                  Those who read this thread, I believe, will quickly find that the topics of discussion introduced by Mr. Burleson would strongly be considered as matching the above definition of non sequitur. I believe that there is a term from the world of logic (which I encountered both in a college level logic class and a college level ethics class) which describes that which Mr. Burleson has attempted here, where he attempted to falsely attribute a characteristic of his own actions to those around him.

                  The issue of selecting the more appropriate block size has been discussed many times in the past. Yasser (the OP) seemed to be satisfied with the answers provided to him, appeared to be testing the suggestions, and had more or less moved the topic of discussion to something other than block sizes.

                  Charles Hooper
                  IT Manager/Oracle DBA
                  K&M Machine-Fabricating, Inc.
                  • 21. Re: Finding appropriate block size?
                    635471
                    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
                    8kb is almost certainly the appropriate size. It is apparantly subject to more rigorous regression testing by Oracle Corp..

                    Memory settings do not have to be finalised before creating the database. Start with something rough (eg. for an OLTP database a ratio of SGA to PGA of 2:1, for a data warehouse a ratio of 1:2), and monitor the buffer cache advice to get a good idea of the pros and cons of varying this.
                    • 22. Re: Finding appropriate block size?
                      706417
                      +"It is apparantly subject to more rigorous regression testing by Oracle Corp."+

                      Do you base that on the Bryn Llewellyn "revelation", of which all there seems to be on the Web are a few anecdotes, like this: http://jonathanlewis.files.wordpress.com/2009/01/ls_temp.pdf ?

                      Unless I'm missing something, all references lead back to the door of damorgan - a guy who has claimed the title "Re: ORA-26723: user "STRMADM1" requires the role "DBA"", even though when, last week, I contacted the university he works at (University of Washington), their reply (twice, in fact, because I re-checked to be certain) was a categorical "No professor Dan Morgan works for UoW teaching Oracle or anything else. We do have a Dan Morgan who is on file as 'Instructor, Oracle'".

                      Others, too, have drawn attention to damorgan's "professorship".

                      So, where is it written that Oracle tests using only the 8k block size? Relying on damunchausen's testimony, by itself, seems a bit weak, as he's prone, it would appear, to over-egging the pudding.


                      Love - Don Lewis
                      • 23. Re: Finding appropriate block size?
                        601262
                        Don Lewis wrote:
                        So, where is it written that Oracle tests using only the 8k block size?
                        It is not written anywhere per se, but it seems (to me at least) that any value that is the default would be tested more frequently than any non-default value, so it is safe to say that the 8k block is likely tested much more than any other block size.

                        Regards,
                        Greg Rahn
                        http://structureddata.org
                        • 24. Re: Finding appropriate block size?
                          706417
                          So there's no proof, then; just a supposition.

                          I agree that it seems likely, but if there's no proof, then it could be the case that Oracle tests, willy-nilly, on any block size, couldn't it? Seriously, it could be that they test data warehouse stuff on, say, 16k blocksize, OLTP on 2k blocksize, and so on. And, perhaps, even change blocksize experiment-by-experiment.


                          Regards - Don Lewis
                          • 25. Re: Finding appropriate block size?
                            635471
                            Don Lewis wrote:
                            So there's no proof, then; just a supposition.

                            I agree that it seems likely, but if there's no proof, then it could be the case that Oracle tests, willy-nilly, on any block size, couldn't it? Seriously, it could be that they test data warehouse stuff on, say, 16k blocksize, OLTP on 2k blocksize, and so on. And, perhaps, even change blocksize experiment-by-experiment.
                            Well, if there are bugs on certain tablespace sizes and not on others, then that might constitute a form of proof. Though it could be that the test that would demonstrate the bug on 32kb was never run on any size of tablespace, so it's difficult to tell. I've seen bugs on 32kb for which the solution was to migrate to a smaller blocksize though.

                            The one strong case for using a large blocks size IMO is where rows do not fit neatly into 8kb. A table having rows of 3kb would probably be better in a larger block size than 8kb.
                            • 26. Re: Finding appropriate block size?
                              706417
                              Hi David,

                              It's all rather interesting!

                              There really is, it seems, an element of guesswork about blocksizes. Not so much regarding what will best fit into what, but "Hey! Did Oracle bother testing this?".

                              I think I'm right in saying that nobody knows.


                              Regards - Don Lewis
                              • 27. Re: Finding appropriate block size?
                                Billy~Verreynne
                                Don Lewis wrote:
                                So there's no proof, then; just a supposition.
                                That depends on what layer you look at ito the impact of block size on performance.

                                Let's look at the pure I/O fabric layer. Does block size make a difference? I have done some testing on a 24TB storage array (using RDMA protocol) with Oracle recently and no, it does not. Yes, you see less PIOs (obviously). But that does not translate in faster I/O response as the primary constraining factor is the amount of data shipped across the fabric - not the number of blocks.

                                Is this true of all storage systems? Nope - as there are a number of moving parts that are different in the I/O layer. Does this then mean that there is no proof? Not at all. It instead means understanding the environment and how the "+proofs+" shown by others relate to your h/w and environment.

                                Let's take the following statement, arguing for smaller block sizes:
                                "+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.+"

                                Sounds logical.. but typically only on 32bit servers with limited RAM. When using a 16GB db buffer cache for example, there is a hefty penalty to pay for 100's of thousand of memory pages to be managed. It is by far more effective to use larger memory (huge) pages which result in less pages for the kernel to manage. So how does small block sizes benefit this environment when these reside in a single large 64KB memory page?


                                Also, what changes? I have seen numerous h/w upgrades and storage system upgrades for databases. It might have been "+clever"+ to select let's say a 2KB block size as the most optimal one when the db was created. But a h/w upgrade later, that choice could have dire performance consequences.. and in retrospect silly as the original performance gains achieved by that smaller block size were marginal.

                                To put this into an analogy. Aircraft are designed for a flight envelope. Aircraft behaves the best and most predictable when staying the centre of that flight envelope - dealing with known conditions. Where you can run at a specific prop RPM and manifold and lean for a specific EGT. Stray to the edges of that flight envelope and you deal with more unknowns, where the "comfortable" rules that applied to the centre of the envelope no longer holds true.

                                In Oracle terms that centre of the envelope is an 8KB block size at this time. Depending on the performance of aircraft (hardware & software), and the skill of the pilot (DBA), one can push that envelope with different block sizes. But most times that needs very good reason as to why you are pushing the edges of the envelope.
                                • 28. Re: Finding appropriate block size?
                                  706417
                                  Hi Billy,

                                  Many thanks for that. And very interesting, too.

                                  So, can I take it that, on everage, straying from 8k is wrong?

                                  I ask as ,like many shops, we use 32k for our data warehouse. We have SAN and huge SGAs all on AIX 10g.


                                  Regards - Don Lewis
                                  • 29. Re: Finding appropriate block size?
                                    108476
                                    Hi Jonathan,

                                    Please remove my book content from your post.

                                    It's copyrighted, not covered by fair use, and you do not have my permission to copy my work, ever.
                                    The failure to correct the errors in the first book is unfortunate
                                    Yes that must be frustrating, writing reviews that the experts don’t even bother to read, much less act upon.
                                    the failure to do so in the second is disgraceful.
                                    Disgraceful? The same thing happened when you rated Rich Niemiec’s book as “poor”:

                                    Now this is disgraceful:

                                    http://www.amazon.co.uk/Oracle-Database-Performance-Techniques-Osborne/dp/0072263059

                                    Rich Niemiec rejected all of your suggestions too. He didn’t "fix" a single one of the mistakes that you noted, not one . . .

                                    Like I said before, arguing with experts does not make you an expert . . .
                                    1 2 3 4 Previous Next