Oracle/Windows question 2005-08-10 - By oracle-l-bounce@(protected)
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,
-joe
> -- --Original Message-- --
> From: Allen, Brandon [mailto:Brandon.Allen@(protected)]
> Sent: Tuesday, August 09, 2005 12:17 PM
> To: Sweetser, Joe; oracle-l@(protected)
> 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,
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 (See ORA-12500.ora-code.com) / TNS-12500
> - TNS:listener failed to start a dedicated server process
>
> Other possible errors include :
>
> - ORA-12540 (See ORA-12540.ora-code.com) / TNS-12540 TNS:internal limit restriction exceeded
> - NT-8 Not enough storage is available to process this command
> - skgpspawn failed:category = ....
> - ORA-27142 (See ORA-27142.ora-code.com) could not create new process
> - ORA-27143 (See ORA-27143.ora-code.com) OS system call failure
> - ORA-4030 (See ORA-4030.ora-code.com) 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@(protected)
> [mailto:oracle-l-bounce@(protected)]On Behalf Of Sweetser, Joe
> Sent: Tuesday, August 09, 2005 10:48 AM
> To: oracle-l@(protected)
> 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
|
|
