Solved

Oracle9 Tuning for WindowsServer2003

Posted on 2004-09-14
11
1,053 Views
Last Modified: 2012-05-05
We have had some crashes lately in our production database that has about 550 concurrent connections using a shared server configuration and all of the crashes have alert.log entries like: "found dead shared server..." or "found dead dispatcher...".  We had to switch to shared server (from dedicated) a year ago when we hit 350 concurrent connections.

Does anyone have successful experience configuring Oracle9.2 for WindowsServer2003 to support more than 500 connections?

Or, has anyone tried running two instances on one Windows server that both use the same database?
0
Comment
Question by:Mark Geerlings
  • 5
  • 3
  • 2
  • +1
11 Comments
 
LVL 23

Expert Comment

by:seazodiac
ID: 12057811
just curious, what are the server hardware specs? esp. Memory.

also have to try to set SQLNET.EXPIRE_TIME in the server machines...
0
 
LVL 23

Expert Comment

by:seazodiac
ID: 12057869
just for a testing, have you tried to increase sga_max_size in init.ora file.

try to bump it close to 4GB within windows if there is enough physical memory.
0
 
LVL 35

Author Comment

by:Mark Geerlings
ID: 12058106
Our server hardware:
RAM: 4GB
Processors: 2 (3.3ghz)
Disks: 12 - 38GB disks configured as four pairs of RAID1, and one four-disk RAID5 set

One of our limitations is WindowsServer2003 Standard Edition which only supports 4GB of RAM total and 2GB per process.  We are upgrading our (identical) test server today to the Enterprise Version of WindowsServer2003 which can support more RAM, and we are also buying more RAM (2GB per server) that we should have by Thursday.  We plan to move our production database to our current test server after we get the Enterprise version of Windows and the additional RAM on that, then upgrade our current production server to match and make that our test server.

For now, no, I cannot have the SGA over 2GB.  In fact if it is much over 1.6GB (as reported via "show sga") we get memory-related errors.

We just found that our initialization settings for:
max_shared_servers
and:
shared_servers
may have been to low.  They were set to 40 and 20 respectively, but some documentation suggests one for every 10 sessions.  "Max_shared_servers" is not changeable on-the-fly, but I did change that value in our spfile (to 80) and I changed "shared_servers" to our current max of: 40.
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 23

Expert Comment

by:seazodiac
ID: 12058717
Sometimes, it is maybe due to the fact that PMON process does not do a good job of maintaining the MTS server process.
there is a bug regarding PMON process unable to keep a MTS pool in windows implementation.
0
 
LVL 48

Expert Comment

by:schwertner
ID: 12061978
500 connections is too much.
Each connection uses 5MB (in MTS may be more because of the dispatchers).
Try to reduce the number of the connections.
0
 
LVL 35

Author Comment

by:Mark Geerlings
ID: 12086427
Yes, we may have encountered an Oracle bug.  We opened a TAR with Oracle Support on this issue, and that is their assessment.  They wanted us to turn on some events and generate some trace files for their troubleshooting, but our users are tired of the crashes, so we are trying to use dedicated connections instead of shared to avoid the apparent bug with MTS on Windows.

We haven't found the combination of initialization parameters though that we need for the number of dedicated connections we want to have (550).  We got to 410 concurrent dedicated connections before hitting ORA-04030 errors this morning.  We then bumped up the java_pool_size, shut down and restarted.  Here are the most significant (we think) spfile settings:
*.java_pool_size=120000000
*.large_pool_size=120000000
*.pga_aggregate_target=681574400
*.processes=800
*.sessions=800
*.sga_max_size=1600000000
*.shared_pool_reserved_size=30000000
*.shared_pool_size=300000000

A select from v$pgastat just before we hit the ORA-04030 errors this monring made it look like Oracle had only used half of the 640M possible for the PGA, and the SGA size was just over 1GB - nowhere near its max.  We do have the /3GB switch in the boot.ini file, so Windows should be able to use up to 3GB of RAM for Oracle.  The Windows Task Manager only reports just over 1GB of RAM for oracle.exe, and no other processes taking more than 30K.
0
 
LVL 1

Expert Comment

by:pavelh
ID: 12275907
Is this thread alive?
0
 
LVL 35

Author Comment

by:Mark Geerlings
ID: 12277717
I noticed your question today, so in that sense this thread is still "alive".  No, we don't have a definite solution to the problem, but we have a work-around: dedicated connections.  We upgraded the Windows O/S from the "Standard" version (which can support a max of 4GB of RAM) to the "Enterprise" version and added 2GB of RAM, giving us a total of 6GB now.   No, that Windows "upgrade" (a complete re-install) was not fun, and I don't recommend that to anyone who can avoid it, since all of the security, network shares, scheduled tasks, etc. in the O/S need to be re-created after the Windows re-install.  Plus, the Oracle (and any other) executables need to be re-installed.

We also used "orastack" to reduce the amount of memory per connection from the default 1M to 512K, and we adjusted the spfile parameters some more.  In our test system that way we got to 780 concurrent, dedicated sessions.

Here are the values from our spfile at present:
*.db_block_size=4096
*.db_cache_size=400000000
*.java_pool_size=23592560
*.job_queue_processes=10
*.large_pool_size=24000000
*.max_shared_servers=0
*.open_cursors=400
*.pga_aggregate_target=800000000
*.processes=800
*.session_cached_cursors=50
*.sessions=800
*.sga_max_size=1000000000
*.shared_pool_reserved_size=30000000
*.shared_pool_size=250000000
*.shared_servers=0
*.sort_area_size=262144

0
 
LVL 35

Author Comment

by:Mark Geerlings
ID: 12278310
We also added these two switches to the boot.ini file after adding more RAM:
/3GB /PAE
so Windows can use the additional RAM and allow more than 2GB to one process (oracle.exe).
0
 
LVL 1

Accepted Solution

by:
pavelh earned 500 total points
ID: 12284862
0
 
LVL 35

Author Comment

by:Mark Geerlings
ID: 12349990
I accepted the response from pavelh just to close this question.  It isn't really an answer, but it is a link to a related article that may help someone else.

No, we don't have the option of reducing connections, and no we don't dare try shared server connections again.  Within a year, we plan to move our database from Windows to Linux, likely in a RAC configuration, and likely on Oracle10 instead of Oracle9.  That is the long term answer to this problem.
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious side…
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…
Via a live example, show how to take different types of Oracle backups using RMAN.

749 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question