How to get PPI/PI/SI usecount

Database
Enthusiast

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

3 REPLIES
Senior Apprentice

Re: How to get PPI/PI/SI usecount

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_015...

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_015...

Enthusiast

Re: How to get PPI/PI/SI usecount

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

Enthusiast

Re: How to get PPI/PI/SI usecount

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