Re: Oracle 9i very slow on certain queries

On Feb 10, 7:00 am, paultre..._at_gmail.com wrote:
> On Feb 9, 11:05 pm, "Mark D Powell" <Mark.Pow..._at_eds.com> wrote:
>
>
>
> > 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 --- Hide quoted text -
>
> > - Show quoted text -
>
> Apologies Frank if I can across short (I had posted before I thought
> about how it looked)- this was not intended
>
> OK - the reason I did the upgrade was purely for testing....and in the
> hope that some of the new features of 10g would throw up the problem
> during the upgrade.
> I rebuilt the indexes on the new database but no performance
> improvement. so this would lead me to think that it is some of the
> data in the database ??
> I have a dump I think from the night before the problem started to
> occur. This is what I am going to do and if you think this is the
> wrong approach then let me know
> option 1
> 1. Restore the dump from before the problem on test box
> 2. Compare the live with the test (the dump of the live before the
> problem)
> option 2
> 1. Restore the dump from before the problem on test box
> 2. Import sections (tables, sequences, etc) of the live DB and test at
> each import point
> 3. hopefully this will throw light on the problem
>
> Thank you both for your suggestions - I really value them despite how
> my above reply may appear
>
> Paul

If you think nothing has changed in the infrastructure .. or that nobody
has altered any oracle parameters etc. then you are probably looking at CBO changing execution plans of some queries. Not sure if you have been running statspack on this database, but that could have given you a better idea on what changed. I believe now your best shot is to identify the queries which are running
slow and start examining their execution plan/tuning them. You might just find a pattern and the above task might not be as big as it sounds.

Remember, when you analyze a schema/table, its always a good idea to backup current statistics...

Anurag Received on Sat Feb 10 2007 - 08:07:04 CST