-
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
-
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"
-
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
-
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"
-
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...
-
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
-
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"
-
Is Informatica using the APPEND hint (direct path insert)?
-
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"
-
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|