Re: Oracle Tools Wanted

Heidi M Thorpe wrote in message <72arms$dn$1_at_news.mel.aone.net.au>...
>I have been asked to find a tool that will tell me to know when an index
has
>been accessed.

I doubt that there are tools available to do this, as Oracle itself keeps no record of when last an index was used.

There are however Query Governor products out there. 3 tier architecture stuff. Your 3rd party software thinks it's the database and talks to it. It in turn decide what to do with the SQL before passing it to the database. And it includes all kinds of controls like timing out queries, controlling resources, monitoring usage etc. I've been in a HP presentation some years ago where they punted their product. Not sure what other vendors have similar products and what platforms are supported and so on.

>We have many third party applications and in tuning them we need to know
>which indexes are used and which are not so that we can remove the unused
>indexes.

Can I open my big mouth again? :-)

I doubt that removing unused indexes will solve anything, except maybe freeing up some space. If such a situation exists where you're not sure what indexes are being used and what not, then that is a design or usage problem IMHO. Typical (speaking from a query/DSS/warehouse point of view) you will identify the "paths" used by users to get to the information they require. Let's say that they require all kinds of sales stats per product per region per time. That already tells you what possible indexes you will need to create. If they need to drill down from a specific sales stat to the actual invoices for that office/branch for a specific product for a specific week, then you know how to cater for that.

It's pretty much the same with any other application. I don't think it wise to run products against the database without knowing how those products are going to being used. It's impossible to sit at the back-end and attempt to address issues like db usage, security, control and performance without having a fair idea of the business/users' requirements. It's like flying in the dark without any instrumentation.

I know, often management or the users decide to purchase and use a product without prior consultation with the techies that need to support the damn thing. It always gets my hair up and I've been in numerous tooth and nail fights where I tell the business that -they- can not make the technical decisions.

I don't believe in this "end-user empowerment" bit, as it's always turns out to be a FUBAR in my experience. Oh well...

regards,
Billy Received on Wed Nov 11 1998 - 02:51:09 CST