Archives of the TeradataForum

Message Posted: Tue, 20 Jun 2006 @ 09:31:50 GMT

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

<-- Anonymously Posted: Monday, June 19, 2006 21:22 -->

CollectTimestamp is the time the log buffer was either initialized or written. Ignore that field, except when "Long QueryID" option is not set in dbscontrol. You really want to use Long QueryID, but it takes a database restart to change that. You'll know if it's set because the QueryIDs will all appear as very large numbers instead of small "sequence numbers". With Long QueryID, you can just join on (ProcID, QueryID).

If you don't use Long QueryID, in addition to joining on (ProcID, QueryID) you'll probably have to also qualify the join using some "reasonable" time interval. How large an interval is a tradeoff. You want it large enough that odds are the buffers for all the different log tables would have been written (can be hard to guess since they are independent and asynchronous and it depends on how busy the system is) but not so large that it's likely a restart occurred and QueryIDs have been re-used. For example

     WHERE DBC.QryLogSql.CollectTimestamp
      BETWEEN DBC.QryLogTbl.CollectTimestamp - INTERVAL '4' HOUR
      AND DBC.QryLogTbl.COllectTimestamp + INTERVAL '4'HOUR

And the PI of the DBQL tables is designed for fast writes (minimal impact logging). It tends to be really bad for query performance if the number of log table rows is large.

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