Archives of the TeradataForum

Message Posted: Wed, 01 Oct 2003 @ 12:26:59 GMT

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

Tony,

We have been using Query Logging in production for approximately 6 weeks. In regards to disk space usage, just keep an eye on it daily. You will soon be able to determine and forecast the necessary disk space requirements. It is not near as significant as you might expect.

Query Logging provides an incredible wealth of information. We know more about the user and usage patterns than ever before. It is hard to imagine what life would be like without this data. We have noticed no noticable impact regarding Query performance.

I'd recommend you start small, then grow as needed. Perhaps you can turn on query logging for just a specific user or account type in the beginning. We have query logging on for All users (loading data into DBQLogTbl.) In addition, for a specific account type (our On-Line users), we have query logging turned on with all detail (ie., we are also populating DBQLSqlTbl, DBQLObjTbl, and DBQLStepTbl.) We are still working out the details regarding how we will archive and store (potentially summarizing) old data.

If you plan on storing Step, Object, or SQL information in addition to the regular DBQLogTbl info, be prepard to write some interesting SQL. When we first started, we simply used the ProcID and QueryID to join all of these tables together; however, after a system reboot, the QueryIDs for certain PEs started to be re-used. We now need to use CollectTimeStamp as part of the join; however, the CollectTimeStamps are different for the different tables. So, we write SQL something like this:

This query returns all SQL for a specific user which ran longer than 5 minutes (wall clock time)

Select   dbase.DatabaseNameI as User_ID
,     dbase.CommentString as User_Name
-- assuming you populate CommentString with the User Name
,     Substr(cast(dbqlogtbl.StartTime as Char(22)),1,10) (Date, Format 'YYYY-MM-DD') as SQLDate
,     ((dbqlogtbl.LastRespTime - dbqlogtbl.StartTime) Day(4) to Second) as Duration
,     dbqlsqltbl.SQLTextInfo as SQLStatement
From  DBC.DBQLogTbl
,     DBC.Dbase
,     DBC.DBQLSQLTbl
Where Duration             >= Interval '5' minute
and      dbqlogtbl.UserID           =     dbase.DatabaseID
and      dbqlsqltbl.ProcID       =  dbqlogtbl.ProcID
and      dbqlsqltbl.QueryID         =     dbqlogtbl.QueryID
and      dbqlogtbl.CollectTimeStamp between
      (dbqlsqltbl.CollectTimeStamp  -     Interval '6' Hour)
      and
      (dbqlsqltbl.CollectTimeStamp  +     Interval '6' Hour)
and      User_ID              =  'E50520'
Order By 4 desc,1,3

It is my understanding that anytime the systems reboots, all cache is flushed and you will lose Query Logging data. If the system is rebooted mutilple times in a single day, then you run the risk of not being able to make use of your Query Logging data (for that day) if you need to join multiple tables together (again, this is because the QueryID is randomly chosen each time the system reboots, thus, duplicates can occur for the same ProcID on the same day.)

I hope this was helpful. I'd be happy to try an answer any other questions you might have as they come up.

Thanks,

Clay

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