This discussion is archived
1 3 4 5 6 7 Previous Next 92 Replies Latest reply: Nov 27, 2009 12:11 AM by Hemant K Chitale Go to original post RSS
  • 75. Re: corrupted indexes
    ca200197 Newbie
    Currently Being Moderated
    >
    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 Oracle ACE Director
    Currently Being Moderated
    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 Oracle ACE
    Currently Being Moderated
    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 Oracle ACE
    Currently Being Moderated
    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 Oracle ACE Director
    Currently Being Moderated
    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 Oracle ACE
    Currently Being Moderated
    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
    587476 Newbie
    Currently Being Moderated
    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 Oracle ACE
    Currently Being Moderated
    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 Oracle ACE
    Currently Being Moderated
    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 Oracle ACE Director
    Currently Being Moderated
    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
    587476 Newbie
    Currently Being Moderated
    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 Expert
    Currently Being Moderated
    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 Expert
    Currently Being Moderated
    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 Journeyer
    Currently Being Moderated
    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 Oracle ACE Director
    Currently Being Moderated
    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

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points