1 2 3 4 5 Previous Next 73 Replies Latest reply: Nov 8, 2007 11:47 PM by damorgan Go to original post RSS
      • 60. Re: When to rebuild indexes
        108476
        Hi Howard and Richard,
        HJR notes: “If your database is 24x7 or (more unlikely these days) still using DMT, then the original answer stands: unless you can explicitly determine the rare one-in-a-hundred index that needs rebuilding regularly, then you shouldn't do it.”
        The term “rarity” is in the eyes of the beholder, IMHO. In some super-dynamic applications (i.e. Pharmaceuticals, scientific research applications), it’s not rare at all to get a benefit from index rebuilding. These types of system were one reason that 10g introduced dynamic sampling. A table can be huge in the AM and tiny on the afternoon, and skewed values change with each batch load.

        **********************************************
        “As Bill puts it below, it might not actually be that safe: performance could actually be harmed”
        “"be prepared for much screaming and hand-wringing come Monday morning, when suddenly several of the indexes you rebuilt cause performance to actually tank".”
        Does that imply that a “pristine” index is sub-optimal?

        I think that there is an important point here. While we can inspect historical data and perform forecasts based on prior activity, we cannot anticipate an unknown future.

        **********************************************
        HJR notes: “[slower performance after an index rebuild is] a result of a large index being compacted now having to re-acquire extents it had lost as a result of the rebuild. In dictionary-managed tablespace, that re-growth by lots of indexes first thing on Monday would have produced a lot of waits on the ST enqueue, and performance would have been bad as a result. . . But that's not going to happen in locally-managed tablespace.”
        Richard notes: “an index regrowing is just as likely to be an issue in LMT as it is in DMT”
        Unless we assume that the DBA is not paying attention, they should know if there will be a “re-growth” issue. Does that imply a naïve DBA who does not adjust PCTFREE to allow for their expected future index growth?

        **********************************************
        It's getting everyone off the "LF_ROWS/DEL_LF_ROWS" ratio "bogus science" crapola
        So, does that imply that this a completely unstructured problem, with no rules whatsoever? Nah. I suggest that well-structured rules exist, but they are somewhat complex and require empirical evidence about:

        1 - Specific conditions within the real-world workload (too-long IRS and IFFS)
        2 – Indexes which are “sparsely populated” as a result of DML
        3 – Knowledge of future index expansion

        Just curious, does anyone know the rules that are used in the OEM segment advisor when it detects a candidate for an index rebuild?

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

        HJR Notes: “a totally quiet index during scheduled downtime. Fact is, you are unlikely to ever come across that situation”

        Again, we are talking about industry-specific issues here. I can think of many apps that site idle when the workers go home each weekend. For me, just about all of my Oracle eBusiness Suite, SAP, and Peoplesoft clients don’t use all of their schemas 24x7, but I respect your statement that things may be different in your neck of the woods. Also, let’s not forget data warehouses, which allot a specific downtime window for EDI updates and re-aggregation of materialized views.

        *******************************************
        Richard notes: “The possible performance issues relating to an index "regrowth" are mainly related to the significant overheads in Oracle having to split a block on the fly, redistribute the index row entries, having to modify the branch block, extra LIOs in having to access and modify the leaf pages on either side of the split block and possibly having to split the related branch block as well.”
        This touches a core issue. I know of only one tool to predict the future:

        http://www.timboucher.com/images/ouija_board_ad_1968.jpg

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

        Consider the popular Clintrial software, a LIMS system that uses Oracle. In Clintrial, experimental results are initially stored as small rows. Later, as more data is collected, the VARCHAR columns expand, causing massive row chaining, and fragmenting the daylights out of secondary indexes, as key values are changed. In the Clintrial databases I’ve worked with, most data updates are done via large batch loads from SAS, and as a typical DSS, the queries tend to do lots of large range scans, aggregations and index full-scans.

        To plan for this expected growth, we set PCTFREE to allow only a few rows per block, thereby avoiding the chained row issue. The problem, of course, is having a-priori knowledge of how the application will process it’s rows. In my world, the scientists start complaining about slow response times, and while I’ve alleviated the row chaining issue, the indexes still needed to be rebuilt.
        don't rebuild indexes unless no-one is around to care about it happening.
        I concur. But it’s not the total opposite either. Oracle’s segment advisor has built primitive predictive models to “suggest” reorganization opportunities, but like any DSS, the rules are not fully quantifiable. The DSS only supplies the well-structured quantitative component, but it’s still a semi-structured problem, requiring a real human expert to make the right decision.

        The question remains, can an automated tool inspect the workload and the indexes and make a rebuild recommendation? I say “yes”, but it can never be fully-automated without human input . . . .

        Don Burleson
        "I will say horrible things, despicable things; and people will call me brave.” – Eric Cartman
        • 61. Re: When to rebuild indexes
          damorgan
          You wrote:
          "To plan for this expected growth, we set PCTFREE to allow only a few rows per block, thereby avoiding the chained row issue. The problem, of course, is having a-priori knowledge of how the application will process it’s rows. In my world, the scientists start complaining about slow response times, and while I’ve alleviated the row chaining issue, the indexes still needed to be rebuilt."

          Which indicates that your original action of minimizing rows per block was a waste of time. So why do it?

          In fact why build the index at all until after the load is completed?
          • 62. Re: When to rebuild indexes
            51034
            Which bit of "That's not a trivial issue, and you can certainly end up with foreground waits as it happens" did you not understand?

            There's really no need for you to repeat yourself: I know what you are suggesting, and I don't agree with you. OK?

            I think changes in the execution plans are for more likely to be a worry. I think in DMTs, the ST enqueue is much more likely to be the bad news item of the day.

            And I know you don't agree with that, so feel free not to repeat yourself again on the matter.

            When we are trading "I suggests" and "my judgement call was...", then once you've made your point, you can move on. Because you have no more fact on your assessment of what might or might not be significant than I have: it's a subjective judgement at best.

            I'll close by rejecting my own advice and repeating myself one last time: we have Don agreeing that indexes should be rebuilt under incredibly restrictive circumstances. That is great progress from the 'rebuild 'em, it can't hurt' school of madness that has been prevalent for too long. Anyone that can honestly say that the restrictive conditions apply to them... well, I don't think they're going to care whether it's ST enqueues or block splits that might trip them up, and I don't think the difference is material in any case. Which is why I think you are straining at gnats.
            • 63. Re: When to rebuild indexes
              311441
              Hi Howard

              Do you have any of those links where Tom Kyte mentions re-acquiring extents is problematic after a rebuild ?

              I have this link where Tom mentions block splits being a real issue:

              http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:6601312252730

              Tom also raises concerns about block splits after a rebuild here as well with a nice example:

              http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:35336203098853

              He raises block splits as an issue here:

              http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:2290062993260

              and here

              http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:38264759390157

              A search on index rebuild block splits on his site turns up quite a number of such links ...

              I'd be interested in those links Howard where Tom considers re-aquiring extents to be an issue after an index rebuild ?

              Just one ?

              You don't want to put words in Tom's mouth after all ;)

              Cheers

              Richard
              • 64. Re: When to rebuild indexes
                405087
                @Niall,

                This is exactly what happens. With OWB 10.1, during the batch records are inserted, but indexes are not rebuild.
                When inserting child records, the FK contraint looks up the master record, sing indexes, as a result the master record is not found.

                For some reason this problem is even worse on an Oracle 10.1.0.5 DB.
                To make sure the batch didn't fail several times a month (which it originally did), we ended up liberally rebuilding indexes after every stage.
                I don't see how it would mess up FK
                validation? Or do you perhaps mean that the index is
                not rebuilt (in 10.1) following the table rebuild?
                • 65. Re: When to rebuild indexes
                  108476
                  Hi Robert,
                  the FK contraint looks up the master record, sing indexes, as a result the master record is not found.
                  There is always a risk of RI violations when rebuilding indexes, but we have the "exceptions" clause to help.

                  "exceptions_clause - Specify a table into which Oracle places the rowids of all rows violating the constraint. If you omit schema, then Oracle assumes the exceptions table is in your own schema. If you omit this clause altogether, then Oracle assumes that the table is named EXCEPTIONS. The exceptions table must be on your local database."
                  • 66. Re: When to rebuild indexes
                    605373
                    Hi Aman,

                    thanks for giving the id..i have send u a request on yahoo messenger....plz accept it...

                    Thanks
                    Vivek
                    • 67. Re: When to rebuild indexes
                      405087
                      Hi Don,

                      First of all, I don't want to use the exceptions-clause to fix out-of-sync-index induced FK violations.
                      Mind you, I didn't mean this is as a request for a solution. after all the problem was fixed with OWB 10.1 by using massive index rebuilding, and in version 10.2 by the fact that it now does analyze the indexes when it analyses the tables.

                      What I was trying to say was more that In most of the discussion the application on the DB is treated as generic. the only thing discussed is whether/how often the indexes need to be refreshed.

                      My point in this is that the application does matter; More and more applications are being build using drag-and-drop software tools which build the actual code.
                      A couple of years ago it was common practice to disable constraints and indexes during ETL loading, and then enable and rebuild them once the table was filled.
                      Nowadays the developer doesn't really see the code he creates, and thus this practice disappears.

                      Between this and ever growing data volumes, out-of-sync indexes do pose a greater problem. On the other hand, the performance of the DB has improved enough, to make rebuilding less of a problem.
                      There is always a risk of RI violations when
                      rebuilding indexes, but we have the "exceptions"
                      clause to help.
                      • 68. Re: When to rebuild indexes
                        433180
                        Oracle default indexes are B-tree indexes. They rarely if ever need to be rebuilt. They are always balanced. The only time to ever rebuild is if you delete a bunch of data permanently. If the index is going to fill up again, you are better leaving it at the same size since Oracle does not have the overhead of doing block splits.

                        The other thing I would do is monitor your index usage. Make sure stats are up to date. If you don't have an index being used, then just drop it.

                        If you do rebuild an index, monitor performance. If it stays the same or gets worse, do not rebuild that index again.
                        • 69. Re: When to rebuild indexes
                          damorgan
                          One very small caveat on what you wrote. You said:
                          "If you don't have an index being used, then just drop it."

                          This will be true most of the time but not always.

                          Jonathan Lewis, in his 3 day "Optimizing Oracle" class, talks about cases where Oracle does not use the index but does use the index's statistics to make good decisions.

                          Based on that I now not only check to see if an index is being used, but also verify that the plan won't change if the stats are dropped.
                          • 70. Re: When to rebuild indexes
                            433180
                            Yes, good point : )
                            • 71. Re: When to rebuild indexes
                              Niall Litchfield
                              Hi Robert,
                              the FK contraint looks up the master record, sing
                              indexes, as a result the master record is not found.

                              There is always a risk of RI violations when
                              rebuilding indexes, but we have the "exceptions"
                              clause to help.
                              go on then Don, give us an example. to the best of my knowledge enabled foreign keys don't require an index (though having one is a rather good idea). the demo below is on 11, but unless you want to run it on 6 or below it will work unchanged on other releases (that have less that 100,000 objects accessible to the user..
                              SQL> drop table c;

                              Table dropped.

                              SQL>  create table p(id,name)
                                2 
                              SQL> spoo off 
                              SQL> spoo /home/oracle/orn.lst
                              SQL> create table p(id,name)
                                2  as select object_id,object_name
                                3  from all_objects
                                4  where object_id is not null;

                              Table created.

                              SQL> alter table p add constraint p_pk
                                2  primary key (id);

                              Table altered.

                              SQL> create table c(id,pid,name)
                                2  as select id,id,name from p ;

                              Table created.

                              SQL> alter table c add constraint c_p_fk
                                2  foreign key (pid) references p(id);

                              Table altered.

                              SQL> select index_name from user_indexes
                                2  where table_name='C';

                              no rows selected

                              SQL> insert into c values (100000,1000000,'dummy');
                              insert into c values (100000,1000000,'dummy')
                              *
                              ERROR at line 1:
                              ORA-02291: integrity constraint (OTN.C_P_FK) violated - parent key not found


                              SQL>
                              • 72. Re: When to rebuild indexes
                                Niall Litchfield
                                One very small caveat on what you wrote. You said:
                                "If you don't have an index being used, then just
                                drop it."

                                This will be true most of the time but not always.

                                Jonathan Lewis, in his 3 day "Optimizing Oracle"
                                class, talks about cases where Oracle does not use
                                the index but does use the index's statistics to make
                                good decisions.

                                Based on that I now not only check to see if an index
                                is being used, but also verify that the plan won't
                                change if the stats are dropped.
                                could be a long set of analysis

                                suppose you have a table 30 columns wide with all 15 columns used in the query having single column indexes on them (think ERP). That's a rather large potential set of interactions to manage given that you have to consider all the activity on the table and not just the query..
                                • 73. Re: When to rebuild indexes
                                  damorgan
                                  In which case one might consider not dropping the index. <g>
                                  1 2 3 4 5 Previous Next