Object access details - when and how many times a table/index/constraint being used by queries

Database
Enthusiast

Object access details - when and how many times a table/index/constraint being used by queries

Hi 

I need to build a query in Teradata to check what field/indexes/object were accessed/referenced in last one week (for example) for how many times along with timestamp.

Can anyone please help me in doing this? Is it something that needs a modification in DBS Control ?

Thanks.

3 REPLIES
Teradata Employee

Re: Object access details - when and how many times a table/index/constraint being used by queries

Hi,

You can use DBQL objects (dbc.qrylogobjects) for that purpose.   At least tables and columns are there.

Another option is to use "accesscount" and "lastaccesstimestamp" fields in dbc views (dbc.tables, dbc.indices, etc.).   There is a DBS Control to turn these fields on.   But those "accesscount" numbers are always increasing only, not for period of time.  So if you want the numbers for a time period, you should take snapshots of data and compare them to get the "delta"s.

Junior Supporter

Re: Object access details - when and how many times a table/index/constraint being used by queries

You can reset Accesscount and lastaccesstimestamp counters via macros under dbc.

Thanks

Abhishek


Abhishek Jadhav
Teradata Employee

Re: Object access details - when and how many times a table/index/constraint being used by queries

Indeed, the reset of counters is an elegant step here.   But still, the counters' values have to be saved somewhere in a different place.  Otherwise the data for the past data periods is simply lost.     

Thank you,

Vlad.