1 3 4 5 6 7 Previous Next 92 Replies Latest reply: Nov 27, 2009 2:11 AM by Hemant K Chitale Go to original post RSS
      • 75. Re: corrupted indexes
        ca200197
        >
        It can so happen that while one session is still processing with PS_COMB_EXP_TAO4, another session comes along and issues the "DELETE FROM PS_COMB_EXP_TAO4 WHERE PROCESS_INSTANCE > 0". Obviously, there is no Row Lock (TX Enqueue) because the other session cannot see the rows in PS_COMB_EXP_TAO4 that have been inserted by the first (still processing) session. Therefore, the second session issue it's own DELETE statement followed by another INSERT (with a different PROCESS_INSTANCE) value.
        There is something strange in this setup. PS_COMB_EXP_TAO4 is a Peoplesoft temp table so each process should get its own "instance" ( PS_COMB_EXP_TAO4, PS_COMB_EXP_TAO5, PS_COMB_EXP_TAO6, ... ) of the table so no two processes should use the same table at the same time. Or, if all distinct temp table instances are marked "in use" the process uses the base table PS_COMB_EXP_TAO with special processing around the fact that multiple processes may be using it concurrently, i.e. no blanket unqualified delete or even truncate.

        What Peopletools version? Is this a vanilla Peoplesoft job or is it customized?
        • 76. Re: corrupted indexes
          Jonathan Lewis
          Hemant K Chitale wrote:
          Does this happen to both indexes on that table?
          Apparently not. Not both indexes grow to the same size -- although the first 5 columns of the indexes are the same.
          Is it the index that Oracle uses to do the delete that grows the most, or the other one ?

          Is the index tablespace using ASSM ?

          Is the index tablespace using autoallocate or uniform extents - if the latter, what size ?

          What's the block size ?

          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
          .
          
          
          "Science is more than a body of knowledge; it is a way of thinking" 
          Carl Sagan                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    
          • 77. Re: corrupted indexes
            Hemant K Chitale
            Randolf,

            What you are suggesting that we update Index statistics separately from Table statistics. This would let Oracle "know" that the Index is large and should not be used.

            I'll see if I can get such a change put through.
            • 78. Re: corrupted indexes
              Hemant K Chitale
              Is it the index that Oracle uses to do the delete that grows the most, or the other one ?
              One Index is 440MB, the other is 180MB. I call this Index "Leakage". Since PROCESS_INSTANCE is the first column in both indexes, even if Oracle uses an Index, it could use the "smaller" one ? Since the available statistics reflect 500 rows and 5 leaf blocks, Oracle chooses the first one -- being created earlier as it is "earlier" in alphabetical ordering. (Don't ask me to implement changes to index names, but ... I could have the indexes rebuilt in a different order, PSBCOMB before PSACOMB ?)
              Is the index tablespace using ASSM ?
              Unforutnately yes. A recommendation to move to MSSM is still held up on implementation.
              Is the index tablespace using autoallocate or uniform extents - if the latter, what size ?
              128K Uniform. I think that it was the default PSoft script.
              What's the block size ?
              8KB.

              Hemant K Chitale
              • 79. Re: corrupted indexes
                Jonathan Lewis
                Hemant K Chitale wrote:
                Is it the index that Oracle uses to do the delete that grows the most, or the other one ?
                One Index is 440MB, the other is 180MB. I call this Index "Leakage". Since PROCESS_INSTANCE is the first column in both indexes, even if Oracle uses an Index, it could use the "smaller" one ? Since the available statistics reflect 500 rows and 5 leaf blocks, Oracle chooses the first one -- being created earlier as it is "earlier" in alphabetical ordering. (Don't ask me to implement changes to index names, but ... I could have the indexes rebuilt in a different order, PSBCOMB before PSACOMB ?)
                Both showing the same sort of behaviour, then. I did wonder the role of the index might make a significant difference. If you want to change the optimizer's preference, you could always give the unwanted index a high clustering_factor and the other a low clustering_factor.

                Is the index tablespace using ASSM ?
                Unforutnately yes. A recommendation to move to MSSM is still held up on implementation.
                My error - I should have asked about ASSM at the start: it's probably the commonest cause of unexplained space loss.

                Bug 6447841 looks relevant, and I think I can reproduce similar (though much less dramatic) effects using a tablespace with your definition. I'm still looking into it. (The bug may give you a little extra pressure for move testing the effect of a change).

                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
                .
                
                "Science is more than a body of knowledge; it is a way of thinking" 
                Carl Sagan                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                
                • 80. Re: corrupted indexes
                  Hemant K Chitale
                  Bug 6447841 looks relevant.
                  Yes, that looks correct. Thank you very much.

                  I had seen Bugs# 4475314, 4694312, 5349632. All are on Performance. Particularly with DELETE/INSERT

                  But not 6447841.


                  (A move to MSSM has been pending for some months)

                  Hemant K Chitale

                  Edited by: Hemant K Chitale on Sep 29, 2009 3:12 PM
                  • 81. Re: corrupted indexes
                    Alen Oblak
                    Hemant K Chitale wrote:
                    Do I want a 450MB index on a 1.3MB table which has a peak of 16,000 to 17,000 rows? No, I do not.
                    Have you considered droping that index? Of course, I don't know all the queries in which this table is involved, but do they actually need this index or is a FTS fast enough?
                    • 82. Re: corrupted indexes
                      Nicolas.Gasparotto
                      As explain earlier by Hemant, he does not want to touch the objects as they'll be rebuild on the next Peoplesoft patches with their default settings.

                      Nicolas.
                      • 83. Re: corrupted indexes
                        Hemant K Chitale
                        Dropping the indexes would also resolve the issue.
                        (e.g. see the discussion at http://tech.groups.yahoo.com/group/psftdba/message/2663 )


                        But I can't be permitted to do that. I must provide a more "supported" "supportable" solution.
                        • 84. Re: corrupted indexes
                          Randolf Geist
                          burleson wrote:
                          I'm not trying to be argumentative, I truly don't understand your point.

                          I really want to know more about this phenomenon because I've never seen it happen.
                          Don,

                          could you please explain what phenomenon you are referring to?

                          I was referring to a tightly packed index after a rebuild of an almost empty table that needs to perform block splits to accommodate the inserts into the table performed after the rebuild.

                          If you mean this "phenomenon", please explain how you think a tightly packed index handles subsequent inserts without performing block splits?
                          Your argument is that an index in a fragmented state may cause less work for DML than the same index after a rebuild, right?

                          Does that assume that the DBA does not adjust PCTFREE for the rebuild to allow for subsequent expected DML?

                          Randolph, maybe the misunderstanding is about the nature of the "logical deletes", and that dead empty nodes remain in the tree, ready to recieve new rows?

                          In my experience, a "sparse index" (or fragmented, unbalanced, browned, or exploded, whatever you call it) does not perform "less work" for subsequent DML than a brand new tree.

                          I have never seen this happen, but that does not mean that it's not possible . . .
                          Could you please explain, why a "sparse" index does not perform "less work" for subsequent DML?

                          Obviously you could create/rebuild the index with a higher PCTFREE setting, which would prevent some or all of the block splits from happening, but what is then the point of rebuilding the index? Using a higher PCTFREE will result in a larger and *"sparse" index* right from the start, so what is then the benefit of rebuilding it?

                          Please explain what you have never seen happen?

                          Regards,
                          Randolf

                          Oracle related stuff blog:
                          http://oracle-randolf.blogspot.com/

                          SQLTools++ for Oracle (Open source Oracle GUI for Windows):
                          http://www.sqltools-plusplus.org:7676/
                          http://sourceforge.net/projects/sqlt-pp/
                          • 85. Re: corrupted indexes
                            Alen Oblak
                            Hemant K Chitale wrote:
                            Dropping the indexes would also resolve the issue.
                            (e.g. see the discussion at http://tech.groups.yahoo.com/group/psftdba/message/2663 )

                            But I can't be permitted to do that. I must provide a more "supported" "supportable" solution.
                            Funny situation. You buy a piece of software that doesn't work well, you must mantain it and you can't apply a perfect, working technical solution because of some birocratic limitations.
                            • 86. Re: corrupted indexes
                              635471
                              Alen Oblak wrote:
                              Funny situation. You buy a piece of software that doesn't work well, you must mantain it and you can't apply a perfect, working technical solution because of some birocratic limitations.
                              Nothing at all unusual in that.

                              On the one hand the technical people will be told that they can put the change into production when:

                              a) it is proven to work
                              b) to have no undesirable side-effects

                              ... and by the way the performance test environment will be available in two months time and the tests will cost $25,000.

                              On the other hand the business will remember the last time they were told that something was the perfect solution only to have their system crippled by an unexpected side-effect or poorly tested implementation.

                              The former is seen by IT as FUD, the latter is seen as "Oh we didn't think of that. Not our fault".

                              Hence you end up with a technically imperfect half-assed solution like regularly rebuilding indexes, which appeals to managers because it "proves" that they are actively doing something to make things better.
                              • 87. Re: corrupted indexes
                                user503699
                                The former is seen by IT as FUD, the latter is seen as "Oh we didn't think of that. Not our fault".

                                Hence you end up with a technically imperfect half-assed solution like regularly rebuilding indexes, which appeals to managers because it "proves" that they are actively doing something to make things better.
                                David,

                                Spot on. You just spelled out my agony.
                                Thanks.
                                • 88. Re: corrupted indexes
                                  108476
                                  Hi Randolph,
                                  could you please explain what phenomenon you are referring to?
                                  The phenomenon whereby a rebuilt index causes "more work" for subsequent DML than a fresh, pristine index tree.

                                  The whole idea that a pristine newly-rebuilt index is somehow sub-optimal is quite weird

                                  *************************************************************************
                                  Obviously you could create/rebuild the index with a higher PCTFREE setting, which would prevent some or all of the block splits from happening, but what is then the point of rebuilding the index?
                                  If you are rebuilding the index only to reclaim space, then yes.

                                  However, disk is so cheap these days that most folks rebuiild their indexes to speed-up queries (index range scans and index FFS).

                                  However, if the DBA expects "subsequent DML" they can adjust PCTFREE ro accommodate it, that's a best practice!

                                  *************************************************************************
                                  I was referring to a tightly packed index after a rebuild of an almost empty table that needs to perform block splits to accommodate the inserts into the table performed after the rebuild.
                                  Yeah, I get it now, thanks!

                                  In my experience, block splits don't cause a significant DML slowdown.

                                  I have seen spawning cause a brief burp (spawning from three levels to four), but again, not significant.

                                  *************************************************************************
                                  Please explain what you have never seen happen?
                                  I've never seen a case where a rebuilt index performed worse than a "used" index tree.

                                  I would say that it's a moot argument because a DBA who expects subsequent DML would rebuild with PCTFREE to minimize subsequent splitting and spawning.

                                  But again, I'm not saying that significant DML slowdowns never occur as an index splits and spawns, only that I've never seen it . . .

                                  In any case, thanks for your time . . .

                                  *************************************************************************
                                  It sounds like Hemant has been crippled by bitmap freelists (ASSM), a feature that was never intended for high-volume production databases:

                                  Slow for full-table scans — Several studies have shown that large-table full-table scans (FTS) will run longer with ASSM than standard bitmaps. ASSM-FTS tablespaces are consistently slower than Freelist-FTS operations. This implies that ASSM may not be appropriate for decision support systems and warehouse applications, unless partitioning is used with Oracle Parallel Query.

                                  Slower for high-volume concurrent INSERTS — Numerous experts have conducted studies that show that tables with high volume bulk loads perform faster with traditional multiple FREELISTS.

                                  ASSM will influence index clustering — For row-ordered tables, ASSM can adversely affect the clustering_factor for indexes. Bitmap FREELISTS are less likely to place adjacent rows on physically adjacent data blocks, and this can lower the clustering_factor, and the cost-based optimizer's propensity to favor an index range scan.

                                  Thanks again for the clarification . . .

                                  Donald K. Burleson
                                  Oracle Press author
                                  Author of "Oracle Tuning: The Definitive Reference"
                                  http://www.rampant-books.com/t_oracle_tuning_book.htm
                                  "Time flies like an arrow; Fruit flies like a banana".
                                  • 89. Re: corrupted indexes
                                    Randolf Geist
                                    Hemant K Chitale wrote:
                                    What you are suggesting that we update Index statistics separately from Table statistics. This would let Oracle "know" that the Index is large and should not be used.

                                    I'll see if I can get such a change put through.
                                    Yes, exactly.

                                    By the way: As a temporary workaround, have you ever tried to rebuild the indexes using a high PCTFREE setting? Depending on the nature of the bug / odd behaviour this might prevent the index from growing indefinitely (however leave you still with an index that is way too large). It might be worth a try but I'm not sure if it really helps to prevent the growth.

                                    Regards,
                                    Randolf

                                    Oracle related stuff blog:
                                    http://oracle-randolf.blogspot.com/

                                    SQLTools++ for Oracle (Open source Oracle GUI for Windows):
                                    http://www.sqltools-plusplus.org:7676/
                                    http://sourceforge.net/projects/sqlt-pp/
                                    1 3 4 5 6 7 Previous Next