DBAsupport.com Forums - Powered by vBulletin
Page 2 of 3 FirstFirst 123 LastLast
Results 11 to 20 of 23

Thread: Deadly Embrace

  1. #11
    Join Date
    Jan 2001
    Posts
    2,828
    Hi Abhay

    Hmm why since they are all updating different rows isnt it ?As the temptable contains unique rows only after aggregations why should the parallel sessions wait on each other as they are updating different rows in the target table

    regards
    Hrishy

  2. #12
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434
    well.. do u know the complete logic? the business keys/primary keys.. and so on..

    i would suggest you to dump the records in temp table and see how the records map with destination table.. then you will get an idea what is going on.

    rgds
    abhay
    funky...

    "I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."

    "Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"

  3. #13
    Join Date
    Jan 2001
    Posts
    2,828
    Hi

    Well what i have seen is simple and it is like

    There is a temp table containing about 3.5 Million records with no keys and based on these records a target table containing about 7 Million records is updated based on the where clause which is also a PK of the target table.
    Informtica partitions the input table and runs about 5 threads to read the source table and update the target table and thats when we hit the deadlock error

    regards
    Hrishy

  4. #14
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434
    sounds like dulipcation of records on business key..
    funky...

    "I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."

    "Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"

  5. #15
    Join Date
    Jun 2006
    Posts
    259
    Quote Originally Posted by hrishy
    Code:
                           ---------Blocker(s)--------  ---------Waiter(s)---------
    Resource Name          process session holds waits  process session holds waits
    TX-0008000c-0000f779        41    2427     X             38    2465           S
    TX-00020007-0000d6d4        38    2465     X             39    2418           S
    TX-00040007-0000de97        39    2418     X             41    2427           S
    session 2427: DID 0001-0029-00004144	session 2465: DID 0001-0026-0000345B
    session 2465: DID 0001-0026-0000345B	session 2418: DID 0001-0027-0000475D
    session 2418: DID 0001-0027-0000475D	session 2427: DID 0001-0029-00004144
    Rows waited on:
    Session 2465: obj - rowid = 00017940 - AAAX8SAI/AAAF9gAAA
      (dictionary objn - 96576, file - 575, block - 24416, slot - 0)
    Session 2418: obj - rowid = 00017940 - AAAX8SAI/AAAFwAAAA
      (dictionary objn - 96576, file - 575, block - 23552, slot - 0)
    Session 2427: obj - rowid = 00017940 - AAAX8SAQoAAABdYAAA
      (dictionary objn - 96576, file - 1064, block - 5976, slot - 0)
    Have you querried the objn 96576 from the dictionary? You could also dump the blocks to determine the contents of the rows.

    Are PK fields (unique indices) being updated? This equates to a delete and an insert for the index and could be the culprit of the deadlock...

  6. #16
    Join Date
    Jan 2001
    Posts
    2,828
    Hi Ixion

    I really appreciate your time for taking a look at this.

    I will query the object with id 96576 and update it here.

    I will also dump the blocks here but i dont know how to carry analysis at this level

    Yes PK feilds are being updated and that too in parallel any idea how to resolve the deadlocks

    regards
    Hrishy

  7. #17
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434
    dont use parallelism with different sessions.. use oracle's parallel processes instead..
    funky...

    "I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."

    "Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"

  8. #18
    Join Date
    Mar 2002
    Posts
    534
    Is Informatica using the APPEND hint (direct path insert)?

  9. #19
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434
    Quote Originally Posted by ixion
    Have you querried the objn 96576 from the dictionary? You could also dump the blocks to determine the contents of the rows.

    Are PK fields (unique indices) being updated? This equates to a delete and an insert for the index and could be the culprit of the deadlock...
    since the is on a single table and if PK are being updated, may it be delete + insert you will not end in deadlock unless you have the source file with dulicate rows on business key(PK here & i assume this is not your parent table).. and invoked parrallely using multiple sessions by your ETL tool.
    Last edited by abhaysk; 09-24-2007 at 06:53 AM.
    funky...

    "I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."

    "Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"

  10. #20
    Join Date
    Jan 2001
    Posts
    2,828
    Hi

    I have checked the source data and it does not have duplicates on the primary key or on the business key.

    The business key and the primary key in this case are the same.

    regards
    Hrishy

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width