Re: Which indexes are not used

SC wrote:
>
> Hello,
> Our database (Oracle 7.3.3.6, VMS ALpha 7.1) is
> reported to be running very slow. After looking
> at Toad - monitor option, I discovered
> that only 33 % of the queries are using indexes
> and rest are non-indexed queries. I gather that this
> is leading to large table scans thereby leading to
> I/O bottleneck.
> We have indexes for all the tables in place and
> no one has dropped the indexes. So why the problem
> is happening today?
>
> My questions are:-
> 1. How to find out which query is not using indexes?
> 2. Why are they not using indexes?
> 3. Which indexes are not used?
>
> About 75 users are logged into the database and I dont
> know how to identify which uses is creating the bottleneck.
>
> Any hint/ideas would be greatly appreciated.
>
> Please send a copy of your reply to
> s_c_99_at_hotmail.com as well.
>
> Thanks in advance.
>
> s_c_99_at_hotmail.com

        This is like the classic recipe for rabbit stew. The one that starts

        "First, catch a rabbit"

        First, find the queries that are consuming the most system resources.

        If you don't have a monitor program that will tell you just download

        orasnap from Rhubarb's site. (Search on orasnap for the URL)

        The output of the Disk Intensive SQL query lists in descending order

        the queries that do the most disk I/O. Start with disk enemy #1. Just

        copy it out and paste it into a edit file. Go through the file using

        your favorite editor and eliminate the extra blank spaces.

        Copy the query into T.O.A.D. and run explain plan. If you see full

        table scans (and you surely will for your biggest disk hogs) you can

        start developing a solution.

1. Just to be sure run ANALYZE INDEX index_name ESTIMATE STATISTICS;
2. Rerun EXPLAIN PLAN If this solves the problem write a script that analyzes the indexes every night.
3. Figure out which item in the where clause of the driving table will return the smallest number of rows and create an index for it.
4. Rerun EXPLAIN PLAN
5. Reiterate until done.

        Using the above technique today we cut the run time of a daily report

        run from 9 hours to well under one hour -- the user was ecstatic. (I

        have my fingers crossed to see how it runs tomorrow.) --
Jerry Gitomer
Once I learned how to spell DBA, I became one Received on Thu Apr 06 2000 - 21:59:48 CDT