Archives of the TeradataForum

Message Posted: Tue, 15 Nov 2005 @ 23:59:08 GMT

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

We do have the long queryid support on. It definitely appears that the issue is system resource related. We run some large supply chain processes on the 1st & 2nd Monday of the month and resusage shows maxing out on cpu for 4-6 hours. Our etl jobs are a combination of multiload & BTEQ's with no macros or sprocs, the heavy cpu is in the BTEQ's where we are applying business rules. We're channel attached if that matters. It looks like we didn't get the sql on 3800 of 22K queries, the miss rate was about 20% during the high cpu hours and 0-4% otherwise. I have to go back to the mainframe log files and correlate the start times with the dbql to find specific queries.

Here's the query I'm using to pull the dbql data. We're archiving the live dbql tables to a systemstats database nightly. The only join in the archive query is DBQLogTbl.UserId or DBQLSqlTbl.UserId = DBASE.DatabaseID to include the username in the archive tables.

     SELECT
        dl.username,
        dl.starttime,
        ((dl.firstresptime - dl.starttime)  hour(4) to second) QryRunTime,
        cast(((dl.firstresptime - dl.starttime)  minute(4)) as smallint) QryRunTimeMinutes,
        dl.TotalIOCount,
        dl.TotalCpuTime,
        (cast(dl.TotalCpuTime as decimal(12,1))* 1000.0)
               /nullifzero(cast(dl.TotalIOCount as decimal(12,1))) as HigaRatio,
        ds.SqlTextInfo

     FROM
        systemstats.DBQLogTblhistory dl
           LEFT JOIN systemstats.DBQLSqlTblhistory ds
              ON  dl.queryid = ds.queryid
              and  dl.ProcID =  ds.ProcID
              and  dl.username = ds.username
              and  dl.CollectTimeStamp between
                      (ds.CollectTimeStamp  -  Interval '24' Hour)
                      and  (ds.CollectTimeStamp   +  Interval '24' Hour)

     ORDER BY  dl.TotalCpuTime desc

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