| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> NT-Oracle memory, page file, ...
Windows 2000 Oracle product line:
http://metalink.oracle.com/metalink/plsql/ml2_documents.showDocument?p_database_id=NOT&p_id=111040.1 -
http://technet.oracle.com/tech/nt/rdbms/rdbmswin.htm
---excerpt---
Oracle8i on Windows NT/2000: Architecture, Scalability, and Tuning
April, 2000
Introduction
As the adoption of Windows continues to progress rapidly, Oracle8i and Oracle8i Release 2 have become the market leading database for the Windows NT/2000 platform. From the outset, Oracle's goal has always been to provide the highest performing and most tightly integrated database on Windows. Oracle invested early to move its market leading UNIX database technology to the Windows platform. In 1993, Oracle was the first company to provide a database for Windows NT.
Initially, Oracle's development efforts were concentrated on improving the performance and optimizing the database architecture on Windows. Oracle7 on Windows NT was re-designed to take advantage of several features unique to the Windows platform including native thread support and integration with some of the Windows NT administrative tools such as Performance Monitor and the Event Viewer.
However, Oracle on Windows has evolved from the basic level of operating system integration to utilize some of the more advanced services in the Windows operating system, including Very Large Memory (VLM) support and 64-bit Windows 2000 support. Oracle is continuing to innovate and leverage new Windows technologies. The following paragraphs discuss the Oracle8i database architecture, scalability topics, and tuning procedures.
...
---end---
http://metalink.oracle.com (search "NT memory")
Doc ID: Note:46001.1
Subject: Oracle Database and the Windows NT memory
architecture, Technical Bulletin
...
Creation Date: 14-JAN-1998
Last Revision Date: 05-APR-2001
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
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.
...
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.
...
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 :
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 :
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 :
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
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.
...
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
...
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.
...
4. Database per session memory allocations
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 :
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
...
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 :
Other possible errors include :
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
...
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 :
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 :
Oracle Support Services.
Copyright (c) 1995,2000 Oracle Corporation. All Rights Reserved. Legal Notices and Terms of Use.
(end)
Doc ID: Note:46053.1
Subject: Windows NT Memory Architecture Overview
Creation Date: 06-JUL-1997
Last Revision Date: 17-APR-2001
This article is intended to assist customers understand how the Windows NT memory architecture works and this should help them better understand how the Oracle database interacts with it when used in combination with article [NOTE:46001.1]. It is not intended to be a definitive guide to the Windows NT memory architecture, please refer to Intel / Microsoft's own information for this.
This note is only relevant to Windows NT 4.0, Windows 2000 includes many new features not addressed here.
...
Intel has introduced Servers based on the Pentium II/III Xeon processor with support for the Intel Extended Server Memory (ESM) Architecture which breaks through the 4GB (32-bit) memory barrier. ESM includes 36-bit memory addressing technologies which are capable of addressing 64GB of main memory, using the Page Size Extension 36-bit (PSE36) driver, which must be obtained from Intel. The current PSE36 driver is limited to 8GB.
The Intel PSE36 driver is a standard RAM disk device (based on the Windows NT DDK RAM disk driver) that lacks a file system and is backed by main memory that is unused by the operating system. The PSE36 driver functions like a raw disk with much lower latency and allows 4MB pages to exist at addresses anywhere in the 36-bit address space. Applications must be rewritten to make use of this feature.
Only one process may open / access the PSE36 driver at a time, this process gets exclusive access to all of the additional memory. The RAM disk is not shared between processes, it is never mapped into the address space of a process and it is not backed by the Windows NT page file. Applications that use this device driver access it via the same Win32 API function calls used to access standard raw disk partitions :
Systems with less than 4GB of memory can still utilize the PSE36 driver as long as the /MAXMEM switch is added to the Windows NT boot.ini file. For example on a system with 4GB of memory and a Xeon processor MAXMEM could be set to 2048 MB :
multi(0)disk(0)rdisk(0)partition(2)\WINNT="Windows NT 4.0 EE" /MAXMEM:2048
Under such a configuration, assuming 256MB of address space at the top of memory has been reserved for I/O devices, Windows NT would control a 2GB chunk of memory and 1.75GB would be controlled by the PSE36 driver. For systems with greater than 4GB of physical memory the MAXMEM parameter can be used to maximize the amount of memory used by the PSE36 driver which is useful in systems where processes have only modest kernel memory requirements. For example on a machine with 5GB of physical memory, MAXMEM could be set to 3GB (3072) to increase the memory available to the PSE36 driver from 1GB to 2GB. Although it is often unnecessary to set MAXMEM on such systems because Windows NT in unable to access memory beyond 4GB.
...
e) Caching Files
Windows NT Server is commonly used as a network file server, to provide better response times to applications accessing common files across the network and to programs that are I/O intensive NT implements a file system cache. The size of the Windows NT file system cache is continually adjusted by the VMM based upon the size of physical memory and the demand for memory space.
The cache is designed to be self-tuning but can be influenced by selecting:
For systems that mainly act as a file server set optimisation to :
For systems that have applications that are accessed via client / server architectures and often perform their own file caching such as database servers set optimisation to :
...
Oracle Support Services .
Copyright (c) 1995,2000 Oracle Corporation. All Rights Reserved. Legal Notices and Terms of Use.
(end)
Doc ID: Note:45967.1
Subject: 20 Differences Between Oracle on NT and Oracle on
Unix
Creation Da 02-JUL-1997
20 DIFFERENCES BETWEEN ORACLE ON WINDOWS NT AND ORACLE ON UNIX
...
With Oracle7 and 8.0.x on Windows NT, most Oracle executables and hence processes had a two-digit version number appended to the name, to allow multiple versions to be installed into the single ORACLE_HOME. With the introduction of multiple ORACLE_HOMEs on NT in release 8.0.4, this was no longer necessary, and as of 8.1.5 the UNIX style of using just the name has been adopted.
With multiple Oracle instances running on Windows NT, there will be one ORACLE process per instance, each with multiple component threads.
...
20. RELINKING Oracle on Windows NT is supplied as a set of executables and dynamic link libraries (DLLs). Relinking by the user is not possible on Windows NT, but executable images can be modified using the ORASTACK utility, to change the size of the stack used by the threads of the Oracle server process. This can be useful to avoid running out of virtual memory when using a very large SGA, or with thousands of connections. It is recommended that this tool should be used under the guidance of Oracle Support.
...
(end)
Oracle8i Enterprise Edition
Release Notes
Release 3 (8.1.7) for Windows NT
November 16, 2000
Part No. A85305-01
...
Oracle8i Support for Very Large Memory (VLM) Configurations
A new feature in Oracle8i for Windows NT is support for Very Large Memory (VLM) configurations, which allows Oracle8i to access more than the 4 gigabyte (GB) of RAM traditionally available to Windows NT applications.
Note:
This feature is only available on Intel Pentium II and Pentium III Xeon 32-bit processor.
Specifically, Oracle8i Enterprise Edition on Windows NT 4.0 (in conjunction with Intel's PSE36 driver) can now allocate substantially more database buffers than previous releases. Further details are posted at:
http://www.intel.com/ebusiness/server/resources/pentiumii/xeon/esma.pdf
On Windows 2000, Oracle8i uses the Address Windowing Extensions (AWE) built into the operating system to access more than 4 GB of RAM. For more information, see the Microsoft Web site:
http://www.microsoft.com/WINDOWS2000/news/fromms/intelpae.asp
To take advantage of this support, you must do the following:
1.More than 4 GB of RAM must be present in the server on which Oracle8i runs.
2.On Windows NT 4.0:
Service Pack 3 or later must be installed.
The Intel PSE36 driver must be installed and operational. See
http://support.intel.com/support/performancetools/pse36/
for further PSE36 system requirements and for download instructions.
3. On Windows 2000, the user account under which Oracle8i runs (typically the local SYSTEM account), has the "Lock memory pages" Windows 2000 privilege.
4.USE_INDIRECT_DATA_BUFFERS=TRUE must be present in the INIT.ORA file for the database instance that uses the VLM support. If this parameter is not set, then Oracle8i behaves in exactly the same way as previous releases.
5. Set the INIT.ORA parameters DB_BLOCK_BUFFERS and DB_BLOCK_SIZE as desired for the database. Note that the total number of bytes of database buffers (that is, DB_BLOCK_BUFFERS multiplied by DB_BLOCK_SIZE) is no longer limited to 3 GB, as was the case in previous releases.
6.The VLM_BUFFER_MEMORY (for Windows NT 4.0) or AWE_WINDOW_MEMORY (for Windows 2000) registry parameter must be created and set in the appropriate key for your Oracle home in the Windows NT registry. This parameter is specified in bytes and has a default of 1 GB. When using Windows NT 4.0, this parameter tells Oracle8i how much non-PSE36 memory to use for database buffers. When using Windows 2000, this parameter tells Oracle8i how much of its 3 GB address space to reserve for mapping in database buffers. For both implementations, this memory comes from Oracle8i's 3 GB virtual address space, so its value must be less than 3 GB. Setting this parameter to a large value has the effect of using more of Oracle8i's address space for buffers and using less PSE36 or AWE memory for buffers. However, since accessing PSE36 or AWE buffers is somewhat slower than accessing virtual address space buffers, tune this parameter to be as large as possible without adversely limiting database operations.
For example, assume that the Oracle8i database is running on an Windows NT 4.0 computer with 8 GB of RAM, which means that the PSE36 driver has control of 4 GB of RAM. If DB_BLOCK_BUFFERS=2500000 and DB_BLOCK_SIZE=2048, then a total of 5 GB of database buffers needs to be allocated. If VLM_BUFFER_MEMORY is set to 1 GB, then 1 GB of buffers come from the Oracle8i virtual address space and 4 GB come from the PSE36 driver. If you set VLM_BUFFER_MEMORY to 500 MB, an error occurs at startup because there is not 4.5 GB of memory available to the PSE36 driver for database buffers. Likewise, if you set VLM_BUFFER_MEMORY to 3 GB, an error occurs because the Oracle8i address space is limited to 3 GB on Windows NT, and this address space must also hold Oracle8i code, shared pool, PGA memory, and other structures.
In general, the higher the VLM_BUFFER_MEMORY or AWE_WINDOW_MEMORY is set, the fewer connections and memory allocations are possible for Oracle8i. The lower VLM_BUFFER_MEMORY or AWE_WINDOW_MEMORY is set, the lower the performance.
7.After these parameters are set, the Oracle8i database can be started and 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:
PSE36 driver is installed and functional
DB_BLOCK_BUFFERS is not set too high for the amount of memory in the computer. 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 Oracle8i's 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.
VLM_BUFFER_MEMORY is not set too high for the amount of address space available to Oracle8i. In Windows NT's Performance Monitor, under the Process object, monitor the Virtual Bytes counter for the "ORACLE" process. If this counter approaches 3 GB, 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.
Currently, there is a limitation in SQL*Plus for Windows NT whereby the amount of database buffers displayed during database startup is incorrect if more than 4 GB of buffers are in use. For instance, if 5 GB of buffers are used, SQL*Plus incorrectly reports that 1 GB is being used. This limitation will be fixed in the next release of Oracle8i.
...
(end)
-- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Eric D. Pierce INET: PierceED_at_csus.edu Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists -------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).Received on Wed May 30 2001 - 19:46:03 CDT
 |
 |