10 Replies Latest reply: Feb 19, 2013 8:02 AM by CycleGeek RSS

    Constant Reorganize Segment Advisor Recommendations

    CycleGeek
      I’m consistently (nightly) getting recommendations to reorganize tables and indexes. Is there some way to size or configure my tablespaces to minimize this?

      Thanks,
      Mark
        • 1. Re: Constant Reorganize Segment Advisor Recommendations
          CKPT
          987503 wrote:
          I’m consistently (nightly) getting recommendations to reorganize tables and indexes. Is there some way to size or configure my tablespaces to minimize this?

          Thanks,
          Mark
          For ongoing transactions of course you can do compression for objects using Advanced compression but it is licensed. You can refer from my blog for its features http://www.oracle-ckpt.com/advanced-table-compression-with-oracle-database-11g-for-oltp/
          • 2. Re: Constant Reorganize Segment Advisor Recommendations
            Jan-Marten Spit
            I think the OP means he is getting a lot of white noise from the segment advisor, and i agree.
            • 3. Re: Constant Reorganize Segment Advisor Recommendations
              CycleGeek
              Is it just white noise that can be ignored? If I implement the recommendations I get more the following day.
              • 4. Re: Constant Reorganize Segment Advisor Recommendations
                Jan-Marten Spit
                "Is it just white noise that can be ignored?"

                well, that's the problem. sometimes it is not, sometimes it is.

                i know the segment advisor ignores any segment < 10M, and maybe that's configurable. i have also seen overestimations of space that can be reclaimed, and i have also seen segment advisor report a gain of 10G on 1 33G index whilst it really was a gain of 32.9G.

                so 'can be ignored' is too strong, but i would certainly not chase every recommendation with row movement, shrink space or online rebuilds.
                • 5. Re: Constant Reorganize Segment Advisor Recommendations
                  Jonathan Lewis
                  Jan-Marten Spit wrote:

                  i have also seen segment advisor report a gain of 10G on 1 33G index whilst it really was a gain of 32.9G.
                  Possibly a compressed index, or a bitmap index. The compression advisor didn't cope particularly well with either type the last time I checked.

                  Regards
                  Jonathan Lewis
                  • 6. Re: Constant Reorganize Segment Advisor Recommendations
                    Jonathan Lewis
                    987503 wrote:
                    I’m consistently (nightly) getting recommendations to reorganize tables and indexes. Is there some way to size or configure my tablespaces to minimize this?
                    It would be worth seeing a few examples of the recommendations in case it gives us a clue.

                    Are there any patterns that you can spot to the way these tables and indexes are used ? For example, do they all relate to tables that suffer a bulk delete and reload at regular intervals, or are the indexes based on "last update date" ? Do the reported objects all reside in the same tablespace and have the default storage parameters for that tablespace been set in some non-standard way.

                    Regards
                    Jonathan Lewis
                    • 7. Re: Constant Reorganize Segment Advisor Recommendations
                      Jan-Marten Spit
                      no - it was a regular btree unique index for a PK on sequence generated integers - i assumed the anomaly (511MB table, 33G index) was due to repeated (right hand inserts,deletes), but maybe you have a better explanation :)
                      • 8. Re: Constant Reorganize Segment Advisor Recommendations
                        Jonathan Lewis
                        Jan-Marten Spit wrote:
                        no - it was a regular btree unique index for a PK on sequence generated integers - i assumed the anomaly (511MB table, 33G index) was due to repeated (right hand inserts,deletes), but maybe you have a better explanation :)
                        The excess size would probably be explained by that pattern - but not the very poor prediction of space saved, unless the num_rows and avg_col_len statistics were very badly out of line with the size of the data.

                        Regards
                        Jonathan Lewis
                        • 9. Re: Constant Reorganize Segment Advisor Recommendations
                          Jan-Marten Spit
                          INDEX = IOF_M_BAT_IND03 size 41.7GiB

                          non-unique normal INDEX ON IOF_M_BETALING_AFSCHRIFTEN.IOF_ONTVANGSTDATUM (date)

                          table IOF_M_BETALING_AFSCHRIFTEN size 0.62GiB
                          SQL> select avg_col_len from dba_tab_columns where table_name='IOF_M_BETALIN
                          G_AFSCHRIFTEN' and column_name='IOF_ONTVANGSTDATUM';
                          
                          AVG_COL_LEN
                          -----------
                                    8
                          
                          SQL> select num_rows from dba_tables where table_name='IOF_M_BETALING_AFSCHRIFTEN';
                          
                            NUM_ROWS
                          ----------
                             5975346
                          
                          SQL> select count(1) from CEBES.IOF_M_BETALING_AFSCHRIFTEN;
                          
                            COUNT(1)
                          ----------
                             6041933
                          
                          SQL> select blevel from dba_indexes where index_name='IOF_M_BAT_IND03';
                          
                              BLEVEL
                          ----------
                                   3
                          
                          Allocated Space:45259888838: Used Space:9398235362: Reclaimable Space :35861653476
                          now the scenario is not quite as i remembered it, but it is a right-hand insert index.

                          i cannot do the rebuild on production (i am not in the BAU department here, actually, i
                          stumbled over this index because this customer wanted an estimateon the space
                          gains it would achieve with advanced compression - which makes this case extra
                          funny :), but i did rebuild the index on test, on which he index was 'only' 33G,
                          but the table is almost the same size. it rebuild from 33GiB to 115MiB.
                          so clearly the estimate of used space is wrong, and i cannot explain it from the small
                          difference between actual and estimated rows, or the avg_col_len, nor the number of NULL
                          values in the column.

                          this is oracle 10.2.0.3.
                          • 10. Re: Constant Reorganize Segment Advisor Recommendations
                            CycleGeek
                            Yes several of the tables/indexes do reside in the same tablespace and yes these tables are periodically (every six hours) truncated and reloaded.

                            Here is a sampel create tablespace statement that I use.
                            create tablespace "sample" datafile
                              '+DATA/dev/datafile/sample_1.dbf' size 5G
                              autoextend on next 1G maxsize 32767M
                              nologging online permanent blocksize 8192
                              extent management local autoallocate segment space management auto;