RE: Oracle/Windows question
- From: "Allen, Brandon" <Brandon.Allen@xxxxxxxxxxx>
- To: <Joe.Sweetser@xxxxxxxxxxxxxxx>, <oracle-l@xxxxxxxxxxxxx>
- Date: Tue, 9 Aug 2005 11:16:44 -0700
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@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx]On Behalf Of Sweetser, Joe
Sent: Tuesday, August 09, 2005 10:48 AM
To: oracle-l@xxxxxxxxxxxxx
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
--
//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.
--
//www.freelists.org/webpage/oracle-l
Other related posts:
