1 2 Previous Next 24 Replies Latest reply: Jun 3, 2008 8:47 AM by 311441 Go to original post RSS
      • 15. Re: Index Organized Tables on 16k Blocks
        311441
        Hi Mohan

        Perhaps the following articles will make you reconsider:

        http://richardfoote.wordpress.com/2008/03/16/store-indexes-in-a-larger-block-tablespace-some-thoughts-big-brother/
        http://richardfoote.wordpress.com/2008/03/18/store-indexes-in-larger-block-size-the-multiblock-read-myth-karma-police/
        http://richardfoote.wordpress.com/2008/03/20/store-indexes-in-a-larger-block-tablespace-the-multiblock-read-myth-part-ii-the-fly/
        http://richardfoote.wordpress.com/2008/03/26/store-indexes-in-a-larger-block-tablespace-height-reduction-12-myth-five-foot-one/
        http://richardfoote.wordpress.com/2008/03/31/larger-block-index-tablespace-and-small-index-scans-performance-improvement-let-down/

        Cheers

        Richard Foote
        http://richardfoote.wordpress.com/
        • 16. Re: Index Organized Tables on 16k Blocks
          311441
          Hi Hemant

          Far better to potentially confuse, although hopefully enlighten novices, that misleading and contradicting advice is just that, than to allow inaccuracies to remain unchallenged.

          It would be a real shame if people feel compelled to remain silent when they see something they believe to be inaccurate.

          Not entirely sure that helps anyone in the end, novices included ...

          Cheers

          Richard Foote
          http://richardfoote.wordpress.com/
          • 17. Re: Index Organized Tables on 16k Blocks
            108476
            tit-for-tat diatribe.......
            Because he knows I have big ones?

            http://www.dba-oracle.com/images/redneck_smoke.jpg

            What's "tat", anyway?
            • 18. Re: Index Organized Tables on 16k Blocks
              108476
              Hi Mohan,
              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
              Excellent reference, clear and well-written.

              I couldn't have said it better myself. . . .
              • 19. Re: Index Organized Tables on 16k Blocks
                108476
                Hi Charles,
                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.
                Well said.

                Oracle has codified an official hypothesis testing method in 11g, with the SPA (SQL Performance Adsvisor). The SPA is an empirical approach that takes the guesswork out of tuning:

                http://www.dba-oracle.com/oracle11g/oracle_11g_sql_performance_advisor.htm

                While artificial test cases are OK for showing how somethings works" in general (a rule-of-thumb), contrived test cases are not as useful for measueing response time and throughput. 30 years ago, I was taught that the scientific method involved:

                - Choosing a representative sample size of real-world transactions
                - Simulation modeling of the probem domain
                - Using a statistically proven methid for covariance (chi square multivariate analysis)


                Dr. Scalzo (a qualified scientist) has an excellent discussion of this in the book "Database Benchmarking":

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

                *************************************************************

                To the original poster's question "Should I consider a 1 separate blocksize for an IOT"? Try it and see, in a representative test database, using your own table and SQL. I recommend:

                Pre 10g - Third party benchmarking tools

                10g - SQL Tuning Sets (STS)

                11g - SPA

                Anyway, I'm not going to take the troll bait, as this issue has been beaten to death, the trolls get increasingly off-topic abnd inappropriate, with no respect for the OP's question.

                The mods have enough trouble policing Siebel spammers . . . . .
                • 20. Re: Index Organized Tables on 16k Blocks
                  26741
                  that was very good of you to provide URLs to refeerences.
                  That is the best response to inaccurate advice.

                  In our field, I believe, we have far more inaccurate assertions and "evidences"
                  completely in the wrong context than, say, in medicine or engineering (the
                  physical engineerings streams like mechanical or civil/strucutral etc).
                  We tend to be "Certified Professionals" without really being Professional
                  in the same was as we expect Sicentists, Doctors and Engineers and
                  (should I add ?) Lawyers and Accountants (not sure about the last two !)
                  Far better to potentially confuse, although hopefully enlighten novices, that misleading and contradicting advice is just that, than to allow inaccuracies to remain unchallenged.
                  If I were to tell a novice that certain advice is misleading, I had better provide
                  my own advice (with evidence !!!). The evidence is most important, being
                  part of the "scientific method" alluded to.
                  However, whosoever reads a Test Case (whether provided by DB, JL, RF or me !)
                  must be aware that every test is in a specific context -- specific version, platform,
                  nature of data (most important !!!!!), instance parameters, query/usage pattern,
                  concurrency (also important !!).
                  • 21. Re: Index Organized Tables on 16k Blocks
                    26741
                    "evidence" could be "Official Documentation" , a Real World Case (not always
                    possible to present in the public domain, for obvious reasons) or a Test Case,
                    with a specific context.

                    Two minutes after mentioning "scientific method", I was reminded of the
                    scientist who studied a grasshopper's ability to jump.
                    • 22. Re: Index Organized Tables on 16k Blocks
                      155651
                      If the table contains several millions of rows and if there are lots of DML happening then index in a larger block size will be better. If the number of rows are less than a million then normal block size will be enough.

                      The following case study explains the cost of index maintenance in 2K blocksize and 16K blocksize. For 10M rows the number of leaf blocks is 253119 blocks after a full update of the table. The same in 16K is 17777 blocks. ie 253119*2-17777*8 is 364M of extra space.

                      SQL> create table test1(a number, constraint test1_pk primary key(a)) organization index tablespace users_2k;

                      Table created.

                      SQL>
                      SQL> declare
                      2 n1 number;
                      3 BEGIN
                      4 for n1 in 1..10000000 loop
                      5 insert into test1 values(n1*3);
                      6 end loop;
                      7 end;
                      8 /

                      PL/SQL procedure successfully completed.

                      SQL>
                      SQL> analyze index test1_pk compute statistics;

                      Index analyzed.

                      SQL>
                      SQL> select leaf_blocks from user_indexes where index_name='TEST1_PK';
                      53869

                      SQL>
                      SQL> update test1 set a=round(a*2.554,0);

                      10000000 rows updated.

                      SQL>
                      SQL> analyze index test1_pk compute statistics;

                      Index analyzed.

                      SQL>
                      SQL> select leaf_blocks from user_indexes where index_name='TEST1_PK';
                      253119

                      SQL>
                      SQL> alter table test1 move tablespace users_16k;

                      Table altered.

                      SQL>
                      SQL> analyze index test1_pk compute statistics;

                      Index analyzed.

                      SQL>
                      SQL> select leaf_blocks*8 from user_indexes where index_name='TEST1_PK';
                      54880

                      SQL>
                      SQL> update test1 set a=round(a*2.554,0);

                      10000000 rows updated.

                      SQL>
                      SQL> analyze index test1_pk compute statistics;

                      Index analyzed.

                      SQL>
                      SQL> select leaf_blocks*8 from user_indexes where index_name='TEST1_PK';
                      142216

                      SQL>

                      Another option for a large index is to partion the index by using "global partion by range" into several small segments.
                      • 23. Re: Index Organized Tables on 16k Blocks
                        Jonathan Lewis
                        >
                        The following case study explains the cost of index
                        maintenance in 2K blocksize and 16K blocksize. For
                        10M rows the number of leaf blocks is 253119 blocks
                        after a full update of the table. The same in 16K is
                        17777 blocks. ie 253119*2-17777*8 is 364M of extra
                        space.

                        SQL> declare
                        2 n1 number;
                        3 BEGIN
                        4 for n1 in 1..10000000 loop
                        5 insert into test1 values(n1*3);
                        6 end loop;
                        7 end;
                        8 /
                        QL> update test1 set a=round(a*2.554,0);

                        QL> alter table test1 move tablespace users_16k;

                        QL> update test1 set a=round(a*2.554,0);
                        Mohan,

                        Hermant's comment about the scientist and the grass-hopper is most appropriate. One of the important things about the scientific method is that you publish details of your experiments along with your conclusions - as you have done. This allows your peer group to review not only your conclusions, but also your experiment.

                        In your test case there are some important flaws that you need to correct before you can argue that your conclusions are valid.

                        You might note in passing that your test case is an extreme one: it would be odd to update every single key value in an IOT, so we have to wonder how relevant the test might be to a more typical use of IOTs, or even simply B-tree indexes - but that's not significant at present.

                        The significant points are:
                        You fill your IOT by a sequential insert when it's in the 2Kb block size - this means the IOT fills at 100% packing (no free space in any block).

                        You then update your primary key - increasing every key value by a factor of 2.554. This will increase the length of a large number of the key values - especially at the lower end of the index - which helps to explain the extreme degeneration of the index.

                        Moreover, since you update every single key in a single statement, Oracle cannot re-use any of the space made free by the entries that have been relocated (as an update is actually a delete/insert pair and the space left from the delete can only be reclaimed after a commit). For key values above 200, entire leaf blocks will become empty as their key values jump to values higher than the high-value for the block.

                        You then rebuild the index into a tablespace with a 16K block size and repeat the update. There are two significant errors in this strategy. First, your rebuild will be leaving the default 10% free space in each leaf blocks and secondly your index entries are already the larger size. The impact of these two differences will affect the timing and pattern of leaf block splits as you do the second update.

                        If you want to do a more meaningful comparison of the effects, you should drop the table and restart the code from the top with a simple change in tablespace.

                        With a litte editing, I converted you script to the following, and ran it twice on a system running 10.2.0.3
                        spool temp_&1
                        set timing off
                        set feedback off
                        
                        drop table test1 purge;
                        
                        create table test1(
                             a number, 
                             constraint test1_pk primary key(a)
                        ) 
                        organization index 
                        tablespace test_&1.k
                        ;
                        
                        
                        declare
                             n1 number;
                        begin
                             for n1 in 1..2000000 loop
                                  insert into test1 values(n1*3);
                             end loop;
                        end;
                        /
                        
                        
                        analyze index test1_pk compute statistics;
                        select 
                             leaf_blocks, round(leaf_blocks * &1 / 1024 , 2) MB 
                        from 
                             user_indexes 
                        where 
                             index_name='TEST1_PK'
                        ;
                        
                        analyze index test1_pk validate structure;
                        select 
                             lf_rows, lf_rows_len, del_lf_rows, del_lf_rows_len 
                        from 
                             index_stats
                        ;
                        
                        update test1 set a=round(a*2.554,0);
                        commit;
                        
                        analyze index test1_pk compute statistics;
                        select 
                             leaf_blocks, round(leaf_blocks * &1 / 1024 , 2) MB 
                        from 
                             user_indexes 
                        where 
                             index_name='TEST1_PK'
                        ;
                        
                        
                        analyze index test1_pk validate structure;
                        select 
                             lf_rows, lf_rows_len, del_lf_rows, del_lf_rows_len 
                        from 
                             index_stats
                        ;
                        
                        spool off
                        The block size goes in as an input parameter, and I've added code to report the leaf block usage in MB. I also added an validate structure to get the deleted-row information from index_stats (there's an interesting difference there - possibly related to delayed block cleanout - that I might take a look at one day).

                        You'll notice that I've only used 2M rows - I think you said something about 1M as a limit earlier on, so I didn't bother to go the whole way to 100M rows.

                        The results I got were as follows:

                        For the 2K block size;
                        LEAF_BLOCKS         MB
                        ----------- ----------
                              10626      20.75
                        
                           LF_ROWS LF_ROWS_LEN DEL_LF_ROWS DEL_LF_ROWS_LEN
                        ---------- ----------- ----------- ---------------
                           2000000    19643099           0               0
                        
                        LEAF_BLOCKS         MB
                        ----------- ----------
                              50010      97.68
                        
                           LF_ROWS LF_ROWS_LEN DEL_LF_ROWS DEL_LF_ROWS_LEN
                        ---------- ----------- ----------- ---------------
                           2035737    20169218       35737          321251
                        For the 16K block size
                        LEAF_BLOCKS         MB
                        ----------- ----------
                               1214      18.97
                        
                           LF_ROWS LF_ROWS_LEN DEL_LF_ROWS DEL_LF_ROWS_LEN
                        ---------- ----------- ----------- ---------------
                           2000000    19643099           0               0
                        
                        LEAF_BLOCKS         MB
                        ----------- ----------
                               7313     114.27
                        
                           LF_ROWS LF_ROWS_LEN DEL_LF_ROWS DEL_LF_ROWS_LEN
                        ---------- ----------- ----------- ---------------
                           2020237    20032574       20237          184607
                        You'll notice that the index storage was 9.4% more space efficient in the larger block size when packed at 100% - that's the difference between "one block overhead for every 2K" instead of "one block overhead for every 16K".

                        On the other hand, with your test case at 2M rows, both indexes grew by a factor of five, with the 16Kb index becoming 17% less space efficient.

                        You might like to try repeating my version of your test at 100M rows - it's quite possible that with a different number of rows this test will produce different results.

                        I was using locally managed tablespaces with a uniform 1M extent size and manual freelist management, by the way.

                        Regards
                        Jonathan Lewis
                        http://jonathanlewis.wordpress.com
                        http://www.jlcomp.demon.co.uk
                        • 24. Re: Index Organized Tables on 16k Blocks
                          311441
                          Hi Mohan

                          See Jonathan's excellent response on why your "test" is fundamentally flawed.

                          However, good on you for at least attempting to show why you believe in a particular conclusion with a repeatable test case. This gives others the opportunity to see where you may have gone wrong and suggest better strategies.

                          The additional point I would make is that having a database with a default blocksize of 2K would be extremely questionable. Therefore making a comparison between an inappropriate block size and a larger block size also has potential "real-world" pitfalls.

                          An attempt to make the comparison between a more typical 8K block size and a subsequent larger blocksize (16K or 32K if supported) would perhaps also be more appropriate to the discussion and the OP question in particular.

                          Cheers

                          Richard Foote
                          http://richardfoote.wordpress.com/
                          1 2 Previous Next