Solved

Oracle9 Tuning for WindowsServer2003

Posted on 2004-09-14
11
1,033 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 34

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
 
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 47

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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 34

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 34

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 34

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 34

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Delphi selector screen 2 58
Oracle SQL Select Statement 19 58
SQL query of Oracle 10g database. 8 57
T-SQL Convert to PL/SQL 23 62
Article by: Swadhin
From the Oracle SQL Reference (http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/queries006.htm) we are told that a join is a query that combines rows from two or more tables, views, or materialized views. This article provides a glimps…
Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

757 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

Need Help in Real-Time?

Connect with top rated Experts

24 Experts available now in Live!

Get 1:1 Help Now