Re: Enterprise slow, Standard fast?

From: IANAL_VISTA <IANAL_Vista_at_hotmail.com>
Date: Sat, 06 Nov 2004 15:37:38 GMT
Message-ID: <Xns95994D96B33D3SunnySD@68.6.19.6>

Arska <NaruArska_at_hotmail.com> wrote in news:cmiodv$g8t$1_at_plaza.suomi.net:

> DA Morgan wrote:
> 
>> Arska wrote:
>> 

>>> Sybrand Bakker wrote:
>>>
>>>> On Sat, 06 Nov 2004 10:07:18 +0200, Arska <NaruArska_at_hotmail.com>
>>>> wrote:
>>>>
>>>>
>>>>> Can anyone tell me what we need to different way in EE?
>>>>> Modifications to application? Oracle settings? OS?
>>>>>
>>>>> About enviroment:
>>>>> HP Proliant DL 380 G4
>>>>> 2 * 3.6 GHz Xeon
>>>>> 4 GB RAM
>>>>> 12 * 36 GB 15000 rpm disk, RAID 0+1
>>>>> White Box Enterprise Linux 3.0
>>>>> (Tested with RH7.3 and FC2 too)
>>>>> Tested with oracle:
>>>>> 9.2.0.4 SE (fast)
>>>>> 9.2.0.5 SE (fast)
>>>>> 9.2.0.4 EE (slow)
>>>>> 9.2.0.5 EE (slow)
>>>>> 10.2.0.3 EE (slow)
>>>>>
>>>>> Settings:
>>>>> Shared pool: 600 MB
>>>>> Buffer cache: 400 MB
>>>>> Large pool: 400 MB
>>>>> Java pool: 32 MB
>>>>> Aggregate PGA target: 665 MB
>>>>> Open cursors: 10000
>>>>> Processes: 300
>>>>> Other settings have default value.
>>>>
>>>>
>>>>
>>>>
>>>>
>>>> I would recommend trying to learn Oracle and trying to learn to
>>>> develop applications instead of just cranking up settings.
>>>> The above settings are absolutely ridiculous, especially the open
>>>> cursors as that is a *per session limit*.
>>>> Also your post doesn't contain the slightest trace of analysis what
>>>> the EE instance is waiting for.
>>>> Your general claim EE is slower than SE is just absolute nonsense, and
>>>> shows your ignoramce.
>>>>
>>>>
>>>> --
>>>> Sybrand Bakker, Senior Oracle DBA
>>>
>>>
>>>
>>> Thanks for the nice answer.
>>> You are absolutely right. I don't konow much about oracle.
>>> But I know, if I set open cursors to, for example 1000, sometimes one
>>> of our applications can not get cursor.
>>> And I know, our application develeper know something about oracle and
>>> he can't understand what is wrong in our installation.
>>> Installed many times beginning from OS inst, but result is always the
>>> same:
>>> SE works ok, but EE is REALLY slow.
>>> EE instance is waiting nothing, but still it does not use cpu more
>>> than 25%.
>>> Just like there is limit for cpu usage, but it should not be exist.
>>>
>>> If my settings are plain wrong, why SE does perform ok?

>> 
>> 
>> Perhaps the problem should be re-asked as "What's wrong with your
>> application developer?" Sybrand is correct that the settings are
>> outrageous. Open cursors greater than a few hundred is a sign that
>> something is likely wrong. My guess is the application is the problem,
>> not the database, and that you should turn your attention there.
>> 
> 
> OK.
> Thanks.
> I was in impression open_cursors is global limit. Need to read documents 
>   more carefully.
> So this is one think we need to correct, over 1000 cursors for one 
> session is too much.
> 
> I think we still look too much this one setting.
> I can set this to original value and it does not have any impact to this 
> 'huge' job.
> 
> And yes. Maybe there is other things wrong in application.
> Still we are using it on two sites without big performance problems.
> Usually about 250 concurrent users.
> 
> How can I find what exactly oracle EE is doing when it does not wait 
> anything and is not using all cpu available?
> 
> SE uses always much more cpu when this job is running.
> And yes, I can see if it is waiting for data or something but it is not.
> 

I concur with the other responders. The problem is NOT EE vs. SE; because SE is simply a subset of EE. The slowness comes from other differences such as hardware, initSID.ora values, DB size, DB statistics, or any number of other variables.

You need to gather verifiable facts; as opposed to unsubstantiated speculation.

You need to enable "EVENT 10046" at level=8 for at least sessions that are running "slow". This well create a *trc file which can be processed using "TKPROF". The resultant output will clearly show where time is being spent.

GOOGLE is your friend. Received on Sat Nov 06 2004 - 09:37:38 CST