You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-dev@db.apache.org by mvarvil <mv...@us.ibm.com> on 2013/03/25 17:45:34 UTC

Logging indexes used by queries

What classes and methods could you point me to such that I could log the
index ultimately used by the optimizer?  

My objective is to identify useless and extraneous indexes in the database.  
I would like to capture a list of indexes used by the database while it is
in use by an application over a period of time.   Comparing the list against
ALL the indexes created in the system should provide a starting point for
identifying extraneous indexes. 

Alternatively, perhaps one might recommend enabling a logger class that
outputs this information already.   

Enabling logQueryPlan is my least favorite idea, because of its verbosity.  
I'm open to other ideas too. 

Thanks. 
Mark



--
View this message in context: http://apache-database.10148.n7.nabble.com/Logging-indexes-used-by-queries-tp128355.html
Sent from the Apache Derby Developers mailing list archive at Nabble.com.

Re: Logging indexes used by queries

Posted by Dag Wanvik <da...@oracle.com>.
Hi,

On 26.03.2013 02:45, mvarvil wrote:
> What classes and methods could you point me to such that I could log the
> index ultimately used by the optimizer?  
I don't know that there is any easy way to do this except siphoning off
information from the logged query plans.
In general, gathering usage information over time to tune the database
is an interesting topic, which we haven't addressed much.
One could imagine updating a data dictionary counter each time an index
was used (at compile and/or query time). That would mean altering the
data dictionary formats with (a) new column(s), not very hard; maybe
total usage count, last time used in a query and possibly other
statistics. I'd start looking by how the current query plan is produced.
Other ideas?

Thanks,
Dag

> My objective is to identify useless and extraneous indexes in the database.  
> I would like to capture a list of indexes used by the database while it is
> in use by an application over a period of time.   Comparing the list against
> ALL the indexes created in the system should provide a starting point for
> identifying extraneous indexes. 
>
> Alternatively, perhaps one might recommend enabling a logger class that
> outputs this information already.   
>
> Enabling logQueryPlan is my least favorite idea, because of its verbosity.  
> I'm open to other ideas too. 
>
> Thanks. 
> Mark
>
>
>
> --
> View this message in context: http://apache-database.10148.n7.nabble.com/Logging-indexes-used-by-queries-tp128355.html
> Sent from the Apache Derby Developers mailing list archive at Nabble.com.