Oracle Database and the Windows NT memory architecture

Subject:	Oracle Database and the Windows NT memory architecture, Technical Bulletin
	Doc ID:	Note:46001.1	Type:	BULLETIN
	Last Revision Date:	18-APR-2006	Status:	PUBLISHED

1. Purpose

==========

This note explains how Oracle interacts with Windows NT's memory architecture,

see "Note 46053.1 Windows NT Memory Architecture Overview", for additional

information about NT's memory architecture.

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 = 

 -  - 

  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 (sort_area_retained_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 users

sessions will fail (usually with an ORA-3113), there will be no trace files or

entries in the 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

Before running ORASTACK ensure no instances of the process are running

in the Windows task manager under the Processes tab.

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.