Good Paper 8i/NT/Windows 2000

Thx
Gautam

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 providethe 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.

Oracle8i Architecture on Windows

When running on Windows, Oracle8i contains the same features and functionality as it does on the various UNIX platforms that Oracle supports. However, the interfaces between Oracle8i and the operating system have
been substantially modified to take advantage of the unique services provided
by Windows. As a result, Oracle8i on Windows is not a straightforward port of the UNIX code base. Significant engineering work has been done to make sure that Oracle8i exploits Windows to the fullest and also to guarantee that
Oracle8i is a stable, reliable, and high performing system upon which to build applications.

Thread Model

By far, the most significant architectural change in Oracle8i on Windows is the conversion from a process-based server to a thread-based server. On UNIX, Oracle uses processes to implement background tasks such as database writer (DBWR), log writer (LGWR), MTS dispatchers, MTS shared servers
and the like. In addition, each dedicated connection made to the database causes another operating system process to be spawned on behalf of that session. On Windows, however, all of these processes are implemented as threads
inside a single, large process. What this means is that for each Oracle database
instance or SID, there is only one process running on Windows for the Oracle8i server itself. Inside that process will be many running threads with each thread corresponding directly to a process in the UNIX architecture. So, if there were 100 Oracle processes running on UNIX for a particular instance, that same workload would be handled by 100 threads in one process on Windows.

Operationally, client applications connecting to the database are unaffected by this change in database architecture. Every effort has been made to
ensure that the database operates in the same way on Windows as it does on other platforms, even though the internal process architecture has been converted to a thread-based approach.

The original motivation to move to a thread architecture had to do with performance issues with the first release of Windows NT when dealing with files shared among processes. By simply converting to a thread architecture and modifying no other code, performance was dramatically increased as
the particular operating system bottleneck was avoided. No doubt that the original motivation for the change is no longer present, however the thread architecture for Oracle remains since it has been proven to be a very stable, maintainable one. In addition, there are other benefits that arise out of the thread architecture. These include faster operating system context switches among threads (as opposed to processes); a much simpler SGA allocation routine which does not require the use of shared memory; faster spawning of new connections since threads are more quickly created than
processes; decreased memory usage since threads share more data structures than processes do; and a perception that a thread-based model is somehow
more "NT-like" than a process-based one.

There are a few negatives that come with being a thread-based architecture. One is that it is harder to identify and manipulate the threads that are
running in the database. Unlike a process based model, where terminating a process or finding statistics about a process can typically be done with operating system tools, a thread based model makes it more difficult to perform these tasks.

Another downside is the fact that all memory allocated for the SGA, PGAs, or for other purposes comes from the same 3GB address space. When large
sort areas are in use (for instance), available memory can be an issue since all foreground threads get their memory from the same 3GB pool. In a process model, each foreground process gets its own address space and therefore has more memory available for it to use. This downside will be short-lived, however, as the 64-bit version of Windows 2000 promises to provide at least 1 terabyte of address space per process, thereby removing any memory issues for the foreseeable future.

Internally, the code to implement the thread model is compact and very isolated from the main body of Oracle code. Fewer than 20 modules provide the entire infrastructure needed to implement the thread model. In addition, robustness has been added to the architecture through the use of exception handlers and also through routines used to track and de-allocate resources.

Both of these additions help allow for 24x7 operation with no downtime due to resource leaks or an ill-behaved program.

Services

In addition to being thread-based, the Oracle8i database is also not a typical Windows process. It is a Windows service, which is basically a background process that's registered with the operating system, started by Windows at boot time, and which runs under a particular security context. The conversion of Oracle into a service was necessary to allow the database to come up automatically upon system reboot, since services require no
user interaction to start. When the Oracle database service starts, there are
no typical Oracle threads running in the process. Instead, the process basically waits for the first connection from Server Manager which will cause a foreground thread to start and which will eventually cause the creation of the background threads and of the SGA. When the database is shutdown, all the threads that were created will terminate, but the process itself will continue to run and will wait for the next connection request and startup command. In addition to the Oracle database service, a second service is created, which spawns Server Manager and opens up the database for client use. Finally, the Oracle Net8 Listener is a service since it
too needs to be running before users can connect to the database. Again, all of this it basically an implementation detail that does not affect how clients connect to or otherwise use the database.

File I/O Enhancements

One other area in which much work has been done in the Oracle8i code concerns support for large files and for raw files. In an effort to guarantee that all features of Windows are fully exploited by Oracle8i, the database supports 64-bit file I/O to allow the use of files larger than 4GB in size. In addition, physical and logical raw files are supported as data, log, and control files in order to enable Oracle Parallel Server on Windows and also for those cases where performance needs to be maximized.

64-bit File I/O

Internally, all Oracle8i file I/O routines support 64-bit file offsets, meaning that there are no 2GB or 4GB file size limitations when it comes to data, log, or control files as is the case on some other platforms. In fact, the limitations that are in place are generic Oracle limitations across
all ports. These limits include 4 million database blocks per file, 16KB maximum block size, and 64K files per database. If these values are multiplied,
the maximum file size for a database file on Windows is calculated to be 64GB while the maximum total database size supported (with 16KB database blocks) is 4 petabytes.

Raw File Support

Like UNIX, Windows supports the concept of raw files, which are basically unformatted disk partitions that can be used as one large file. Raw
files have the benefit of no file system overhead, since they are unformatted
partitions. As a result, using raw files for database or log files can have a slight performance gain. However, the downside to using raw files is manageability since standard Windows commands do not support manipulating or backing up raw files. As a result, raw files are generally used only by very high-end installations and by Oracle Parallel Server, where they are required. To use a raw file, all Oracle needs to be told is the filename specifying which drive letter or partition to use for the file. For instance, the
filename \\.\PhysicalDrive3 tells Oracle to use the 3rd physical drive as a physical raw file as part of the database. Likewise, \\.\G: tells Oracle
to use the logical raw file that has been assigned drive letter G. In addition, a file such as \\.\log_file_1 is an example of a raw file that has been
assigned an alias for ease of understanding. Aliases can be assigned with the SETLINKS utility provided with Oracle8i. When specifying raw filenames to Oracle, care must be taken to choose the right partition number or
drive letter, as Oracle will simply overwrite anything on the drive specified
when it adds the file to the database, even if it's already an NTFS or FAT formatted drive.To Oracle, raw files are really no different from other Oracle database
files. They are treated in the same way by Oracle and can be backed up and restored via Recovery Manager as any other file can be.

Oracle8i Scalability on Windows

One of the key goals of the Oracle8i product on Windows is to fully exploit any technologies that can help increase scalability, throughput, and database capacity. The following section describes a few of these technologies, how they affect Oracle, and the benefits that can be derived from them.

4GB RAM Tuning (4GT) Support

Windows NT Server v4.0 Enterprise Edition includes a feature called 4GB RAM
Tuning (4GT). This feature allows memory-intensive applications running on
Windows NT Server Enterprise Edition to access up to 3GB of memory as opposed to the standard 2GB in previous versions of the operating system.
The obvious benefit to Oracle8i is that 50% more memory becomes available
for database use, which can increase SGA sizes or connection counts. All

Oracle database server releases since 7.3.4 have supported this feature with
no modifications necessary to a standard Oracle installation. The only configuration change required is to ensure that the /3GB flag is used in

Windows' boot.ini file. This feature is also supported in Windows 2000.

For more information on the 4GT feature, follow the following links: http://www.microsoft.com/NTServer/ntserverenterprise/exec/feature/4GBT.asp

http://www.microsoft.com/ntserver/NTServerEnterprise/exec/overview/WindowsNT EnterpriseFAQ.asp#4gig

Very Large Memory (VLM) Support

One of the key Windows-specific additions introduced in Oracle8i was support
for Very Large Memory (VLM) configurations. Specifically, Oracle8i breaks
through the 3GB address space limit imposed by Windows and allows a single
database instance access to up to 16GB of database buffers. By configuring a
database with z large amount of buffers, disk I/O activity can be diminished
since more data is cached in memory. This leads to a corresponding increase
in throughput and performance. The follow sections will describe the different VLM implementations on Windows NT v4.0 and Windows 2000.

Windows NT V4.0 and ESMA

When running on Windows NT v4.0 Enterprise Edition, Oracle8i version 8.1.4
and higher have been enhanced to support Intel's Extended Server Memory Architecture (ESMA) via the use of Intel's PSE36 device driver. This device
driver allows applications to access up to 16GB of RAM when running on Intel
Xeon processors. Specifically, Oracle8i can now make calls to PSE36 in order
to read from and write to memory not normally accessible to Windows' memory
manager. Oracle8i uses these calls to allow the use of large numbers of database buffers. By merely increasing the value of the db_block_buffers

initialization parameter and setting one other initialization parameter and
a registry value, a database instance gets instant access to much more memory than was previously possible. No database file changes are required,
nor are any database operations affected other than the increase in available database buffers.

For further information about this support and about the PSE36 device driver, follow the following links:
http://www.intel.com/procs/servers/Xeon/downloads/esma.pdf http://developer.intel.com/vtune/pse36/index.htm

Windows 2000 and AWE

With Windows 2000, Microsoft has enabled an even faster implementation of
VLM support than PSE36. Called the Address Windowing Extensions (AWE), this
support is a set of API calls that allow applications to access more than
the traditional 3GB of RAM normally accessible to Windows NT applications.
As opposed to PSE36, which was a read/write interface to the extended memory, the AWE interface takes advantage of the Intel Xeon architecture and
provides a fast map/unmap interface that avoids the expensive memory copying
done by PSE36. It is expected that the AWE interface will prove to be a faster implementation of VLM support for Oracle than PSE36 was. The Oracle8i
Release 2 (8.1.6) and higher supports the AWE interface.

In exactly the same way that PSE36 allows Oracle to access many database

buffers, so too will AWE allow a large increase in database buffer usage up
to 16GB of buffers total. Again, as with PSE36, this support is purely an
in-memory change with no changes or modifications made to the database files.

On Windows 2000 Datacenter Server, Oracle supports up to 64GB of memory.

However, Windows 2000 restricts the database address space to 2GB if you

more than 16GB of RAM exists on the machine. When running Windows 2000 on a
machine with more than 16GB of RAM installed, there is a conflict between
the 4GT feature and the use of AWE calls. Windows 2000 on machines with 16GB
or more of RAM only allows one of these features to be used at a time. If
the 4GT feature is turned in via the /3GB flag in boot.ini, then Windows

2000 will ignore any memory over and above 16GB, making it not accessible to
Oracle. If the /3GB flag is not used, then Windows 2000 can and will use all
the memory in the machine, but Oracle will get a small address space of only
2GB.

For more information on AWE, see the following: http://www.microsoft.com/Windows/server/News/fromMS/intelpae.asp http://www.microsoft.com/HWDEV/NTDRIVERS/AWE.htm

Turning on VLM

Since the PSE36 and the AWE implementations are so similar, they will both
be described here. To enable the use of many database buffers for Oracle,
perform the following steps:

1. Windows NT v4.0 Enterprise Edition, Service Pack 3 or later (for PSE36) or Windows 2000 Datacenter Server (for AWE) must be installed.
2. For PSE36, the Intel PSE36 driver must be installed and operational.
3. For AWE, verify that the user account under which Oracle runs
   (typically
   the local SYSTEM account) has the "Lock memory pages" NT privilege.
4. use_indirect_data_buffers=true must be present in the init.ora for the database instance that will use VLM. If this parameter is not set, then Oracle8i behaves in exactly the same way as previous releases.
5. Set 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 3GB as was the case in previous releases.
6. The VLM_BUFFER_MEMORY (for PSE36) or AWE_WINDOW_MEMORY (for AWE) registry parameter must be created and set in the appropriate key for the Oracle home in the Windows NT registry. This parameter is specified in bytes and has a default value of 1GB. When using PSE36, this parameter tells Oracle8i how much normal, non-PSE36 memory to use for database buffers. When using AWE, this parameter tells Oracle8i how much of its 3GB address space to reserve for mapping in database buffers. For both implementations, the value set

comes from Oracle8i's 3GB virtual address space, so its value must be less
than 3GB. 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.

7. Once these parameters are set, the Oracle8i database can be started up
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:

1. For PSE36, the PSE36 driver is installed and functional
2. 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 from 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 or AWE_WINDOW_MEMORY.
3. VLM_BUFFER_MEMORY or AWE_WINDOW_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 3GB, then out of memory errors can occur. If this happens, reduce db_block_buffers and/or VLM_BUFFER_MEMORY or AWE_WINDOW_MEMORY until the database is able to start up.
4. 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 is fixed in Oracle8i Release 2.

Tuning Considerations

There are a few tuning considerations to deal with when using these VLM implementations. The first and most important is that Oracle is still limited to 3GB of address space in which it must put the buffer headers for
the database buffers, the rest of the SGA, PGAs, stacks for all the threads,
code, and other memory allocations. Setting db_block_size to a large value
causes many database buffer headers to be allocated from Oracle8i's address
space. Likewise, setting VLM_BUFFER_MEMORY or AWE_WINDOW_MEMORY high again
uses a lot of address space. Using too much address space for these structures has the effect of limiting how many connections can be made to
the database or limiting other memory allocations needed to perform database
operations. Lowering them will sacrifice some performance since fewer buffers are quickly accessible in the normal Oracle8i address space. If out
of memory errors occur when running with VLM, one or the other of the above
parameters will need to be reduced.

One final observation on the use of VLM is that it is useful even when running on a machine with only 4GB of RAM installed. On such a machine, Oracle is normally limited to 3GB of memory, while Windows and other applications on the system use the remaining 1GB. However, Windows does not
need 1GB of physical RAM to perform its tasks. Instead, there are typically
several hundred megabytes of RAM available and not being used in a typical
installation. On Windows 2000, turning on the AWE support allows Oracle to
access perhaps an extra 500MB of buffers just by bumping up db_block_buffers
and setting AWE_WINDOW_MEMORY appropriately. Likewise, on Windows NT v4.0
Enterprise Edition, the /maxmem flag in boot.ini can limit NT's memory usage
to 3.5 GB total and allow the PSE36 driver access to the remaining 500MB.
Turning on the PSE36 support in Oracle will then allow the database access
to the extra 500MB of buffers.

Large User Populations

One area in which much activity has been undertaken is an effort to support
large numbers of connected database users on Windows. As far back as Oracle7
version 7.2, there have been customers in production with over 1000 concurrent connections to a single database instance on Windows. As time has
progressed, that number has increased to a point where a recent 3rd party
benchmark connected over 2200 users concurrently to the database. When using
the Oracle Multi-threaded Server architecture, which limits the number of
threads running in the Oracle database process, over 10,000 simultaneous

connections have been accomplished to a single database instance. In addition, the Net8 multiplexing and connection pooling features can also

allow a large configuration to achieve more connected users to a single database instance. Finally, Oracle Parallel Server can be used to again increase connection counts dramatically by allowing multiple server machines
access to the same database files, thereby increasing capacity to tens of
thousands of user connections and at the same time increasing throughput as
well.

64-bit Support

The next leap in Oracle8i performance and scalability on Windows will happen
when a 64-bit version of Oracle8i is released on the upcoming Intel Itanium
(Merced) processor and the corresponding 64-bit version of Windows 2000.
The
development teams at Oracle have been working closely with these technology
vendors to guarantee that Oracle8i on Windows will be released in production
form very shortly after the hardware and operating system are generally available. As with other Oracle 64-bit ports to different UNIX variants, Received on Tue Jan 23 2001 - 14:30:10 CST