process become slower

[PSoni]

Don't take it wrong, I trust what you say but it's really hard to believe reinstallation of Oracle binaries has something to do with getting better performance.

As per previous postings who have said you already tried exp/imp with no success.

Lets try to take Oracle binaries out of the equation. Could you please do the following two tests?

++ Test #1

1-- Shutdown your database
2-- Reboot your server
3-- Startup your database
4-- Check performance... how is it now?
IF performance is better either Oracle services recycle or O/S
recycling fixed it.

If Test #1 does not does the trick...try test #2...

++ Test #2

1-- Export your erp database (just in case)
2-- Uninstall Oracle software
3-- Reinstall Oracle software
4-- Create your erp database pointing to the old datafiles
5-- DO NOT IMPORT the data, lets Oracle work with the old data
6-- Startup your database and check performance... how is it now?

Test#1 we are doing this
but no gains

Test#2 i will do and let you update.

bye-the-way what will it indicate if it success?

[reydp]

I'll agree with the current analysis.

Just a question to the poster..
may I know what is the memory of your win2k server? and how much is allocated for your oracle db?

[PAVB]

Test#2 i will do and let you update.
bye-the-way what will it indicate if it success?

man... if reinstalling Oracle binaries solves performance issues by itself I'll have to seriously think in looking for a different kind of job

My bet is it won't work meaning, you are gonna experience the very same bad performance afterwards, then we take uninstall/reinstall out of the equation and we can focus on SGA/PGA and data reorganization.

[PSoni]

I'll agree with the current analysis.

Just a question to the poster..
may I know what is the memory of your win2k server? and how much is allocated for your oracle db?

Total Memory of W2k server is 2GB

Memory for oracle is as under

Share Pool 1024 MB
Buffer Cache 150 MB
Large Pool 122 MB
Java Pool 50 MB

Total SGA = 1346.76 MB

Sort Area Size = 2 MB
Concurrent users 150-2000

[slimdave]

bye-the-way what will it indicate if it success?

Then do the same thing but run statspack and find out what is making the difference.

[Kamkan]

Total Memory of W2k server is 2GB

Memory for oracle is as under

Share Pool 1024 MB
Buffer Cache 150 MB
Large Pool 122 MB
Java Pool 50 MB

Total SGA = 1346.76 MB

Sort Area Size = 2 MB
Concurrent users 150-2000

Hi,
This may be due to memory stack issue. Every thread occupies 1MB and uses upto 200kb initially. If you calculate 2000conns*1MB = No free memory. More over Windows default max mem setting for single process is 2GB. Due to this no new connection cud be established once all the 2GB occupied.
U can try changing the orastack of oracle binaries to 500kb. (2000 conn*500kb) more mem can be left to use.

Is that shared pool sized correctly?

[slimdave]

U can try changing the orastack of oracle binaries to 500kb. (2000 conn*500kb) more mem can be left to use.

Yes, you could guess that it is some obscure memory issue and run another funky experiment, or you could run statspack and actually find out what is making the system slow.

[reydp]

Yes, you could guess that it is some obscure memory issue and run another funky experiment, or you could run statspack and actually find out what is making the system slow.

Hi Dave,

With due respect to you Dave 'coz you're one the icon here in this forum.

Yes, running statspack will surely help. But will it shows how many client connection were rejected by the LISTENER? How much paging/swapping were O.S. utilized just to accomodate db server's requirements?

With 2G of Memory.
Minus O.S. process
Minus ORACLE SGA
Minus ORACLE PGA for 500-2000 concurrent connection
Minus etc..

2G is surely not enough obviously.

[Bore]

Guys,
That goes out of control. Memory is low, DB is fragmented, stats are not generated etc. there can be thousands of reasons for that. Statspack or trace or whatever, but the poster should analyze the situation, come to the root of the problem and solve it. Just playing around will hardly help

Regards
Boris

[reydp]

Boris,
I'm just wondering what is there for you not to understand?

The poster gives this config:
-----------------------------------------
Total Memory of W2k server is 2GB

Memory for oracle is as under

Share Pool 1024 MB
Buffer Cache 150 MB
Large Pool 122 MB
Java Pool 50 MB

Total SGA = 1346.76 MB

Sort Area Size = 2 MB
Concurrent users 150-2000
---------------------------------------

WINDOWS 2000 SERVER O.S. = 100MB->150MB
SGA=1.35G
PGA=(150->2000)*2M(#2m here is minimum) = 300MB -> 4G at least
+ HASH_AREA_SIZE
+ JAVA_POOL_SIZE
+ etc
--------------------------------------
+ O.S. overheads
+ ORACLE overheads
==============================
Variables with figures alone assuming with minimum value will have a sum of:
100mb + 1.35G + 300mb = 1.75Gbytes
==============================
if you follow what WINDOWS recommendation(specially if it is not highmem) which is:
you should not allocate more than 50% of the physical memory for ORACLE SGA.
then, this is more than 80% contiguous memory already consumed with minimal concurrent connection.

I really don't believe that it will goes up to 2000 concurrent connection,
I mean this is simply not true and therefore impossible.