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

Hi,
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.
Thanks, 
Samir

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.
http://www.info.teradata.com/HTMLPubs/DB_TTU_15_00/index.html#page/Database_Management/B035_1093_015K/1093Chap11.037.15.html
 
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:
http://www.info.teradata.com/HTMLPubs/DB_TTU_15_00/index.html#page/Database_Management/B035_1093_015K/1093Chap16.040.27.html
 

Dieter

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 !
Samir

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 
--Samir

You must sign in to leave a comment.