Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Mailing Lists -> Oracle-L -> RE: Oracle/Windows question

RE: Oracle/Windows question

From: Allen, Brandon <>
Date: Wed, 10 Aug 2005 10:56:31 -0700
Message-ID: <04DDF147ED3A0D42B48A48A18D574C450236141E@NT15.oneneck.corp>

Joe, forget what I said about the /3gb switch - since you only have 3gb of RAM (I read your email too fast the first time), you should not set that switch and I'm not sure what the implications of setting it would be - Windows might just ignore it, or it might crash. You should only use the /3gb switch if you have 4gb+ RAM - this leaves 1GB for Windows and gives the other 3GB to the database (and/or other apps).

If I understand correctly (someone please correct me if I'm wrong), the 2GB limit applies to the sum of the SGA *AND* the memory for all the Oracle sessions, since they're all threads of the same single process - that's why you're running out of memory. The show sga command doesn't include all the memory used up by all the oracle server processes for your users. I suggest you read that note I mentioned earlier (46001.1) very carefully.

Check the count of sessions (select count(*) from v$session) when you get these errors - then have a few users log out to free up some memory and see if the error goes away as the session count goes down.


-----Original Message-----
Sent: Wednesday, August 10, 2005 10:22 AM To:
Subject: RE: Oracle/Windows question

First, thanks to all for suggestions/recommendations. I'm leaning towards a memory constraint but not ruling the network as another possible issue. Here's my SGA:

SQL> show sga

Total System Global Area 1809261100 bytes

Fixed Size                   455212 bytes
Variable Size             578813952 bytes
Database Buffers         1228800000 bytes
Redo Buffers                1191936 bytes
SQL> So, right out of the box I'm gobbling up almost 1.8gb. This wouldn't leave much room to grow before hitting the default 2gb limit. The /3GB switch is not set in the boot.ini. I have a recommendation in to the server folks to set the switch. Sanity check: just want to make sure this makes sense. By setting the switch, Oracle will be able to use up to ~3gb of virtual memory, thereby allowing more simultaneous connections. Is that correct?

Thanks again,

> -----Original Message-----
> From: Allen, Brandon []
> Sent: Tuesday, August 09, 2005 12:17 PM
> To: Sweetser, Joe;
> Subject: RE: Oracle/Windows question
> 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,
> Oracle can't use it. How much RAM do you have and how much are you
> according to task manager? Are you using the /3GB switch to allocate
> (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


> . . .
> Once the address space starts to fill with users session allocations
> 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
> server processes address space, these errors are likely to occur when
> 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
> operation this will be around 2.5GB / 2.7GB. It is important to
> that
> it is only the committed pages that are backed by physical memory or
> 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
> released, leaving the Oracle processes address space free of reference
> the completed session.
> If a users session terminates unexpectedly it will not release the
> it
> has allocated, the allocated pages will remain in the Oracle processes
> address
> space until the process exits. Unexpected termination may occur if a
> 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
> 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
> a
> large percentage of the 2GB address space of the Oracle process will
> become
> inaccessible, ultimately causing allocation problems when Oracle is
> 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
> 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)
> 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:
> []On Behalf Of Sweetser, Joe
> Sent: Tuesday, August 09, 2005 10:48 AM
> To:
> Subject: Oracle/Windows question
> Oracle
> Windows 2000 SP4
> 3gb memory
> The database is about 100gb. I seem to be having a problem with
> resources that is causing new connections to the database to not
> during peak times. Last time this occurred, I increased the processes
> parameter (from 300 to 400) and this alleviated the problem at the
> 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
> 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
> 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
> --
> Privileged/Confidential Information may be contained in this message
> attachments hereto. Please advise immediately if you or your employer
> not consent to Internet email for messages of this kind. Opinions,
> conclusions and other information in this message that do not relate
> the official business of this company shall be understood as neither
> nor endorsed by it.
-- --
Received on Wed Aug 10 2005 - 12:57:13 CDT

Original text of this message