SEO

#1 (**permalink**) 02-27-2008, 02:12 AM

Is there a way to find the following in UDB ?

1. How many times an index is used for a duration ?
2. Which SQL/application is using the index?

if not, is there a different way to find it ?

#2 (**permalink**) 02-27-2008, 02:13 AM

One way is to use EXPLAIN on all of the SQL that is being executed and
examine the explain tables. It will tell you what indexes are being used
by what SQL.

If you are interested in usage over a time interval; then you can run
statement traces and correlate the usage counts with the explain data.

If you are using bound packages (not dynamic SQL) then the catalog
tables contain dependency information that will tell you what packages
are dependent on which indexes. This cannot be used to determine usage
over an interval because you have no way to determine which SQL in the
package was actually executed.

Philip Sherman

SKC wrote:
> Is there a way to find the following in UDB ?
>
> 1. How many times an index is used for a duration ?
> 2. Which SQL/application is using the index?
>
> if not, is there a different way to find it ?

#3 (**permalink**) 02-27-2008, 02:13 AM

Query Patroller and it's historical analysis piece will allow you to
do such things potentially.

cshiva@gmail.com (SKC) wrote in message news:<de91becd.0411081134.133c744@posting.google.c om>...
> Is there a way to find the following in UDB ?
>
> 1. How many times an index is used for a duration ?
> 2. Which SQL/application is using the index?
>
> if not, is there a different way to find it ?

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

SEO

Index usage