Re: Oracle 9i very slow on certain queries

On Feb 9, 3:15 pm, Frank van Bortel <frank.van.bor..._at_gmail.com> wrote:
> paultre..._at_gmail.com schreef:
>
>
>
>
>
> > On Feb 9, 1:02 pm, "frank.van.bor..._at_gmail.com"
> > <frank.van.bor..._at_gmail.com> wrote:
> >> On 8 feb, 23:21, 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.
> >> Very obvious answer:
> >> What happened today with your infrastructure? Someone installed
> >> a service pack? RAID array in degraded mode? Someone all
> >> of a sudden decided statistics might be a good idea?
>
> >> Anyway: you get the picture: now fill in the blanks.- Hide quoted text -
>
> >> - Show quoted text -
>
> > very simple answer - nothing happened with my infrastructure:
>
> I don't believe it - maybe you are simply not aware of it, because
> it's someone else's responsibility (as often happens in larger
> firms - Windows boys don't discuss with the Oracle boys, etc).
> I
>
> > restored last night's dump to a new 10g Oracle database on a different
> > box to see in this would throw up my problem as I was having no joy
> > finding the route on to problem using Mark's suggested method
> > above(thank you Mark) ...
>
> > however to my horror I am still getting the same response times with
> > the new server running 10g (10.2.0.1.0 - Production) database with the
> > 9i(Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production) dump
> > imported.
>
> OK - that's something: you can reproduce the problem. Do you
> happen to have a dump of -say- a week old?
> Could you restore that, and see if the problem the goes away?
> If it does, it rules out all other options of hardware, OS, etc.
>
> --
> Regards,
> Frank van Bortel
>
> Top-posting is one way to shut me up...- Hide quoted text -
>
> - Show quoted text -

Not fair, the first post does not say that you did an upgrade from 9i to 10g.

Did the problem occur immediately after the upgrade or after the Oracle provided automatically scheduled gather stats job had time to run (over night)? The provided Oracle task generates histograms and if your 9i system did not use them then the sudden availability of the information can cause a lot of query plan changes and not necessarily for the better.

10g needs more memory than 9i so you should have added at least 5% and probably 10% to your shared pool with the upgrade.

Did you change any of the database parameter settings with the upgrade? Did you switch SGA memory methods (manual vs auto)?

HTH -- Mark D Powell -- Received on Fri Feb 09 2007 - 17:05:07 CST