Oracle support updated my sr to say, yes, we could use event 10132 for this purpose but that it involves some overhead, however small, as well as the 12k disk space per statement (10g) that Jonathan Lewis had already highlighted to me. If that’s undesirable, we could also use v$sql_plan as I mentioned before. This was the query that support suggested.
select sa.sql_text, do.object_name
from v$sql_plan sp, dba_objects do, v$sqlarea sa
where sp.object#=do.object_id
and sa.sql_id=sp.sql_id and sp.child_number=0There are many ways to skin this particular cat, including a suggestion from Peter Scott in a comment to the first blog.
One more word on event 10132. I forgot to say before that Jonathan mentioned this event could be useful before a major upgrade. Should this event be set pre-upgrade and the upgrade changed execution plans for the worse, you would have some pretty useful information to help solve the problems.
Interesting that support should give you that query as an alternative.
For 10g (at least) v$sqlarea is no long an aggregate across x$kglob, so there is no massive sort(aggregate) involved – but you will still hammer the library cache latches as you sweep your entire SQL library. Better to use v$sqlstats (new to 10.2) which is latch free.
But – how many times per day are you supposed to hammer the library cache (as you have to if you want to hit v$sql_plan) to find out what’s going on ?
And I don’t understand why you need to join to dba_objects to get the object_name since that seems to be available in v$sql_plan any way.
Moreover, if you stick to child number 0, you’re not getting all the plans for each statement – and the same statement could have two different execution plans, one which uses a given index and the other that doesn’t.
Here’s a fun thought though – just because a plan doesn’t use an index, that doesn’t mean you can afford to drop it. The optimizer may have used it to determine the plan. See:
http://www.jlcomp.demon.co.uk/index_usage.html
I was surprised to see them use v$sqlarea too. I didn’t question it much because we’re not going to use that approach anyway. We’re thinking about making this an interesting test of the 10g Access Advisor.
As for ‘a fun thought’ – I don’t think so 😉 I remember a room full of slightly stunned people in Edinburgh when you mentioned that possibility!
In practical terms, for this system, we can probably afford to get a few index decisions ‘wrong’ because there are a limited number of users running long-running reports. If we do turn out to drop an index that maybe we shouldn’t have, we’ll have plenty of opportunity to recreate it.
There are other OLTP systems with thousands of users that I would dread dropping an index on!