TUNING WINDOWS NT FOR ORACLE
Traditionally, Oracle DBAs on UNIX platforms have left the tuning of UNIX to
the system administrators. This does not hold true for Windows NT because NT is
simpler to administer than UNIX and also databases installed on NT tend to be
smaller than those on UNIX and as a result usually the same person tends to do
both DBA and system admin work on Windows NT.
Windows NT can be tuned to improve the performance of Oracle using the
following strategies:
- Choose 'Maximum throughput for network applications' in the control panel
- Oracle server uses its own memory management for caching file and network I/O
therefore optimize the server memory by changing the relationship of memory
allocated to network connections compared to that allocated to applications.
- Disable services not needed. List of services that can be disabled include
License logging service, plug and play, remote access autodial manager,
remote access connection manager, remote access server, telephony service.
Services that should not be disabled are Alerter, Browser, Eventlog,
Messenger, OracleServiceXXXX, OracleTNSListener, Server, Spooler and
Workstation.
- ORACLE_PRIORITY
?
Deploying, Managing, and Administering the Oracle Internet Platform
Paper #260 / Page 10
- DO NOT CHANGE this from its default. The correct setting is
CLASS:normal;DEF:normal. Oracle allows the base priority for its threads to be
set using the above parameter in the registry. The base priority is the lowest
priority at which any of a process's threads will run. NT schedules threads by
priority and dynamically adjusts the thread priorities in a range associated
with the base priority of its process to ensure that no one thread can dominate
the CPU. When a thread runs, it runs either until completion or until its time
expires, at which point a context-switch occurs and a new thread gets
scheduled. By default, Oracle and its background threads run in the normal
priority class. Normal priority ranges from 1 to 15 while real-time priority
ranges from 16 to 31.Setting ORACLE_PRIORITY too high can result in an unstable
system since lower priority threads may not get enough CPU.
The following guidelines are used to change the thread priorities:
- -Threads waiting for input receive a priority boost
- -Threads that complete a voluntary wait receive a priority boost
- -Threads receive a priority boost periodically
- -Compute-bound threads get their priority reduced
There are four major system resources that can contribute to contention and
performance problems:
- System memory
- Memory limitations can cause paging and swapping which can reduce
performance. Windows NT system cache has buffers for disk I/O. On a 64MB
system, about 8MB is allocated to the system cache. Oracle server uses it
own buffering scheme and bypasses the system cache and therefore performance
can be increased by setting LargeSystemCache parameter in the
\HKEY_LOCAL_MACHINE\Control\SessionManager\MemoryManagement to zero.
- CPU
- Several concurrent applications can contend for the CPU. In a multi-processor
system, if the %processor_time is greater than 80% consistently it indicates
that the CPU is a bottleneck. If the application is CPU bound and the
%USER_TIME is very high then the application design should be revisited.
Another counter that can indicate that the CPU is a bottleneck is the
performance monitor counter Processor: interrupts/sec and System: Processor
queue length which indicate if the processor is spending too much time
servicing interrupts or if there are too many threads waiting for the processor
respectively.
- Disk I/O
?
Deploying, Managing, and Administering the Oracle Internet Platform
Paper #260 / Page 11
- Simultaneous read/write to disk by multiple users can cause performance
problems. The following queries can be used to determine if I/O is spread
evenly across multiple disk drives:
SELECT NAME, PHYRDS, PHYWRTS, PHYRDS + PHYWRTS "TOTAL I/O"
FROM V$DATAFILE DF, V$FILESTAT FS
WHERE FS.FILE# = DF.FILE#
The output of this query would look like this:
NAME PHYRDS PHYWRTS TOTAL I/O
======================== ======== ========= ========
C:\ORANT\DATABASE\SYS1ORCL.ORA 2139 102 2241
C:\ORANT\DATABASE\USR1ORCL.ORA 32909 763 33672
C:\ORANT\DATABASE\RBS1ORCL.ORA 35 26 61
As seen from above, the USR1ORCL.ORA file is used heavily and could be moved to
a different disk to balance the I/O.
The following suggestions can be used to minimize I/O problems:
- Make sure that the memory is not a bottleneck anymore
- Keep Oracle online redo logs on separate, nonstriped disks away from the data
files.
- Archived redo log files can be placed on striped disks
- Use as many disk drives and disk controllers as possible and distribute the
I/O across multiple spindles
- Upgrade the hardware from IDE disks to SCSI and from SCSI to fast/wide SCSI
- Network
Collisions can occur as a result of exceeding network bandwidth.
APPLY THE LATEST SERVICE PACK FROM MICROSOFT
Microsoft is committed to releasing OS patches called Service Packs on a
quarterly basis. These service packs are cumulative collection of bug fixes and
product enhancement to the base Windows NT release.
Contention is the result of multiple processes trying to access the same
resource simultaneously, which can lead to some processes waiting for access to
the database structures. Strategies that are used to identify and reduce the
contention for structures such as Rollback Segments, Shared Servers,
Dispatchers, Parallel Servers, Redo Log Buffer Latches, Database Buffer Cache
LRU Latches, Library Cache latches and Free Lists on UNIX platforms can also be
used on Windows NT.
WINDOWS NT PAGING
Windows NT uses virtual memory to provide more memory to applications than is
physically available. A page is the smallest amount of memory that Windows NT
can work with for I/O purposes. By default, the page size for Intel-based NT
systems is 4K and for Alpha-based NT systems is 8K. The NT performance monitor
can be used to monitor page-outs (which occur when inactive pages are moved out
of memory to the paging file on disk) and page- ins (which occur when pages
that are needed by an application are moved from the paging file into the
memory).
Your goal should be to minimize these page faults -- page-ins and page-outs.
Unless you have an extremely large amount of memory, paging will be quite
common. Setting the SGA size very large can lead to excessive paging. When
sizing the SGA you should consider the memory used by all the other non- Oracle
components as well as Windows NT. The physical memory should be greater than
the sum of all memory used by these components. The Oracle8 initialization
parameter PRE_PAGE_SGA when set to TRUE can be used to indicate to Windows NT
to keep the pages that constitute the SGA in memory as much as possible and
avoid writing them to the paging file unless absolutely necessary. Working set
is the total number of pages that a program needs in physical memory when it is
running. A soft page fault occurs when the program needs a page that is found
elsewhere in memory while a hard page fault occurs when the required page is
obtained from the disk. Hard page faults cause the most performance problems
and should be minimized. You can detect hard page faults by comparing the
?
Deploying, Managing, and Administering the Oracle Internet Platform
Paper #260 / Page 12
counters Memory: page faults/sec and Memory: pages input/ second simultaneously
in the performance monitor. A memory bottleneck is indicated by more than 5
hard page faults/sec consistently. The Task Manager can be used to identify the
particular process that is causing the page faults. There are several
suggestions that can be tried to reduce the hard page faults:
- Make sure that the entire SGA fits in physical memory
- Stop unnecessary services
- Remove any network protocols that are not being used
- Make the paging file larger
- Add more memory
SUMMARY
When running Oracle8i on Windows NT, several strategies can be used by Oracle
DBAs and system administrators in order to simplify their day-to-day tasks.
Thorough analysis of your system usage should be performed so that you can
properly configure Windows NT and Oracle8i to get the maximum gain. Quest
software provides several monitoring products such as Instance Monitor, I/Watch
and Instance Expert that can be used to identify bottlenecks and tune the
system performance. However, it should be kept in mind that the greatest
benefit can be obtained by tuning the application prior to performing database
tuning. This paper also identifies various ways in which the administration of
Oracle can be integrated with that of Windows NT.
Meghraj T. Thakkar, Sr. Software Engineer, Quest Software,
[EMAIL PROTECTED]
REFERENCES
- Some parts of this paper have been extracted from the book
"SAMS Teach Yourself Oracle8i on Windows NT in 24 hours" by Megh Thakkar
- Oracle Manuals
-- End --
[end 5 of x]
