All Forums Viewpoint
MeenakshiBirai 3 posts Joined 01/13
19 Feb 2014
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:
VandeBergB 182 posts Joined 09/06
19 Feb 2014

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

Some drink from the fountain of knowledge, others just gargle.

MeenakshiBirai 3 posts Joined 01/13
19 Feb 2014

 
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 ?

Adeel Chaudhry 773 posts Joined 04/08
20 Feb 2014

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_111A/ch14.033.17.html
 
Following two tables should answer your queries:

DBQLogTbl

DBQLObjTbl

 

HTH!

 

-- If you are stuck at something .... consider it an opportunity to think anew.

You must sign in to leave a comment.