Archives of the TeradataForum

Message Posted: Mon, 01 Jul 2013 @ 10:24:04 GMT

	<Prev	Next>		<<First	<Prev	Next>	Last>>

Hi Forum,

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

Thanks

Roopalini

	<Prev	Next>		<<First	<Prev	Next>	Last>>