Archives of the TeradataForum

Message Posted: Fri, 09 Mar 2007 @ 20:14:41 GMT

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

<-- Anonymously Posted: Friday, March 09, 2007 14:11 -->

Dhana,

From the DBMS 6.1 SQL manual:

"When you perform a COLLECT STATISTICS statement, the system places an ACCESS lock on the table from which the demographic data is being collected. The system places row-hash-level WRITE locks on the DBC.TVFIELDS and DBC.INDEXES tables, respectively, while collecting statistics at the column and index levels. These lock prevent parsing of new requests against the data tables for which the statistics are being collected until the operation completes."

So contention will occur, new queries will not be able to parse once the write locks are placed. Inserts that start BEFORE the collect statistics statement obtains the write locks will run.

If your data write process is requesting EXCLUSIVE locks, the Collect Statistics will be blocked and will block processes requesting EXCLUSIVE locks. The catalog write locks are requested late in the process and generally the Collect Statistics process is nearly complete and does not hold the write locks for very long. Processes blocked by the write locks generally will not be blocked for very long.

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