db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Dag Wanvik <dag.wan...@oracle.com>
Subject Re: Logging indexes used by queries
Date Tue, 26 Mar 2013 02:08:47 GMT

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?


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

View raw message