Ever wonder which indexes are being used in your database? Well Oracle 9i introduced the capability to track index usage. But you had to learn new ALTER INDEX command syntax to support this and then how to query V$OBJECT_USAGE view to see the index usage information. Well I’m too darn lazy, and why not be – as with most things in Oracle, Toad cleanly exposes this feature and increases its usability.
Let’s say we want to run a TPC-C OLTP benchmark and see whether the indexes created as per the TPC.org specification are actually used. It’s a very simple four step process.
- Use Benchmark Factory to create a project for creating and populating our base database objects, but don’t yet run the transaction mix. For readers of this blog, step #1 is simply to get your database created – which in your cases has already been done. Using Toad’s Schema Browser, I can easily see the TPC-C indexes.
- Use Toad to enable index monitoring on the indexes in question as shown here (note – you must be connected as a privileged user with SELECT CATALOG ROLE or SELECT ANY TABLE, or of course also a direct GRANT SELECT):
- Now simply run your test load, in this case the TPC-C benchmark using freeware version of Benchmark Factory, to generate the workload containing all the queries that are either using the indexes or not. Here’s Benchmark Factory showing that:
- Now we can experiment with different new indexing strategies and quickly see whether they make any difference (i.e. are they used in place of the prior indexes).
During development, such information can be very useful. It’s one thing to run explain plans while coding and to tune the statements. It’s quite another to actually run a sample test workload and see whether the indexes actually got used or not. Usually they do – but there are some scenarios where they may not. Depending upon the nature of the database statistics, histograms, workload mixture, time of day, saved outlines, sql tuning sets, and sometimes even planetary alignment – you’d be surprised by what does and does not get used.