Re: tablescans vs indexed scan

In article <38873BBC.5E6225E1_at_hotmail.com>,   s_c_99_at_hotmail.com wrote:
> Hello,
>
> Our database is going very slow (Oracle 7.3.3.6, Open VMS-7.1)
> and we discovered that many queries are performing tablescans
> rather than indexed scans. This is increasing
> Input/Output drastically and hence the database is very slow.
>
> Could anyone please advise me how can I make the query to
> use indexes? I am sure that the indexes exist as we have
> always been using them. So why the performance is going
> down NOW ? Will rebuilding indexes be any help?
> Also how can i detect which indexes are used and
> which ones arent?
>
> Please Help. (please send me an email at s_c_99_at_hotmail.com
> as well)
>
> Thanks.
>
> SC.
>

Under the assumption that your init.ora set the optimizer to the default choose try updating your statistics.

Analyze table owner.table_name estimate statistics sample 50000 rows;

Also look at how often you are switching redo logs. You can see this in your alert log. If you have multiple log switches in a couple minute period or find messages that checkpoints were unable to complete then you probably could benefit from bigger redo log files.

If analyzing helped but you still find too many queries where the explain plain is doing a full table scan instead of using an index you can resort to hinting the SQL.

Look into upgrading to 7.3.4 which came out last July on OpenVMS Alpha. --
Mark D. Powell -- The only advice that counts is the advice that  you follow so follow your own advice --

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Thu Jan 20 2000 - 14:46:37 CST