[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1067
  • Last Modified:

Oracle9 Tuning for WindowsServer2003

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
Mark Geerlings
Asked:
Mark Geerlings
  • 5
  • 3
  • 2
  • +1
1 Solution
 
seazodiacCommented:
just curious, what are the server hardware specs? esp. Memory.

also have to try to set SQLNET.EXPIRE_TIME in the server machines...
0
 
seazodiacCommented:
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
 
Mark GeerlingsDatabase AdministratorAuthor Commented:
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!

 
seazodiacCommented:
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
 
schwertnerCommented:
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
 
Mark GeerlingsDatabase AdministratorAuthor Commented:
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
 
pavelhCommented:
Is this thread alive?
0
 
Mark GeerlingsDatabase AdministratorAuthor Commented:
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
 
Mark GeerlingsDatabase AdministratorAuthor Commented:
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
 
pavelhCommented:
0
 
Mark GeerlingsDatabase AdministratorAuthor Commented:
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

Restore individual SQL databases with ease

Veeam Explorer for Microsoft SQL Server delivers an easy-to-use, wizard-driven interface for restoring your databases from a backup. No expert SQL background required. Web interface provides a complete view of all available SQL databases to simplify the recovery of lost database

  • 5
  • 3
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now