Re: Oracle 9i very slow on certain queries

On Feb 8, 5:21 pm, paultre..._at_gmail.com wrote:
> Hi,
> my company has the following setup for 2 years:
> - NT Server 4 with oracle 9i server
> - Forms and Reports 6i connecting to the server from client
> - these are XP clients connected to an NT domain
>
> All queries etc were running fine for the last 2 years until today
> when suddenly some of the queries to the database started to perform
> very slowly.
>
> Queries that took seconds now are taking minutes and some of the
> reports are bombing out as a result.... I was wonder if there are any
> checks that I can run to specifcally isolate the problem/s
>
> Indexes are the only thing that comes to mind as an explaintion to the
> problem but how can I isolate with indexes to look at?
>
> If I do isolate an index as the problem is a rebuild the correct
> option
>
> Thanks in advance.

Indexes? There could be any number of causes.

Someone added, updated, or removed the optimizer statistics resulting in query plan changes

One too many applications was added to the server and has overloaded the environment

There was/is something going on in the disk farm such as a disk failing in a RAID-5 setup but the disk is still responding to queries

Use a couple of statspack snapshots to see what is going on in the database

Run explain plan on some of the slow SQL

Check the last_analyzed date in dba_tables and dba_indexes

Check the alert log for error messages or database strurcture change messages

USe the OS tools, such as available on Windows, and make sure the cpu usage, memory usage, and disk io look OK

HTH -- Mark D Powell -- Received on Thu Feb 08 2007 - 18:09:20 CST