Folks - Firstly, I am new to TERADATA Database but not to Databases as such. I am an Oracle DBA myself. Have run into a peculiar issue..
A simple update on a 154 records table is executing forever. The UPDATE has just one record to update, but the performance is pathetic
Creation of a simple and small table with one column executes forever. My observation being, any DML fired against that DB is ending up running for infinite time. However, SELECTs are performing at their best
I happened to create the table along with data in a temp table and the UPDATE ran in just 1 sec. So, it's clear that DMLs or for that matter any DML is of an issue here.
Is there any Global Dead Lock? How did I identify that? Did anyone encounter any such issue?
Besides, a query on MonitorSession, gives me two sessions with AMPState=Blocked when PEstate=DISPATCHING. Does this combination mean that the sessions are blocked. Possibly because a commit was not issued?
AMPState clearly state that DML statement was in Blocked state adding overall query execution time. In BTEQ mode explicit commit is not required until you use BT/ET.
Review all session executed at given time holding (Write/Exclusive) lock on the table