RE: Oracle/Windows question

From: Allen, Brandon <Brandon.Allen_at_OneNeck.com>
Date: Tue, 9 Aug 2005 11:16:44 -0700
Message-ID: <04DDF147ED3A0D42B48A48A18D574C4502361410@NT15.oneneck.corp>

Joe, sounds like your most likely running out of RAM even though task manager might not show it - there might be plenty free for Windows, but Oracle can't use it. How much RAM do you have and how much are you using according to task manager? Are you using the /3GB switch to allocate 3GB (instead of only 2GB) to Oracle?

You might want to check out the following from the bottom of Metalink Note:46001.1:

d) How per session allocations are performed & address space fragmentation

. . .

Once the address space starts to fill with users session allocations the will be a danger that a new session can not be created due to the lack of available address space. If this occurs the most likely error is :

ORA-12500 / TNS-12500
TNS:listener failed to start a dedicated server process

Other possible errors include :

ORA-12540 / TNS-12540 TNS:internal limit restriction exceeded
NT-8 Not enough storage is available to process this command
skgpspawn failed:category = ....
ORA-27142 could not create new process
ORA-27143 OS system call failure
ORA-4030 out of process memory when trying to allocate ....

Due to address space fragmentation and dll's being loaded into the Oracle server processes address space, these errors are likely to occur when the Windows NT performance monitor shows the Oracle process has allocated around 1.6GB / 1.7GB of the 2GB address space. If the 4GT tuning feature is in operation this will be around 2.5GB / 2.7GB. It is important to remember that it is only the committed pages that are backed by physical memory or the page file.

e) How session memory is released & thread termination consequences

When a users session completes successfully it deallocates its memory using the Win32 API call "VirtualFree" with the MEM_DECOMMIT | MEM_RELEASE allocation flags. After all allocations have been freed the stack is also released, leaving the Oracle processes address space free of reference to the completed session.

If a users session terminates unexpectedly it will not release the memory it has allocated, the allocated pages will remain in the Oracle processes address space until the process exits. Unexpected termination may occur if a users session if forced to terminate for one of the following reasons :

Shutdown abort.
Alter session kill session.
Oracle command line utility orakill.
Oracle Administration assistant for Windows : kill session.
Other utilities that can kill threads in processes.

Oracle Support Services recommends customers minimize the use of the above commands, in particular the shutdown abort command. When shutdown abort is run its calls the Win32 API "TerminateThread" for each users session, which kills the thread without releasing its memory. On systems with many users a large percentage of the 2GB address space of the Oracle process will become inaccessible, ultimately causing allocation problems when Oracle is next started. The only way to release this memory is to stop and start the Oracle Service (e.g. OracleServiceORCL).

f) How to increase the number of sessions

For customers who need to achieve large user populations on Windows NT, the following can be used as a guide to optimising memory usage :

Reduce session parameters described in section 4.a to a minimum.
Reduce global database parameters described in section 3.a to a minimum.
Minimize the number of database job queues (job_queue_processes) and parallel query slaves (parallel_max_servers) as they also create threads in the Oracle executable.
Reduce the sessions / threads stack to 500K with ORASTACK.
Consider switching to the Oracle Multi Threaded Server (MTS).
Consider upgrading Windows NT to Windows NT Enterprise Edition.
Consider upgrading the hardware to support Intel ESMA.

-----Original Message-----
From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org]On Behalf Of Sweetser, Joe Sent: Tuesday, August 09, 2005 10:48 AM
To: oracle-l_at_freelists.org
Subject: Oracle/Windows question

Oracle 9.2.0.4.0
Windows 2000 SP4
3gb memory

The database is about 100gb. I seem to be having a problem with system resources that is causing new connections to the database to not happen during peak times. Last time this occurred, I increased the processes parameter (from 300 to 400) and this alleviated the problem at the time. But now it happened again. I just restarted the database and all is well for the time being, but I want to figure out what's happening.

There are no errors in the alert log. The listener.log shows repeated entries similar to this (I substituted actual names with <>'s):

09-AUG-2005 11:06:56 * (CONNECT_DATA=

  (SERVICE_NAME=<TNS service name>)
  (CID=(PROGRAM=C:\Temp\<xxxxxx>\1df\PCXWIN.EXE)
  (HOST=<source-hostname>)(USER=<username>))) 
* (ADDRESS=(PROTOCOL=tcp)(HOST=<IP address>)(PORT=2948))

* establish * <TNS service name> * 12500 TNS-12500: TNS:listener failed to start a dedicated server process TNS-12540: TNS:internal limit restriction exceeded TNS-12560: TNS:protocol adapter error
TNS-00510: Internal limit restriction exceeded 32-bit Windows Error: 8: Exec format error

Metalink research for the TNS 510 error (Note:171636.1) indicates there is a system resource issue. My problem is that I'm pretty green on Windows ("unix weenie, am I" -Yoda) and don't know where to look to see what's being used up. Task manager doesn't show any constraints with memory or CPU.

Any/all suggestions/hints/ideas are welcome.

Thanks,
-joe

--
http://www.freelists.org/webpage/oracle-l

Privileged/Confidential Information may be contained in this message or attachments hereto. Please advise immediately if you or your employer do not consent to Internet email for messages of this kind. Opinions, conclusions and other information in this message that do not relate to the official business of this company shall be understood as neither given nor endorsed by it.

--
http://www.freelists.org/webpage/oracle-l

Received on Tue Aug 09 2005 - 13:18:05 CDT