1 2 Previous Next 24 Replies Latest reply: Jun 3, 2008 8:47 AM by 311441 RSS

    Index Organized Tables on 16k Blocks

    529937
      What are the benefits and the downside of using IOTs on 16k blocks? Would you recommend it?
        • 1. Re: Index Organized Tables on 16k Blocks
          428027
          If you will not need any another indexes, and your table have at least 1MB, yes i recommend.

          Regards
          Helio Dias
          http://heliodias.com
          • 2. Re: Index Organized Tables on 16k Blocks
            JustinCave
            Are you asking whether it is appropriate to use IOT's in a database where the database block size is 16k? Or are you asking whether it is appropriate to create a separate 16k block size tablespace and a separate 16k block size buffer cache in a database whose block size is something other than 16k and to put IOT's in that tablespace?

            What version of Oracle are you using?
            How are you managing your SGA?

            Justin
            • 3. Re: Index Organized Tables on 16k Blocks
              529937
              Yes, a separate tablespace that has a 16k block size. We are using Oracle 9i. The SGA isn't dynamic in 9i.
              • 4. Re: Index Organized Tables on 16k Blocks
                JustinCave
                OK. What problem are you trying to solve and why do you believe a 16k block size tablespace might be the solution to that problem? Leaving aside the question of whether multiple block sizes ever improve performance for the moment, I believe it's fair to say that just about everyone agrees that it's not something to be trifled with. You want to at least have some reasonable evidence that it's going to help things.

                Adding and managing multiple buffer caches is generally a pain, made worse when you eventually upgrade and dynamic SGA management doesn't work right. Unless you are spending a lot of DBA effort monitoring things, or you have an exceptionally good handle on your workload, you're very likely going to end up wasting RAM in one or the other pool that could be exceptionally useful to objects with the other block size.

                Justin
                • 5. Re: Index Organized Tables on 16k Blocks
                  108476
                  Hi,

                  There is a debate about creating multiple blocksizes.

                  Some claim that the increased human management components of watching multiple buffers is too cumbersome, and that the benefits are not great engough to warrant the new blocksize.

                  Me, I use scripts to manage the monitoring of multiple data buffers, so that I can have larger blocksizes, but you need to run a careful test to see if your specific database sees any improvements in throughput. In my experience, the results can range from negative (the new blocksize decreases throughput), up to over 20%, it depends on many many factors.

                  Here are my notes on this issue:

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

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

                  Hope this helps. . .

                  Donald K. Burleson
                  Oracle Press author
                  Author of "Oracle Tuning: The Definitive Reference":
                  http://www.dba-oracle.com/bp/s_oracle_tuning_book.htm
                  • 6. Re: Index Organized Tables on 16k Blocks
                    311441
                    Hi,


                    Some claim that the increased human management
                    components of watching multiple buffers is too
                    cumbersome, and that the benefits are not great
                    engough to warrant the new blocksize.
                    Or that there may indeed be no benefits in most environments

                    >
                    Me, I use scripts to manage the monitoring of
                    multiple data buffers, so that I can have larger
                    blocksizes, but you need to run a careful test to see
                    if your specific database sees any improvements in
                    throughput. In my experience, the results can range
                    from negative (the new blocksize decreases
                    throughput), up to over 20%, it depends on many many
                    factors.
                    Hi Don

                    So you finally agree the results of multiple blocksizes can be negative in your experience ...

                    Why then you do still suggest in the soon to be infamous "Hands On Course Requirement" Thread: Hands On Course Requirement

                    "it remains great advice for senior DBAs" that

                    "One of the very first things the Oracle 9i database administrator should do is to migrate all of their Oracle indexes into a large blocksize tablespace. Indexes will always favor the largest supported blocksize"

                    when you now claim to have experience with seeing negative results ????

                    And why did you claim that "It's always worked for my clients" when you now say the results in your experience can be negative.

                    You can't have it both ways Don ...

                    Cheers

                    Richard Foote
                    http://richardfoote.wordpress.com/
                    • 7. Re: Index Organized Tables on 16k Blocks
                      424150
                      Ok, your (RF & DB) last little spat was quite amusing and at times informative but lets not let this thread replicate the other Richard.

                      Why don't you just respond to the original question with your own (often impressive) technical input rather than trying to bait Don into another tit-for-tat diatribe.......
                      • 8. Re: Index Organized Tables on 16k Blocks
                        155651
                        Multiple block sizes helps in fine tuning IO. Also for index management in better with a bigger block size.

                        http://www.myoracleguide.com/s/MultipleBlocksizes.htm
                        • 9. Re: Index Organized Tables on 16k Blocks
                          26741
                          little spat was quite amusing and at times informative
                          True ! I wonder how many forum users (novices) have been confused and put off
                          by the "war of words" that JL and RF have with DB.
                          • 10. Re: Index Organized Tables on 16k Blocks
                            NitinJoshi
                            create or replace trigger WORD_WAR After COMMENT of DB,RF,JL
                            on EVERY_THREAD for Each REPLY
                            output varchar2(4000);
                            Begin

                            for i in ∞ loop
                            output:= '#%*#$%#$*% %#%#$%#$%#$#$%#$%#$%#$';
                            DBMS_OUTPUT.PUT_LINE(output);
                            end loop; --(Can't be)
                            End;

                            and the output looks like this
                            If user in (OP,Novice) then
                            forget about this
                            Else
                            user in ('JL','DB','RF') then
                            Carry On


                            PS. Sure to get some fiery words from above GURUS.
                            • 11. Re: Index Organized Tables on 16k Blocks
                              Charles Hooper
                              little spat was quite amusing and at times
                              informative

                              True ! I wonder how many forum users (novices) have
                              been confused and put off
                              by the "war of words" that JL and RF have with DB.
                              I had resisted the temptation to respond, but Hermant makes a good point - I don't necessarily agree with the point, but it is a good point. I don't want to drag this thread too far off topic, but I think that it is headed in that direction anyway. I was an Oracle novice at one time, as I am sure most in this group will also admit for themselves. I recall several years ago, shortly after reading "Oracle Performance Tuning 101", of trying to improve database performance by reviewing the initialization parameters, redo log configuration, and general server configuration. One of the initialization parameters that I investigated was LOG_BUFFER, which at the time on one of the production databases was set to 262,144 bytes (256KB). A Google search found several pages with information related to this parameter, and one of the first pages found by Google showed wait events from a Statspack report, that according to the website, indicated a problem with the LOG_BUFFER parameter, which was configured at 512KB.

                              The wait events included on the page from the Statspack report included "log file parallel write", "log file sync", "db file parallel write", and "control file parallel write". The notes that I recorded while reading "Oracle Performance Tuning 101" indicated that for Oracle 8i, 512KB is the default value for LOG_BUFFER, and that the value of that parameter should only be increased if there are wait events associated with the redo log buffer ("log buffer space" wait event). This caused a great deal of confusion for me. One book that brought me significant performance improvements indicated that "log buffer space" wait events indicated the potential for an undersized LOG_BUFFER, and one of the top hits in a Google search demonstrated that the existence of "log file parallel write", "log file sync", "db file parallel write", and "control file parallel write" indicated an undersized LOG_BUFFER.

                              So, I investigated the wait events found on the web page, using the "Oracle Performance Tuning 101" book pages 175-176.
                              log file parallel write: "Waits associated with writing of the redo records from the redo log buffer to disk. Usually indicates slow device(s) or contention where the online redo logs are located."
                              log file sync: "Waits associated with the flushing of redo log buffers on a user commit. If the waits are persistent, it may indicate device contention where the online redo logs are placed and/or slow devices.
                              db file parallel write (pg 37): "Indicates waits related to the DBWR process. May be related to the number of DBWR processes or DBWR I/O slaves configured. May also indicate slow or high contention devices."
                              control file parallel write: not found in the book, but later found that it might have to do with disk contention where the control files are located.

                              So, what did I determine was the cause of the wait events? Slow disks, or disks with write performance problems. It is so easy to become confused when supposedly reliable sources suggest entirely different causes for a particular performance problem.

                              Understanding how to push Oracle to function optimally is sometimes confusing. When someone tests a configuration for performance and finds that in one situation the configuration performs quickly, and in another configuration performs very slowly, it is necessary to look at what variables changed, and how those changes influenced the results so that repeatable results may be obtained. I am reminded of a term from grade school, called "the scientific method". As I recall, this method does not necessary need to apply to the pursuit to science, but may also apply to the pursuit of understanding. The process involves restricting the change to one controllable variable, and examining how that variable affects the outcome of an experiment. This implies that if the environment is the same, and only the one controllable variable is changed in exactly the same way, the same results will always be produced due to the change in the one controllable variable.

                              Various definitions for "the scientific method"
                              http://www.google.com/search?hl=en&q=define%3A+scientific+method

                              http://www.merriam-webster.com/dictionary/scientific+method
                              "Date: circa 1810 : principles and procedures for the systematic pursuit of knowledge involving the recognition and formulation of a problem, the collection of data through observation and experiment, and the formulation and testing of hypotheses."

                              http://www.sciencebuddies.org/mentoring/project_scientific_method.shtml
                              "It is important for your experiment to be a fair test. A 'fair test' occurs when you change only one factor (variable) and keep all other conditions the same."

                              http://sciboard.louisville.edu/gensci.html
                              "An experiment that demonstrates a hypothesis must be 'repeatable'. This means that anyone who performs the experiment correctly should get the same results."

                              When testing a hypothesis related to optimal functionality of Oracle, what happens if a test is not repeatable? What happens if the change in one variable is actually the change in many variables?

                              Without the extended discussion and feedback of the topics of hypothesis by Mr. Burleson, Jonathan Lewis, Richard Foote, Tom Kyte, and the other thousands of contributors on this forum, how will a novice user understand what hypotheses really work, and are reproducible in similar environments? Novices need to be able to understand the logic behind a hypothesis, and if no such logic exists, the novice needs to determine whether or not that hypothesis really works. That is part of the magic of forums like this - incorrect answers tend to attract the attention of people with corrected answers.

                              Charles Hooper
                              IT Manager/Oracle DBA
                              K&M Machine-Fabricating, Inc.
                              • 12. Re: Index Organized Tables on 16k Blocks
                                311441
                                Hi Terrible

                                I know where you're coming from I do.

                                However, what you need to remember is that these threads are can be viewed by an Oracle novice next week, or next month or in the years to come. Wouldn't it be unfortunate if someone read all this nonsense regarding multiple blocksizes and only the one, incorrect and misleading point of view was discussed ?

                                Yes, I agree it's tiresome that the same old conflicting advice is repeated again and again. However, unless these conflicting and invalid advices are also questioned again and again, future Oracle novices will only continue to be mislead and confused.

                                The fact these questions and obvious contradictions remain unanswered will hopefully serve as a warning to those who stumble across these threads.

                                Cheers

                                Richard Foote
                                http://richardfoote.wordpress.com/
                                • 13. Re: Index Organized Tables on 16k Blocks
                                  26741
                                  how will a novice user understand what hypotheses really work, and are
                                  reproducible in similar environments
                                  It would help the new DBA if each hypothesis was presented TO him instead of
                                  being thrown at someone else entirely.
                                  • 14. Re: Index Organized Tables on 16k Blocks
                                    424150
                                    Richard

                                    Appreciate the honest response. I was trying to point out that you presented your disagreement and not your argument, that is where the confusion will lie when these threads are read.

                                    I consider myself only a mid level DBA, I like reading the comparitive advice so hopefully that will be displayed more consistently.
                                    1 2 Previous Next