Analyze Query logs

Viewpoint

Analyze Query logs

Do we have any tool/utility to find Teradata usage profile statistics from the Teradata query logs? We want to pursue this activity because it is a good way to take inventory of the ad-hoc business users for KPI/Reporting rationalization.

We are looking to capture following information.

Sum(# of queries executed) - To identify most active business users

avg(lines of SQL code) - To identify most complex users

avg(number of columns retrieved). Excluding Select (*) queries - To identify users potentially extracting data for offline analysis

avg(query return size) - To identify users potentially extracting data for offline analysis

unique queries vs same query) (Score 1-10) - To identify ad-hoc exploratory users vs. cyclical standard report users

Tags (1)
3 REPLIES
Enthusiast

Re: Analyze Query logs

Meena,

Do you have DBQL logging enabled? These all look like pretty simple queries from the dbql tables.  it's a pretty direct task to develop some views that would represent these metrics.

Thanks,

Blaine

Re: Analyze Query logs

Thanks for your response Blaine.

Yes We have DBQL enabled. What are the DBQL tables and what kind of information will the DBQL tables store ?

Teradata Employee

Re: Analyze Query logs

You can find DBQL information for below link:

http://www.info.teradata.com/htmlpubs/DB_TTU_14_00/index.html#page/Database_Management/B035_1093_111...

Following two tables should answer your queries:

DBQLogTbl

DBQLObjTbl

HTH!