Re: Oracle 9i very slow on certain queries

From: Frank van Bortel <frank.van.bortel_at_gmail.com>
Date: Sat, 10 Feb 2007 15:55:38 +0100
Message-ID: <eqkrhl$lka$1@news4.zwoll1.ov.home.nl>

paultreacy_at_gmail.com schreef:
> 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
>

You now reply to Mark - not me.

Anyway, it is getting less and less clear - now you are referring to upgrades, earlier it was an import.

And if it was an upgrade, you do not regard that a change in your infrastructure?!?

-- 
Regards,
Frank van Bortel

Top-posting is one way to shut me up...

Received on Sat Feb 10 2007 - 08:55:38 CST