Archives of the TeradataForum
Message Posted: Mon, 01 Jul 2013 @ 10:24:04 GMT
One of our customer ETL jobs is using ODBC load/mload to insert data into their table. When their job runs, I see several blocks in viewpoint and I was curious to find the total time these sessions were being blocked by other sessions using dumplocklog utility.
To my surprise , I found a lot of row hash blocking. Below are few records from the report I ran to determine the total blocking time. As a next step of analysis, I wanted to find out if there are any too many hash synonyms causing this row hash locks. I ran another query which determines the total count of hash synonyms and there were no hash synonyms at all. Can someone please help me understand why there are so many row hash blocking? Could there be any other reason?
DATE TIME DELAY BLOCKER BLKINGSESSNO BLOCKING LEVEL BLOCKING MODE BLOCKED USER BLKDSESSNO BLOCKED LEVEL BLOCKED MODE STATEMENT TYPE DB\TABLE 6/22/2013 9:30:46 434.46 ETLUSER1 2,198,984 RowHash Wr ETLUSER1 2,199,090 RowHash Wr Insert DB1.Table_2 6/22/2013 9:31:54 326.37 ETLUSER1 2,199,090 RowHash Wr ETLUSER1 2,198,984 RowHash Wr Insert DB1.Table_2 6/22/2013 10:03:06 2,224.36 ETLUSER1 2,200,743 RowHash Wr ETLUSER1 2,200,283 RowHash Wr Insert DB1.Table_3
Also, can someone help me understand , how much of resources get wasted due to blocking? I would like to show that to my customer ETL team.
As always responses are much appreciated
|Copyright 2016 - All Rights Reserved|
|Last Modified: 24 Jul 2020|