All Forums Database
teradatauser2 236 posts Joined 04/12
03 Apr 2015
How to get PPI/PI/SI usecount

Is it possible to get the below information any any DBC/Pdcrdata tables ?
1. if a PPI is defined for a table then how many times is it used by the queries during a sampling period ?
2. How many times queries use the PI/SI of the table in their where/join conditions ?
We do have a option Usecount to know if any stats is being used/not used in TD14. Is something of this sort is available for Indexes/Coulumns. I need this information to decide wherether a particular PI/SI/PPI is correct for a table or should it be changed ?
Please guide me to any TD manual link , if there is any details about this.

dnoeth 4628 posts Joined 11/04
06 Apr 2015

Hi Samir, 
WITH USECOUNT not only logs info about stats usage, it's also about access on database/table/column/index-level.
This info is stored in dbc.ObjectUsage and several views access it, e.g. dbc.IndicesV will show AccessCount and LastAccessTimestamp, similar dbc.IndexUseCountV.
Tis is just accumulated since the last reset with one of the dbc.Clear???UseCount macros.
For more details on a query level you might enable WITH OBJECTS:


teradatauser2 236 posts Joined 04/12
08 Jun 2015

Thanks Dieter, i will go through it. We have the usecount implemented in our installation now.  Do you have a link to any manual/is there any orange book, that can help me analyse the usage of Pi/PPI in a table using usecount feature. I can then use it to decide if we need to modify them for performance issues.
Thank !

teradatauser2 236 posts Joined 04/12
11 Jun 2015

Hi Dieter,
I am analysing a situation wherein i need to decide changing PI of a table. There is one column (xyz) that i checked in ColumnUseCountV. I see that xyz is used 845 times. But how do we know if this was used in the select or where/join clause ? This is the columns on which PI is being suggested and i need i see if this is really being used in join conditions 

You must sign in to leave a comment.