Oracle FAQ Your Portal to the Oracle Knowledge Grid

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

RE: Oracle/Windows question

From: <>
Date: Wed, 10 Aug 2005 10:22:03 -0700
Message-ID: <>

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:38:51 CDT

Original text of this message