All Forums Database
mybudy 5 posts Joined 01/11
23 Sep 2013
How know if an index is used?
mybudy 5 posts Joined 01/11
23 Sep 2013

I would like to know how many time one index is used. In dbc.indicesv there is a column AccessCount , this colum is ok for this ?
I don't understand why have one secundary index with columns (nu_telef,cod_central) and the accesscount is : 29 for nu_telef and
3 for cod_central , and columnposition is 1 for cod_central and 2 for nu_telef
the accesscount should not be the same for both ? which is the number of access to the index, 29 or 3 ?

Thanks.

sudheer51 7 posts Joined 03/13
24 Sep 2013

If you need the access count of indexes (hash indexes or join indexes), then you are referring to the correct view.
Access of primary index and secondary index can be different.
I guess that you are referring to the count of the columns accessed. That can be fetched using 'DBC.ColumnsVX' view
 
PS: If the count is not getting logged, make sure you have enabled dbscontrol flag ''ObjectUseCountCollectRate'

If at first you don't succeed; call it version 1.0

sudheer51 7 posts Joined 03/13
24 Sep 2013

Note that the count measured is the count from the 'Optimizer execution plan'.
(Count for explain, insert explain, dump explain not accounted)

If at first you don't succeed; call it version 1.0

mybudy 5 posts Joined 01/11
24 Sep 2013

Yes , I am refering to access count of indexes, but if my secundary index is the tuple (nu_phone,cod_central) and the access count is 29 for the column nu_phone and 3 for the column cod_central then , how many time is accessed the index ? 29 or 3 ?

 

Example (dbc.indicesv) : 

 

.... indexname , columname , columnposition , accesscount .....  -->

 

myIndex1 , nu_phone, 1 , 29    --> row1

myIndex1 , cod_central,2,3      -> row2

myIndex2 , co_region,1,960  --> row3

myIndex2 , co_state,2, null -->     row4

myIndex2, co_store,3,247 -->     row5

 

The question : 

how many access have the index "myIndex1" ?   29 or 3 ?  

how many acces have the index "myIndex2? 960 or 247 or null ???

why the access to column co_state in the index "myIndex2" is null ?

 

I understand if you access to index (all columns)  then the number of accessed to columns of index should be the same , true ?
Thaks.
 
 

VBurmist 96 posts Joined 12/09
24 Sep 2013

Hi,
 
you can also use DBQLobjects for that purpose.   There is ObjectType field to filter the index rows there.    
 
It would be interesting to compare numbers from dbc.qrylogobjects and from dbc.indicesv for your example.
 
With kind regards,
Vlad.

mybudy 5 posts Joined 01/11
25 Sep 2013

I still do not know the answer ...

VBurmist 96 posts Joined 12/09
29 Sep 2013

Hi,
were you able to turn on the DBQLobjects and see the results?   
It would not answer the question why counters for different columns are different.  But would answer the initial question about how many times the index is used.
Also note that DBQL provides more detailed information than AccessCounts.  You would know what users and queries are using that index.
Regards,
Vlad.

omng392 8 posts Joined 06/12
24 Oct 2013

Look at the DBQLobjTBL or DBQLOBJTBL_HST.  There is a TypeOfUse column.  With values like this

  • 1 =   Found in the resolver
  • 2 =   Accessed during query processing
  • 4 =   Found in a conditional context
  • 8 =   Found in inner join condition
  • 16 =   Found in outer join condition
  • 32 =   Found in a sum node
  • 64 =   Found in a full outer join condition 

The values can also be summed up, if a column is encountered in more than one condition.  Such as

  • 3 =  1 + 2
  • 6 =  2  + 4
  • 14 =  2 + 4 + 8
  • 22 = 2 + 4 + 16
  • 34 = 2 + 32
  • 38 = 2 + 4 + 8 + 32
  • 46 = 2 + 4 + 8 + 32
  • 70 = 8 + 64

 

You must sign in to leave a comment.