[10gR2][DBA]Threads Oracle non libérés dans W2K3/ORA-04030

Bonjour à tous,
Nous avons une base de production 10gR2 Std sur Windows Server Std 2003 SP1 32-bits avec les switches /3GB /PAE (pour allouer plus de 2GB au process oracle.exe).
C'est une base transactionnelle à laquelle se connectent des pools de connection JDBC Thin driver.
La SGA max est réglée à 1904 Mo.
Pour monitorer la mémoire suite à des erreurs ORA-04030, j'ai activé perfmon.
Il apparait que le nombre de threads liés au process oracle.exe est largement supérieur au nombre de sessions référencées par la requête suivante :

Code:

---

1 2 3

select spid, osuser, s.program,background from v$process p, v$session s where p.addr=s.paddr;

---

Cette requête me retourne 181 threads (cohérant par rapport à l'activité de la base), alors qu'il y en a 346 associés au process oracle.exe dans le moniteur de performances NT.

Aussi :
- Dois je trouver le même nombre de threads oracle dans la requête SQL ci-dessus et dans le moniteur de performance ?
- Si oui, quelle piste aborder pour identifier la nature de la non-libération de ces threads au niveau OS ?
Et comment libérer les threads non référencés par la requête SQL ci-dessus sans arrêter le service (base de prod 24/7...), ni utiliser la commande orakill à la chaine ?
Il y a des notes très intéressantes sur MetaLink (316652.1) mais je n'y ai pas trouvé la solution à mon problème.

Merci à tous de votre aide.

Attention :
Toute manip ici nécessite un arrêt oracle voire de windows et oui !
boot.ini, regedit...


Je n'ai plus eu de ora-4030 en utilisant ces info




1. Purpose
==========

This note explains how Oracle interacts with Windows NT's memory architecture,
for addition information about NT's memory architecture see [NOTE:46053.1]


2. How the Oracle Process interacts with the Windows NT memory architecture
===========================================================================

a) Total addressable memory per database
----------------------------------------

On Windows NT an Oracle instance runs as a single process, this process is a
standard Win32 application that is able to make memory allocations from the
2GB virtual address space. All memory allocations made by all user connections
and background threads have to fit into 2GBs including global allocations such
as the buffer cache. For systems that have either large user populations or
buffer cache requirements this presents a problem, later in this note we
discuss reducing the impact of this problem. Although the ultimate solution
will come when Windows supports a 64-Bit virtual address model.

b) Configuring Windows NT memory for the Oracle process
-------------------------------------------------------

PDC / BDC :

The Oracle database will run on all editions and many configurations of
Windows NT, but Oracle does not normally recommend customers run it on Primary
and Backup Domain Controllers (PDC/BDC). The main reason for this is because
domain controllers tend to require larger file caches (which reduces memory
available to the database) and network resources, Oracle would recommend :

If you plan to install the Oracle database on either a PDC or BDC you should
re-review the hardware choices you made in capacity planning. You should
account for the additional overhead that is incurred by the domain controller
services. In large networks running on Windows NT, substantial resources can
be required by domain controllers for performing directory-replication and
server-authentication for clients. However, if you have a very small network
in which account information rarely changes and in which users do not log on
and off frequently, you should be able to run Oracle without encountering
under resourcing issues.

Windows NT File Cache Size :

Another common question relates to the Windows NT file cache, when running
the machine as a dedicated database server or as a mixed usage server. The
first point to note is that the Oracle database does not use the file cache,
it writes direct to disk avoiding the Windows NT file cache and manages data
caching independently.

When Windows NT is installed the LAN Manager Server ("Server" in the list
of installed network software/services in Control Panel) is set to "Maximize
Throughput for File Sharing". The LAN manager server service is generally
responsible for named pipes, file and print services. These services can
cause considerable memory allocations to be made for internal buffers and
tables depending on the amount of physical memory installed. It also affects
the size of the file cache depending on what its optimisation parameter is
set to.

Choosing the appropriate optimisation parameter depends on the type of system
you have, dedicated database server or mixed usage server. For systems that
run a mix of applications or are domain controllers the default setting will
probably be the best compromise. This may also be true for dedicated database
servers where named pipes is the preferred SQL*Net protocol, because named
pipes is the only inter-process communications mechanism to use the server
service.

If the optimisation is set to "Maximize Throughput for File Sharing" Windows
NT sets a very high maximum size for the file cache working set, which is not
desirable for dedicated database servers using TCP/IP Sockets as the SQL*Net
connectivity protocol. Generally customers should set the optimisation to
"Maximize Throughput for Network Applications" because it favours the working
set of processes over the working set of the file system cache. But if the
server service is not being used at all on dedicated database servers it will
be better to set the optimisation to "Minimize Memory Used", because it
favours the process working set over the file system cache in the same way and
minimizes the internal buffers that are created.

The above settings do not apply to Windows NT Workstation.

Page File Size :

On systems that run as dedicated Oracle database servers customers should
strive to ensure that the pagefile is not used at all. This can be achieved
by reducing the relevant init.ora parameters or by increasing physical memory.
If a large number of pages are continually moving to and from the pagefile
performance of the database will be very poor.

When running as a dedicated Oracle database server we would make the following
recommendations :

- If the total memory allocated by Oracle can be guaranteed not to exceed
physical memory, the pagefile can be set to 50% of physical memory with
the ability to grow to 100%.
- For the majority of Windows NT servers running purely Oracle databases
we would normally recommend the pagefile be between one and one and a
half times physical memory on the machine.
- For machines with greater than 2GB of main memory, a 2GB pagefile should
be adequate.

The total memory that can be allocated (commit limit) on a machine is equal
to : physical memory plus pagefile size before extension. Customers should
avoid setting parameters such as db_block_buffers, or combinations of init.ora
parameters to exceed physical memory. This is allowed as long as it is within
the commit limit, but as pages are accessed more and more paging will occur,
which will in turn degrade systems performance. For example if a machine has
1GB of physical memory / 1GB pagefile and db_block_buffers are set to 1.2GB
we will commit all these pages without error, but as we access more than 1GB
worth of the buffer cache, pages will be swapped in and out causing poor
performance. If the buffer cache hit ratio is high db_block_buffers can be
decreased, where as if it is low more memory should be added.

On systems that have a mix of applications running concurrently, some of which
may be idle for a period of time, the size of the pagefile may need to be
considerably larger than physical memory. Processes that are not currently
in use will have their working sets reduced to allow active processes working
sets to be increased. If Oracle is running in such an environment we would
recommend that the pagefile be at least 1.5 to 2 or more time the physical
memory of the machine. This may even be necessary when memory is greater
than 2GB.

c) Altering the way that the Oracle process interacts with memory
-----------------------------------------------------------------

Prior to Oracle 8.1.x starting the Oracle Service did not start the Database
Instance, when the Service is started but the instance is not the majority
of the memory associated with the ORACLE.EXE process is the memory mapped
dll's, this may be as much as 20MB. On starting up an Oracle instance all
global memory pages are reserved and committed (Total Shared Global Area,
Buffer Cache and Redo Buffers). Only a small number of these memory pages are
touched on startup and are thus not in Oracle's working set, as more pages are
touched they will be brought into memory. Oracle must contend equally with
other processes and will have its working set trimmed if other processes are
faulting at a greater rate.

It is unlikely that this trimming will be desirable, especially when the
database has a varying workload (high and low usage periods). Two registry
parameters exist to allow the administrator to manipulate the working set
bounds of the Oracle process, these are :

- ORA_WORKINGSETMIN or ORA_%SID%_WORKINGSETMIN
: Minimum working set for the ORACLE.EXE process (units = MB)
- ORA_WORKINGSETMAX or ORA_%SID%_WORKINGSETMAX
: Maximum working set for the ORACLE.EXE process (units = MB)

These parameters apply to all releases from 7.3.x and should be added to the
registry under :

HKEY_LOCAL_MACHINE -> SOFTWARE -> ORACLE
or HKEY_LOCAL_MACHINE -> SOFTWARE -> ORACLE -> HOMEx (for multiple homes)

The main benefit of setting these parameters will be in environments where
Oracle coexists with other applications. Although it can be beneficial in
environments which run production and test instances on the same machine, the
production instance can use a large ORA_%SID%_WORKINGSETMIN to ensure that
test instances do not cause working set trimming of production instances.

Customers who are using these registry entries should consider using them in
combination with the init.ora parameter PRE_PAGE_SGA, which causes Oracle to
touch all the SGA pages (including the buffer cache), bringing them into the
working set of the Oracle executable. This increases instance startup time but
allows the instance to reach its maximum performance capability quickly,
rather than through an incremental build up as pages are loaded.

ORA_WORKINGSETMIN is the most useful parameter and prevents the working set
of the Oracle process from dropping below the threshold until the instance
is shutdown :

a) If used in combination with PRE_PAGE_SGA, the working set will start
above the minimum threshold and not drop below.
b) If ORA_WORKINGSETMIN is used in isolation, then once the working set
rises above the threshold it will not drop below.

Always use these parameters with caution, because they change NT's default
behaviour. Before using these parameters, ensure that the page file is
large enough and remember that pages above the minimum threshold can still
be paged out.


3. Database global memory allocations
=====================================

a) Database parameters
----------------------

All memory allocations made by an Oracle instance are limited by the process
address space as described in section 2.a. This means that the total memory
available to parameters described in this section can not exceed 2GB unless
the server has been configured as described in sections 3.d or 3.e. In
production systems users sessions will also be making allocations from this
address space which will further restrict the memory available to these
parameters.

The following table defines the main init.ora parameters that cause global
allocations within the oracle process and their restrictions :

+--------------------------+-------------------------------------+
| Parameter | Maximum values |
+--------------------------+-------------------------------------+
| buffer_pool_keep | 4GB / db_block_size - other buffers |
| buffer_pool_recycle | 4GB / db_block_size - other buffers |
| db_block_buffers | 4GB / db_block_size - buffer pools |
+--------------------------+-------------------------------------+
| log_buffer | defaults to 500k maximum 4GB |
+--------------------------+-------------------------------------+
| java_pool_size | 1GB |
| shared_pool_size | 4GB |
| large_pool_size | 4GB |
+--------------------------+-------------------------------------+
| shared_memory_address | Not applicable to Windows NT |
| hi_shared_memory_address | Not applicable to Windows NT |
+--------------------------+-------------------------------------+

The memory available to all these parameters added together can never exceed
the available address space and can be described as :

Total Available for = <address space> - <code size> - <session memory>
Global Allocations

A common question asked by customers is, how large can I set db_block_buffers
to ? Below is an example for a system running with 50 users and a 4K block
size :

Address Space : 2,000,000,000
Average Code Size : 50,000,000 -
java_pool_size : 0 -
shared_pool_size : 50,000,000 -
large_pool_size : 0 -
Session Memory : 150,000,000 - (50 users with average session = 3MB)
--------------
db_block_buffers = 1,750,000,000 / 4096 = 427,200

Prior to 8.1.5 the Getting Started Guide for Windows NT stated that the
db_block_buffers parameter was restricted to 3200, this is not correct and
is documented in BUG:705601. Please note it may not be possible to achieve
exactly the number of user sessions listed above due to address space
fragmentation described in section 4.

b) Locking the SGA in memory
----------------------------

The ability to lock the SGA in memory is provided by Oracle on a number of
platforms with the LOCK_SGA and LOCK_SGA_AREAS (obsolete in 8.1) init.ora
parameters. This feature is not available on Windows NT and will cause startup
to return ORA-27102, this is documented in BUG:642267.

It is unlikely that locking the SGA in memory would have much benefit over
setting the working set as described in 2.c, because it would not influence
memory allocated to users sessions which could still be swapped out.

c) How global allocations are performed
---------------------------------------

When the database starts up Oracle creates a contiguous memory region in the
virtual address space for each parameter listed in section 3.a. This memory
is always reserved and committed, using the Win32 API call "VirtualAlloc" with
the MEM_RESERVE | MEM_COMMIT allocation flags and using the PAGE_READWRITE
protection flag. This ensures that all threads can access the memory and
all pages in the region will be backed by physical storage (memory or disk).
VirtualAlloc does not touch pages in the region, meaning that the pages
allocated for parameters such as db_block_buffers will not be in the working
set of the process until touched. The Java pool introduced in Oracle 8.1 has
to be initialised and all pages defined in java_pool_size are brought into
the processes working set.

The buffer cache / buffer pools are usually created as single contiguous
regions, but they do not have to be. This is especially important when using
large buffer caches because the virtual address space will be fragmented by
dll's and thread allocations. System Dll's are usually loaded high around the
2GB boundary, which will prevent a single region being created, especially
when using the 4GT option described in section 3.d. To get around this problem
Oracle8 repeatedly retries to create sub areas half the size of the previous
until the entire cache is allocated.

A problem (BUG:943211) has been identified with the above algorithm which will
affect customers trying to allocate large buffer caches, customers hitting
this bug should either upgrade to 8.1.6 or higher, or request a backport to
8.0.6. This algorithm does not apply to the other parameters defined in
section 3.a which means setting them to large values may fail due to the
virtual address space fragmentation.

Oracle7 has to be configured to avoid address space fragmentation by using the
registry value ORA_MAX_SGA_ALLOC, which defines the maximum allocation size
and should be added to the registry under :

HKEY_LOCAL_MACHINE -> SOFTWARE -> ORACLE

This parameter was renamed in Oracle8 to ORA_MAX_ALLOC and no longer affects
the size of buffer cache sub area allocations.

A number of the Oracle Database ports attempt to protect these global regions
(particularly the log buffer) from erroronious access by stray pointers (e.g.
pointers writing past the end of a valid region) using a guard page above and
below the region. This could be implemented on Windows NT using a free page or
a page marked with the PAGE_GUARD or PAGE_READONLY protection flag, but has
not been implemented.

d) Configuring the Oracle process to make allocations greater than 2GB
----------------------------------------------------------------------

The Oracle database supports the 4GT tuning feature of Windows NT Server,
Enterprise Edition from release 7.3 onwards, allowing it to access up to 3GB
of virtual address space per instance. It may be the case that certain
releases / patch sets do not have the 4GT flag set even though the release
does support the feature. To check the executable has been correctly enabled
run :

imagecfg oracle.exe

oracle.exe contains the following configuration information:
Subsystem Version of 4.0
Image can handle large (>2GB) addresses
Stack Reserve Size: 0x100000
Stack Commit Size: 0x1000

For executables that do not have the flag set, run :

imagecfg -l oracle.exe

The above settings will only take affect if the boot.ini has been set up as
described in [NOTE:46053.1].

e) Configuring the Oracle process to make allocations greater than 3GB
----------------------------------------------------------------------

Oracle 8.1.5 for Windows NT introduced support for the Intel ESMA (Extended
Server Memory Architecture), which allows Oracle to access more than the 3GB
of physical memory traditionally available to Windows NT applications.

Access to this memory is limited to a single Oracle instance, but this
instance can now allocate substantially more database buffers than previous
releases. Additional information is available in [NOTE:46053.1] and on the
Intel Web site.

To take advantage of this support carry out the following steps :

1. More than 4GB of RAM must be present in the server on which Oracle will
run. ESMA can be used with servers with less than 4GB of memory, but
Oracle does not recommend customers use this configuration.

2. Windows NT 4.0 Enterprise Edition, Service Pack 3 or later must be
installed on the server.

3. The Intel PSE36 driver must be installed and operational on the server.
Customers should refer to their hardware vendor and the Intel Web site
on how to set up the driver.

4. USE_INDIRECT_DATA_BUFFERS = TRUE must be set in the init.ora for the
database instance that will use the PSE36 driver. If this parameter is not
set, then Oracle behaves in exactly the same way as previous releases.

5. Set DB_BLOCK_BUFFERS and DB_BLOCK_SIZE as desired for the database.

The total number of bytes of database buffers (that is, DB_BLOCK_BUFFERS
multiplied by DB_BLOCK_SIZE) is no longer limited to the 3GB of address
space as was the case in previous releases.

6. The VLM_BUFFER_MEMORY registry parameter must be created and set in the
appropriate key for your Oracle home in the Windows NT Registry :

HKEY_LOCAL_MACHINE -> SOFTWARE -> ORACLE -> HOMEx

This parameter is specified in bytes and has a default of 1GB. When set,
this parameter tells Oracle how much non-PSE36 memory to use for database
buffers. This memory comes from Oracle's virtual address space as was the
case in previous releases. Setting this parameter to a large value has the
effect of using more of Oracle's address space for buffers and using less
PSE36 memory for buffers (assuming the DB_BLOCK_BUFFERS parameter remains
the same). However, since accessing PSE36 buffers is somewhat slower than
accessing virtual address space buffers, tune this parameter to be as large
as possible without adversely limiting database operations (user session
memory).

For instance, assume that the Oracle database is running on a machine with
8GB of RAM, which means that the PSE36 driver has control of 4GB of RAM.
If DB_BLOCK_BUFFERS = 2500000 and DB_BLOCK_SIZE = 2048, then a total of
5GB of database buffers needs to be allocated. If VLM_BUFFER_MEMORY is set
to 1GB, then 1GB of buffers come from the Oracle virtual address space and
4GB come from the PSE36 driver. If you set VLM_BUFFER_MEMORY to 500MB, an
error occurs at startup because there is not 4.5GB of memory available to
the PSE36 driver for database buffers. Likewise, if VLM_BUFFER_MEMORY is
set to 3GB, an error occurs because the Oracle8i address space is limited
to 3GB on Windows NT, and this address space must also hold Oracle code,
shared pool, PGA memory, and other structures. In general, the higher the
VLM_BUFFER_MEMORY is set, the fewer connections and memory allocations will
be possible for Oracle. The lower VLM_BUFFER_MEMORY is set, the slower the
performance will be.

The maximum size of the buffer cache is thus expressed as :

= VLM_BUFFER_MEMORY + Physical Memory above 4GB

7. Once these parameters are set, the Oracle database can be started up and
will function exactly the same as before except that more database buffers
are available to the instance. In addition, disk I/O may be reduced since
more Oracle data blocks can be cached in the SGA. If out of memory errors
occur during the startup sequence, verify the following:

a. PSE36 driver is installed and functional
b. DB_BLOCK_BUFFERS is not set too high for the amount of memory in the
machine. Note that more memory than just the database buffers themselves
is required when starting up the database. For each database buffer, a
database buffer header is also allocated in Oracle virtual address
space. When allocating 2,000,000 database buffers, the memory for these
buffer headers amounts to several hundred megabytes. This must be
considered when setting DB_BLOCK_BUFFERS and VLM_BUFFER_MEMORY.
c. VLM_BUFFER_MEMORY is not set too high for the amount of address space
available to Oracle. In Windows NT's Performance Monitor, under the
Process object, monitor the Virtual Bytes counter for the "ORACLE"
process. If this counter approaches 3GB, then out of memory errors can
occur. If this happens, reduce DB_BLOCK_BUFFERS and/or VLM_BUFFER_MEMORY
until the database is able to start up.

8. Currently, there is a limitation in Server Manager for NT whereby the
amount of database buffers displayed during database startup is incorrect
if more than 4GB of buffers are in use. For instance, if 5GB of buffers
are used, Server Manager will incorrectly report that 1GB are being used.
This limitation will be fixed in a future release of Oracle.

The support for ESMA can be used with the 4GT tuning feature of Windows NT
Server, Enterprise Edition, as long as the appropriate advice in section 3.d
above is followed.


4. Database per session memory allocations
==========================================

a) Database session parameters
------------------------------

All memory allocations made by an Oracle instance are limited by the process
address space as described in section 2.a. This means users sessions have
access to the portion of the 2GB address space that is left after global
allocations are complete. For systems with large user populations a compromise
must be made between an appropriately sized SGA and the ability to fit all the
required users into the limited address space.

The following list defines the main init.ora parameters that cause memory
to be allocated within the oracle process by each users session :

- bitmap_merge_area_size
- create_bitmap_area_size
- hash_area_size
- open_cursors
- sort_area_size

Limiting the size of the above parameters will assist in achieving a balance
between the size of the SGA and the size of users per session memory
allocations. Heavy use of PL/SQL constructs (such as PL/SQL tables) can also
significantly contribute to user session memory.

b) Database sessions and Windows NT threads
-------------------------------------------

Whenever a thread is created in a process the system reserves a region of the
address space for the thread's stack (each thread has it's own stack) and it
also commits some physical storage to this reserved region. By default, the
system reserves 1Mb of the address space and commits the top two pages of the
region. This can be changed by using the /STACK:reserve[.commit] linker flag
or by techniques similar to those described in the following section, the
Oracle process has been linked in the default way. For more information on
the default behaviour refer to the "Threads and Memory allocations" section
of [NOTE:46053.1].

When the Listener creates a users session on behalf of a connection it creates
a thread in the Oracle process to service this connection, thus each users
connection has its own thread in the Oracle process. In general, the Oracle
process will commit around 300K of the 1Mb stack under normal operation, but
because the remaining 700K is reserved no other session / thread can use this
memory. The main reason the Oracle process uses the default stack setting is
because regression testing has shown that applications that perform highly
recursive operations (such as nested triggers) can allocate a stack in excess
of 650K.

c) Altering the way Oracle creates per session memory structures
----------------------------------------------------------------

The per session memory allocations listed in section 4.a do not come from
the threads stack, section 4.d describes how they are separately allocated
from the address space. Keeping parameters such as SORT_AREA_SIZE to a minimum
is the easiest way to minimize per session memory allocations, the only other
alternative is to alter the default stack size.

Oracle supplies the ORASTACK utility to allow customers to modify the default
stack size of a thread / session when created in the Oracle executable. When
ORASTACK is run against specific executables it alters the part of the binary
header that defines the default stack size used by the create thread API. It
is not necessary to change the default number of pages committed by the thread
because these will be allocated as required from the stack. By reducing the
stack of every session created in the Oracle executable, it is possible to
achieve a larger user population. In a system with a 1000 users reducing the
stack from 1Mb to 500K would release 500Mb of the address space for other
allocations or more users.

Customers should thoroughly test their applications against databases that
have the stack trimmed to less than 1Mb before using the new stack size in
their production systems. If the Oracle stack has been trimmed below the size
required by the Oracle server side code a stack overrun will occur and the
users session will fail with a ORA-3113, there will be no trace files or
entries in the <sid>CORE.LOG file. Oracle Support Services does not recommend
that customers trim the stack below 500K, although a number of systems run
without error on a 300K stack.

ORASTACK must be run against all processes that can create a thread in the
Oracle executable, use the following syntax :

orastack executable_name new_stack_size_in_bytes

Below are examples of setting the stack to 500K for the main executables :

orastack oracle.exe 500000
orastack tnslsnr.exe 500000
orastack svrmgrl.exe 500000
orastack sqlplus.exe 500000

In addition, if there are programs on the server machine which connect to the
database locally [without SQL*NET], run orastack on those as well.

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

Once the listener has created a users session (thread and stack) the Oracle
server code takes over and starts to create the necessary memory structures
for the users connection. These allocations and those required by parameters
described in section 4.a (which are created as and when the session requires
them) are created using the Win32 API call "VirtualAlloc". Each session makes
calls to "VirtualAlloc" with the MEM_RESERVE | MEM_COMMIT allocation flags,
which both reserves and commits the region of the address space for its
private use. For information on how memory is released see section 4.e.

When "VirtualAlloc" is called to reserve memory at a specific address it
returns an address that has been rounded down to the next 64K chunk in the
virtual address space. Oracle calls "VirtualAlloc" without defining a specific
address, by passing in NULL, it is returned the address of the next available
64K chunk. So when a users session makes a PGA, UGA or CGA allocation it is
also aligned along the 64K boundaries, committing the requested pages from
this boundary up. Users sessions often make many small allocations which are
less than 64K, this causes address space fragmentation because many 64K
regions exist with only a couple of pages committed.

The following example shows what the address space would look like after the
initial allocations have been made by a users session :

Address State Region Size (Bytes)
------- ------ -------------------
B290000 Stack 1,048,576
B390000 Commit 4096
B391000 Free 61440
B3A0000 Commit 4096
B3A1000 Free 61440
B3B0000 Commit 4096
B3B1000 Free 61440
B3C0000 Commit 65536
B3D0000 Commit 4096
B3D1000 Free 61440
B3E0000 Commit 4096
B3E1000 Free 61440
B3F0000 Commit 65536
B400000 Commit 65536
B410000 Commit 65536

The following table can be used as a guideline for calculating the minimum
amount of the address space that will be used by a users session. It does not
take into account the memory that could be allocated by parameters described
in section 4.a as the session proceeds. The default 1Mb stack is also assumed.

Version Minimum Memory
------- --------------
7.3.4 1.38MB
8.0.6 1.56MB
8.1.7 1.56MB

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.


Momosa

Bonjour Momosa,
Tout d'abord, merci de la réponse.
J'avais déjà lu cette note sur MetaLink (46001.1).
Le problème vient du fait qu'il y a plus de threads associés au process oracle.exe au niveau OS que dans la vue v$process.
J'en conclus qu'il s'agit probablement de sessions orphelines qui n'ont pas été libérées.
Je viens d'incrementer dans le fichier sqlnet.ora (côté serveur) la ligne SQLNET.EXPIRE_TIME = 5, on verra si ça limite ce phénomène.
Sinon, j'envisage d'utiliser orastack sur le fichier tnslsnr.exe afin de limiter la quantité de Ko allouée à la pile de chaque process. Quelles sont les valeurs conseillées ? 700Ko ? 500 Ko ?
Merci de vos conseils.

le problème sur win 32 bit quel que soit la version Windows est l 'unicité du process oracle.exe .... pour tout ce qui est lancé dans oracle
et ... que un seul process ne peut dépasser 4Go

bon t'as lu tous les notes metalink concernant win32 et 2go ?


je te conseille dans ton serveur de test , :wink:
de diminuer donc les valeurs soit du pga soit du sga

voir aussi session , parallel


effectivement , soit au niveau applicatif , ou au niveau oracle, desactiver
les utilisateurs non actifs


Pour orastack , les exemples sont donnés à 500ko
et tu peux faire pour sqlplus, tnslsnr, oracle

au pire mise en place de mts ,comme le suggere aussi metalink ....

apres ... win64 ??? voire autre os


Momosa