Archives of the TeradataForum

Message Posted: Tue, 18 Jan 2005 @ 21:22:13 GMT

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

Here is a SQL statement that I run against the QRYLOG tables to determine long running queries...(Feel free to make it better A-Team...)

You can use this to determine what SQL you wish to extract and run Explains on suspect SQL... I do not know of a way to determine if PI's are being used without running an explain on the query.

     LOCKING TABLE DBC.DBASE FOR ACCESS
     SELECT
     Q.COLLECTTIMESTAMP
     ,D.DATABASENAME AS USERNAME
     ,(DT.LAST2 - DT.START2) MINUTE(4) AS RUNTIME
     ,S.SQLTEXTINFO
     FROM
      DBC.QRYLOG Q
     ,DBC.DBASE D
     ,DBC.QRYLOGSQL S ,
      (
     SELECT
     PROCID
     ,QUERYID
      ,CAST(SUBSTR(CAST(LASTRESPTIME AS CHAR(26)),11,9) AS TIME) LAST2
     ,CAST(SUBSTR(CAST(STARTTIME AS CHAR(26)),11,9) AS TIME) START2
     FROM DBC.QRYLOG
     ) DT
     WHERE Q.PROCID = S.PROCID AND Q.QUERYID = S.QUERYID
     AND DT.PROCID = Q.PROCID AND DT.QUERYID = Q.QUERYID
     AND Q.USERID = D.DATABASEID
     AND RUNTIME > '4'
     ORDER BY 3 DESC

Chris Coffing
Air Force Knowledge Services (AFKS)
Lead DBA Operations & Support
Teradata Certified Master

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