This discussion is archived
1 2 3 4 5 7 Previous Next 92 Replies Latest reply: Nov 27, 2009 12:11 AM by Hemant K Chitale Go to original post RSS
  • 30. Re: corrupted indexes
    Jonathan Lewis Oracle ACE Director
    Currently Being Moderated
    Hemant K Chitale wrote:

    Index "size" (or explosion) can also depend on the nature of operations against the index and how the index was first set up. Your *[Index Explosions|http://jonathanlewis.wordpress.com/2009/07/28/index-explosion/]* notes also indicate some cases when indexes may grow large.
    Not so much "large" as "two or three times larger than expected" - the problem is bounded, not constantly increasing
    [This Peoplesoft discussion thread|http://tech.groups.yahoo.com/group/psftdba/message/2533] also has found issues with some indexes -- I've also seen indexes grow extraordinarily large, impacting not only Index Scans but, possibly, even DML against the table.
    I would treat that example with a little caution, and not one to quote as dependable evidence of a real problem, for two reasons:

    <ul>
    first - the originator makes a comment about "indexes not re-using space unless the exact value is re-inserted", thus demonstrating a basic misunderstanding of Oracle's implementation. Based on that, I have to assume that he may have failed to investigate the problem properly, and may not have understood any evidence he observed.

    secondly - when David Kurtz (one of the world's specialists in trouble-shooting Peoplesoft) asked him if he has made sure the index was not a bitmap index, he gave no reply. The faiure to respond (and, indeed, David's feeling that it was a necessary question) make me wonder if the issue was something very basic that the originator had not realised was relevant.
    </ul>
    [On asktom|http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1396006300346456969#1563326300346627527], (towards the end of that thread) too, I presented a similar issue. A rebuild or coalesce returns index size and performance of batch jobs to "normal".
    A couple of points about your Peoplesoft example:
    <ul>
    a) The table has 10 columns
    b) Both indexes are built on the same 9 out of 10, and both start with the same four in the same order
    c) The concurrent processes insert data with "process_id = {my_id}" but start by deleting data for all process_ids.
    </ul>

    This does not look like a mechanism designed by someone who understands Oracle, or indexing, let alone Oracle's indexing (or even concurrency).

    I can think of a couple of reasons (including hypothetical bugs / edge cases) why the Peoplesoft code might result in index space getting lost - particularly space that should be put onto freelists but doesn't get there, or doesn't get re-used. I've already built a test case emulating this behaviour by applying an unsuitable (but not insanely stupid) design error.

    If you care to extract the definitions of the objects using dbms_metadata.get_ddl() and post them here, I'll spend a little time seeing if I can make the problem appear with your object definitions.

    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                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            
  • 31. Re: corrupted indexes
    311441 Employee ACE
    Currently Being Moderated
    burleson wrote:

    You do understand; you just want to argue about proper word usage.
    Strangely, proper word usage is important. You don't say "small red car" for example when you're talking about big pink elephants ...

    >
    FACT - After a large delete, Oracle will re-link an index data block back onto the freelists, where the block might get index entries from other places in the tree.
    Not sure what you're trying to say here. After a large delete, an index block is placed back on the freelist only if all it's index entries have been deleted. In which case, once a new block is required due to a subsequent block split, this empty index block gets logically unlinked from it's current location within the index structure and is recycled and relinked elsewhere. But this of course is a good thing as it means all the deleted entries are removed without the need for an index rebuild or coalesce.

    >
    FACT - When massive deletes occur, Oracle DOES NOT re-balance the index nodes . . .
    Again, not sure what you're trying to say here as it's unclear what you mean by "re-balance" and by "index nodes". After a massive delete, all index leaf blocks are all the exact same distance from the root block (blevel) and the index remains perfectly "balanced" so indeed there is no need to "re-balance" the index after such a delete. Again, this is a good thing as it means the index structure is still efficient and as the deleted index entries may all get subsequently reused and cleaned out automatically by Oracle by subsequent inserts, may again mean there's no need for an index rebuild or coalesce.

    >
    the word unbalanced is not mentioned once.
    You can call it "bad", "exploded", unbalanced, fragmented, call it whatever you like . . .
    I like to call something by the correct terminology, not by whatever I like, as the above four terms have different meanings ...

    >
    As to the SAP issues, there are several OSS notes specifically related to rebuilding of Oracle indexes, especially when they are defined with multiple freelist groups.

    https://www.sdn.sap.com/irj/scn/weblogs?blog=/pub/wlg/8750%3Fpage%3Dlast

    +"For index quality, we should talk about fragmentation, or balancing, which relate to how much extra space is in the index, leading to longer access times, as well as the side effect of pushing good data out of our caches such as the DB_BLOCK_BUFFER (DB_CACHE in recent Oracle versions)."+
    LOL !! If you actually read the linked blog entry and see the comments below, you'll find that other SAP folk disagree with his opinions. You might even notice I get mentioned a number of times when explaining why he is wrong :)

    >
    Also, don't forget that SAPDBA has a utility specifically designed to identify "bad" Oracle indexes for rebuilding.

    http://www.sap-basis-abap.com/bc/rebuilding-indexes-in-sap.htm
    A number of years ago, I attended a SAP on Oracle Admin course in Sydney, run by SAP, which was just full of errors and misinformation about how Oracle works and should be maintained. I was asked by the instructor to talk to the class one afternoon and go quickly through all the incorrect material and explain why it was all wrong, which the class and instructor appreciated very much.

    Note BTW, the author in the link above doesn't sound particularly confident on what he's talking about ("Don't think its quite the same as a proper drop index ..."). It dates to at least Oracle 8.1.7 and recommends stopping the database and running the rebuild in restricted mode ...

    LOL, do you really want SAP to tell you how to administer an Oracle database in this way, really ?
    As to specific indexes, VBAP___0 comes to mind . . .
    So are you saying you rebuild indexes on the VBAP table each and every day ?


    Richard Foote
    http://richardfoote.wordpress.com/
  • 32. Re: corrupted indexes
    Hemant K Chitale Oracle ACE
    Currently Being Moderated
    Jonathan,

    Table and Index DDL :
    SQL> select dbms_metadata.get_ddl('TABLE','PS_COMB_EXP_TAO4','SYSADM') from dual;
    
    DBMS_METADATA.GET_DDL('TABLE','PS_COMB_EXP_TAO4','SYSADM')
    --------------------------------------------------------------------------------
    
      CREATE TABLE "SYSADM"."PS_COMB_EXP_TAO4"
       (    "PROCESS_INSTANCE" NUMBER(10,0) NOT NULL ENABLE,
            "SETID" VARCHAR2(5) NOT NULL ENABLE,
            "TREE_EFFDT" DATE,
            "PROCESS_GROUP" VARCHAR2(10) NOT NULL ENABLE,
            "COMBINATION" VARCHAR2(10) NOT NULL ENABLE,
            "SEQUENCE_NBR_6" NUMBER(*,0) NOT NULL ENABLE,
            "CHARTFIELD" VARCHAR2(18) NOT NULL ENABLE,
            "RANGE_FROM_30" VARCHAR2(30) NOT NULL ENABLE,
            "EFFDT_FROM" DATE,
            "EFFDT_TO" DATE
       ) PCTFREE 10 PCTUSED 80 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
      STORAGE(INITIAL 40960 NEXT 106496 MINEXTENTS 1 MAXEXTENTS 2147483645
      PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
      TABLESPACE "GLAPP"
    
    
    
    SQL>  select dbms_metadata.get_ddl('INDEX','PSACOMB_EXP_TAO4','SYSADM') from dual;
    
    DBMS_METADATA.GET_DDL('INDEX','PSACOMB_EXP_TAO4','SYSADM')
    --------------------------------------------------------------------------------
    
      CREATE INDEX "SYSADM"."PSACOMB_EXP_TAO4" ON "SYSADM"."PS_COMB_EXP_TAO4" 
    ("PROCESS_INSTANCE", "SETID", "PROCESS_GROUP", "CHARTFIELD", "RANGE_FROM_30", 
    "EFFDT_FROM", "EFFDT_TO", "COMBINATION", "SEQUENCE_NBR_6")
      PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
      STORAGE(INITIAL 40960 NEXT 131072 MINEXTENTS 1 MAXEXTENTS 2147483645
      PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
      TABLESPACE "PSINDEX"
    
    
    
    SQL>  select dbms_metadata.get_ddl('INDEX','PSBCOMB_EXP_TAO4','SYSADM') from dual;
    
    DBMS_METADATA.GET_DDL('INDEX','PSBCOMB_EXP_TAO4','SYSADM')
    --------------------------------------------------------------------------------
    
      CREATE INDEX "SYSADM"."PSBCOMB_EXP_TAO4" ON "SYSADM"."PS_COMB_EXP_TAO4" 
    ("PROCESS_INSTANCE", "SETID", "PROCESS_GROUP", "CHARTFIELD", "COMBINATION", 
    "SEQUENCE_NBR_6", "RANGE_FROM_30", "EFFDT_FROM", "EFFDT_TO")
      PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
      STORAGE(INITIAL 40960 NEXT 131072 MINEXTENTS 1 MAXEXTENTS 2147483645
      PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
      TABLESPACE "PSINDEX"
    
    
    
    SQL>
    DELETE statement issued by different (at times concurrent) sessions :
    DELETE FROM PS_COMB_EXP_TAO4 WHERE PROCESS_INSTANCE >= 0;
    (and we know that PROCESS_INSTANCE is never NULL, 0 or negative !)

    The DELETE statement appears as the Top SQL by Elapsed_Time (5.56sec each), CPU_Time (5.43sec each), Buffer_Gets(327,234 gets each) and Physical_Reads (320,762 blocks each execute)


    The INSERT statement is :
    INSERT INTO PS_COMB_EXP_TAO4 (PROCESS_INSTANCE, SETID, TREE_EFFDT, PROCESS_GROUP, COMBINATION, SEQUENCE_NBR_6, CHARTFIELD, RANGE_FROM_30, EFFDT_FROM, EFFDT_TO) SELECT DISTINCT some_constant_number .....
    from multiple tables joined
    Therefore, each INSERT inserts N rows (a few to a few ten's of thousands) with the same PROCESS_INSTANCE. However, every new INSERT statement, being issued by a separate "job" has a new PROCESS_INSTANCE.

    The table is also referenced in many queries.
    Unfortunately, it is the DELETE that is the biggest pain.
    327thousand gets at every DELETE is a very high count.

    Hemant K Chitale

    BTW: This is supposedly from standard Peoplesoft code.

    Edited by: Hemant K Chitale on Sep 14, 2009 5:13 PM
  • 33. Re: corrupted indexes
    Jonathan Lewis Oracle ACE Director
    Currently Being Moderated
    Hemant,

    Your notes on asktom suggest that a process does the following:
    DELETE FROM PS_COMB_EXP_TAO4 WHERE PROCESS_INSTANCE >= 0;
    INSERT INTO PS_COMB_EXP_TAO4 ... SELECT DISTINCT some_constant_number .....
    commit;
    Your comments above don't sound like exactly the same description - they give the impression that there may be processes that delete but don't insert.

    Questions:
    Is a given value for +"some_constant_number"+ reused over time, or is it a value that never gets used for two inserts ?

    If it is a constantly changing value, is it always increasing, or is it effectively random.

    Within a given process_instance, how does the setid (second column in index) behave.

    Which version of Oracle are you on and has the behaviour changed between versions. Upgrading from 9i to 10g, for example, could have caused the insert to change from an ordered insert (from a sort distinct) to a randomised insert (from a hash distinct).


    Thoughts:
    I can understand why the delete should use a lot of resources, but I don't understand why you aren't seeing lots of time lost waiting on TX locks. If my interpretation of your description on AskTom is correct: since each delete can see the committed data of all the other inserts, every session will be trying to delete the same data - which means each has to wait for the one ahead to complete - and when a session starts its delete it has to use a lot of undo to check what data it's supposed to see and whether it's supposed to delete it.


    Regards
    Jonathan Lewis
    http://jonathanlewis.wordpress.com
    http://www.jlcomp.demon.co.uk


    "Science is more than a body of knowledge; it is a way of thinking"
    Carl Sagan
  • 34. Re: corrupted indexes
    Hemant K Chitale Oracle ACE
    Currently Being Moderated
    1. No, I wasn't waiting on TX locks. The DELETEs take much longer than the INSERTs. The time on the DELETEs s high Buffer Gets and CPU.

    2. The sessions run DELETE and INSERT statements.

    3. Each new INSERT batch is for a new PROCESS_INSTANCE. This is an Increasing number -- but Peoplesoft doesn't use a Sequence, only does a current_value+1 on a lookup table. The SETID can be different, but within a very small set (there would be about 4 to 8 different SETIDs in this database) at each run.

    4. Release 10.2.0.2 with ASSM

    Edited by: Hemant K Chitale on Sep 14, 2009 6:29 PM
  • 35. Re: corrupted indexes
    Jonathan Lewis Oracle ACE Director
    Currently Being Moderated
    Hemant K Chitale wrote:
    1. No, I wasn't waiting on TX locks. The DELETEs take much longer than the INSERTs. The time on the DELETEs s high Buffer Gets and CPU.
    The comments about timing don't surprise me given your description to date. The absence of TX lock, though, means you are only running one process at a time - or these processes are waiting somewhere else in the code.

    2. The sessions run DELETE and INSERT statements.
    So no other sessions run a delete, and if a session runs an insert it must first run a delete, and does the commit take place after the insert.

    3. Each new INSERT batch is for a new PROCESS_INSTANCE. This is an Increasing number -- but Peoplesoft doesn't use a Sequence, only does a current_value+1 on a lookup table. The SETID can be different, but within a very small set (there would be about 4 to 8 different SETIDs in this database) at each run.
    Presumably the code to get the next value does a select for update, update to value+1. Does this code run before of after the delete; if it runs before the delete does it commit, or is covered by the final commit. How many processes are likely to be active concurrently ?

    "different setids in each run". So it's a very small number of setids in each run, and a very small number in total.
    4. Release 10.2.0.2 with ASSM
    Can you check whether the select statement is using a hash distinct or a sort distinct.


    Regards
    Jonathan Lewis
    http://jonathanlewis.wordpress.com
    http://www.jlcomp.demon.co.uk


    "Science is more than a body of knowledge; it is a way of thinking"
    Carl Sagan
  • 36. Re: corrupted indexes
    Hemant K Chitale Oracle ACE
    Currently Being Moderated
    I currently have more than 1,500 "INSERT INTO PS_COMB_EXP_TAO4" statements in the Shared_Pool. As each one inserts a different PROCESS_INSTANCE.
    There is only one "DELETE FROM PS_COMB_EXP_TAO4 WHERE PROCESS_INSTANCE >= 0" statement.

    In the busiest databases, (perversely ?), there would only be 1 or 2 SETIDs.

    I am not sure which SELECT you refer to in "Can you check whether the select statement is using a hash distinct or a sort distinct.".

    (Note : gbyhash_aggregation_enabled is FALSE).
  • 37. Re: corrupted indexes
    515958 Pro
    Currently Being Moderated
    This thread is becoming an advanced topic on index. :)

    I hope this will continue, so that we will get a lot of information from you guys.

    With great regards to all,
    S.K.
  • 38. Re: corrupted indexes
    Jonathan Lewis Oracle ACE Director
    Currently Being Moderated
    Hemant K Chitale wrote:
    I am not sure which SELECT you refer to in "Can you check whether the select statement is using a hash distinct or a sort distinct.".
    You gave me the following "insert as select ...." - that's why I wanted to know about the "distinct" behaviour, as it might make a difference to the way the index was loaded.
    INSERT INTO PS_COMB_EXP_TAO4 ... SELECT DISTINCT some_constant_number .....
    But your comment here gives me the answer I need (probably).
    (Note : gbyhash_aggregation_enabled is FALSE).
    If you are still interested in trying to work out what's going on, you're going to have to figure out what happens as a process goes through the entire sequence of steps that ends in the final commit. From what I can tell so far a process will:

    <ul>
    select its "process id" from a table
    update the table with the next value for the "process id".

    -- is there a commit at this point ?
    -- what is the actual select / update code used for the process id ?

    delete all visible rows from the ps_comb_exp_ta04 table (where process_id > 0)
    insert a number of rows into the ps_comb_exp_ta04 using the recently acquired process_id
    commit;

    </ul>

    But there are some details missing from this set of steps, because as described there has to be at least one point where processes will queue on TX enqueues waiting for rows, and you say that this doesn't happen.

    As a background question - is the number of executions of the delete statement the same (to within 1 or 2) as the number of sum(executions of all the copies of the insert statement) ?

    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                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        
  • 39. Re: corrupted indexes
    Jonathan Lewis Oracle ACE Director
    Currently Being Moderated
    Jonathan Lewis wrote:
    Richard Foote wrote:

    I support a SAP database that has approximately 4,500 users.

    Interestingly, we maintain our index structures in a manner such that we don't have to rebuild a single index.
    burleson wrote:I beg to differ. I support a SAP database with a key tables and index with 100 freelists defined, with 400 users doing nothing but adding and deleting rows.

    The performance gets so bad (for index FFS) that we have to rebuild them nightly!

    That's an extreme example, and feel free to call it "rare", but it happens a lot in large OLTP systems with thousands of online users . . .
    That's very convenient - lots of people run SAP so there's no question of giving away secret information by supplying a table name, or index name, or a few numbers about an object in the SAP schema. I'm sure the owners of the systems would give permission for such non-identifying information to be published.

    So could we ask Mr. Burleson to identify the index (table_name, index_name, index columns and order, freelist information, block size, etc.) and give us some numbers that show the index causes a performance problem unless rebuilt every night on his 400 user system.

    Then Mr. Foote can show us the same index definition and statistics from his system and explain what he's done to the same index so that he doesn't have to rebuild it every night on his 4,500 user system.
    We've waited 10 days now for information from Mr. Burleson about some tables and indexes with freelists 100, and why the indexes need to be rebuilt every night. To date all we have is this comment:

    >
    As to specific indexes, VBAP___0 comes to mind . . .
    But we haven't seen any technical information to suggest that it is necessary to rebuild indexes on this table. In fact we haven't even been told anything about the index definitions, let alone performance characteristics. We don't even have a response to Richard Foote's question:

    >
    So are you saying you rebuild indexes on the VBAP table each and every day ?
    I think we can only assume at the moment that Mr. Burleson is currently unable to provide any evidence in support of the need to rebuild indexes.

    I would ask Mr. Foote to explain why he doesn't need to rebuild the indexes on VBAP___0 on his system - but that's a bit like asking someone to prove that BigFoot or Unicorns don't exist. He won't know what it is that he's supposed to show isn't+ happening (or isn't relevant) until Mr. Burleson shows what is+ happening that makes him want to rebuild the indexes.

    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                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                
  • 40. Re: corrupted indexes
    Hemant K Chitale Oracle ACE
    Currently Being Moderated
    It seems that the process id (PROCESS_INSTANCE) is obtained by another process (the Process Scheduler). Therefore, it is already committed in another transaction before it is passed to this program (which is run by the Process Scheduler). This is the "some_constant_value" that is inserted as the same value in every row at each run (but a new value for a run by another / next session).

    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.

    Since there are multiple transactions concurrently active on the table and each session issuing it's own "delete all rows" (because all rows will always be PROCESS_INSTANCE > 0), ... wouldn't the index grow as deleted entries are not reused ? (deleted entries would be reused if the index was a Unique Index). ?

    Hemant K Chitale
  • 41. Re: corrupted indexes
    PavanKumar Guru
    Currently Being Moderated
    Hi Hemanth,
    It seems that the process id (PROCESS_INSTANCE) is obtained by another process (the Process Scheduler). Therefore, it is already committed in another transaction before it is passed to this program (which is run by the Process Scheduler). This is the "some_constant_value" that is inserted as the same value in every row at each run (but a new value for a run by another / next session). 
    As per your above comments you said that process_id is input came from another process - I think there might some unique table which is accessed across the DB, for maintaining the Unique ness and it is commited on partially in your scripts (Other wise in the case of currency the records might fail).
    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.
    As I stated above then, looking to previous commetns or above paragraph, when one process gets the "PROCESS_INSTANCE" from one process - for example 101 and still it is under process and parallely another session comes into picture, then it inturn gets the same "PROCESS_INSTANCE" as 101 - if my understanding is correct.
    If it does not get "PROCESS_INSTANCE" as 101, else some thing greater or different - its based on some hashing alogirthm which written or handled or some business procedure - which returns the uniqure session value based on current user.
    Since there are multiple transactions concurrently active on the table and each session issuing it's own "delete all rows" (because all rows will always be PROCESS_INSTANCE > 0), ... wouldn't the index grow as deleted entries are not reused ? (deleted entries would be reused if the index was a Unique Index). ?
    I don't accept with above clause " Hemanth" - the deleted entreis are re-used - based on the immediate commits happens, by next executing transactions - perphaps the data segments are perfectly fitted in block level with out any row migrations. You should check that the records are splitting the index's in 9-1 ratio or 5-5 ratio.

    Let us wait for Jonathan and Richard Comments on this and let go through thread.. in details..some things are missed up.

    - Pavan Kumar N
  • 42. Re: corrupted indexes
    Hemant K Chitale Oracle ACE
    Currently Being Moderated
    PROCESS_INSTANCES are incremented and committed, no two sessions get the same PROCESS_INSTANCE value.


    Deleted entries that have been committed are reused by the next session that starts after the commit. They are not reused by another session running concurrently with the first session. That is my assumption.
  • 43. Re: corrupted indexes
    Jonathan Lewis Oracle ACE Director
    Currently Being Moderated
    Hemant K Chitale wrote:

    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.
    Scenario 1:_
    Assume a session has inserted data for process_instance = 99 and committed.

    The next session gets process_instance = 100, issues delete that deletes the rows for process_instance = 99, and starts to insert rows (no commit yet).

    The next session gets process_instance = 101, issues a delete that TRIES to delete the rows for process_instance = 99 because read-consistency tells it that those rows still exists. It gets stuck waiting on a TX enqueue until the session using process_instance = 100 commits.

    As the session using process_instance 100 commits, the session using process_instance 101 will become free, restart the delete (because it will see the data has changed), then start the insert. It's delete will delete the data for process_instance 100 - but not be able to re-use that bit of space in the index or table, as it has not yet committed, but it will be able to use the space previously filled by the entires for process_instance 99 - moving the free index block from the left hand side of the index to the right hand side.

    Consequences of Scenario 1:
    Concurrent sessions will serialise on the delete.
    The index cannot (in these circumstances) grow unreasonably large - unless you have found a(nother) bug with ASSM


    Scenario 2:_

    Sessions acquire process IDs 102, 103, 104, 105, 106 and are all queuing up behind session 100 waiting for it to commit.

    Assume that the insert statement from the session using process_instance 100 doesn't find any data to insert.

    When session 100 commits, session 102 is freed to run. I'll have to check this, but since it has to rollback its attempted delete, it's possible that it frees up all the other waiting sessions at the same time - and since there is no data to delete to introduce another TX enqueue chain, all the sessions will insert their data simultaneously - making the index grow large enough to hold all five sets of data.

    Consequences of Scenario 2:
    It may take just a few intermittent busy periods when a number of sessions run the job, and occasionally a couple of them don't have any data to insert, to allow your index to grow arbitrarily large. Once the index has grown it will not shrink unless you coalesce (or rebuild) it.

    Oracle doesn't remove blocks from the left hand side of the index, and since you are always delete with process_instance > 0, you will always have to walk through the entire range of empty blocks from previous deletes before you get to the data that really exists. (If you hinted the delete statement - or gather stats on the index when it had some data in it then Oracle could use a tablescan to delete the data, and this might be quicker - it wouldn't stop the index growing, of course, but it would mitigate the problem caused by the empty space.

    Regards
    Jonathan Lewis


    "Science is more than a body of knowledge; it is a way of thinking".
    Carl Sagan
  • 44. Re: corrupted indexes
    PavanKumar Guru
    Currently Being Moderated
    Hi Jonathan,

    From last two days, I was waiting for your reply, finally what I expected and though of is expected in Results of Scenario 1, that pretty's nice and know the "Consequences" of Scenario. I hope Hemanth is looking for that scenario too.

    Coming to Scenario 2, I am just thinking off, what you pointed of "Consequences". Let us wait what "Hemanth" replies back on that... and still waiting for Richard comments too.!!

    Inturn you have elaborated the scenarios very nice Sir.. !! ;-)

    - Pavan Kumar N
1 2 3 4 5 7 Previous Next

Legend

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