Need metrics for database, table, and column usage in queries. TD 13.10/14.10

Database

Need metrics for database, table, and column usage in queries. TD 13.10/14.10

We're using Teradata 14.10 and 13.10.

I'm trying to determine which DBC tables and their "historical" counterparts can be queried to tell me how often particular databases, tables, and columns are used in our SQL statements.  The purpose is to help us determine how we can improve our primary indexes.  I think it looks like I should be able to get the information from the various QryLog views, but I'm not certain.  If that's the right place to go, what are their "historical" counterparts?

Thanks,

Robin

3 REPLIES

Re: Need metrics for database, table, and column usage in queries. TD 13.10/14.10

http://www.info.teradata.com/htmlpubs/DB_TTU_13_10/index.html#page/Database_Management/B035_1093_109...

The two view you would probably be interested in are QryLog and QryLogObjects. You will need to join on QueryID and ProcID. I'm not sure if you should join on collect timestamp... I do not know if they are the same for a single query.

A couple notes:

  1. Your logging must be turned on. You will need to consult the link above for info on that.
  2. Data is not moved out of the DBC logs by default. If you collect data, you will need to clean it up by using some kind of process to archive or delete old data.
  3. Object logging takes a lot of space. It logs every object touched by every query. Step and explain logging can take as much or more space.
  4. The "QryLog" views are views that sit on top of (most) dictionary DBQL tables.

Re: Need metrics for database, table, and column usage in queries. TD 13.10/14.10

Do you have PDCR enabled for your system. if yes, there will be "historical counterparts" for those in the DBC tables. Usually there will be a cron which flushes out the data from DBC to PDCR on daily basis. Also there will be aretnetion  period applied for the data in the PDCRDaTA database which depends how it is configured.

Re: Need metrics for database, table, and column usage in queries. TD 13.10/14.10

I will look at this info.  I believe that I saw some information the other day about QryLogObjects.  History is turned on in our 13.10 environments, but it is still turned off in our recently updated environments...I have no control over that.  I'll see what I can get from the two Qry tables above.

Thanks for the info,

Robin