| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> Re: NT Server very slow
> I have an Oracle 8.1.6 instance on an NT Server. The application has only
a
> few users. Very often throughout the day it seems response time drops to
> about 20 seconds. By the time I log on to look at the problem everything
is
> back to normal. If I do a simple query the result is returned
immediately.
> I have looked at SQL through Quest's SQLAB and there is nothing that stand > out. The hit ratio averages between 88% and 92%. Is there a good way to > diagnose this type of problem? >
Once I experienced simillar periods of mysterious slowdown and it turned out that someone has configured a 3D screen saver on the server
I'm attaching part of a paper for tuning Oracle on NT that was posted few months ago
hth,
Marin
Herman Hesse, "Siddhartha"
==============================================================
Oracle8i Tuning on Windows
Included in the following sections are advice and guidelines for tuning
Oracle8i on Windows from a hardware, operating system, and Oracle
software
perspective. Generic Oracle8i tuning advice (such as database structure
layout and SQL statement tuning) which is applicable to all platforms on
which Oracle8i runs is not included, since this is already covered extensively in the Oracle8i documentation library.
Hardware
As they say, "Bigger is better." This certainly applies when selecting
hardware for an Oracle database server, since Oracle can typically use
all
resources provided to it, as needed by the particular application in
question. However, not all applications require an extremely large
server to
achieve reasonable response times and performance characteristics.
Included
in the following sections are some very general guidelines for choosing
or
configuring specific hardware components.
CPU An Oracle database is a particularly memory-intensive application that heavily exercises the system bus with memory accesses, especially in the
case where a large SGA is in use. As a result, the CPU to memory
interface
is a crucial part of system performance when dealing with Oracle8i and
in
some cases is more important than raw CPU speed. Choosing the largest
available level 2 (L2) data cache and the fastest system bus available
is
typically a very important consideration when selecting a system. CPU
speed
obviously is also an important factor, but it is sometimes the case that
extra CPU speed will not result in a proportional increase in throughput
if
the system bus is a limiting factor in the configuration. In an SMP
configuration in which multiple CPUs are used, this effect is amplified
since there can be 2, 4 or 8 CPUs performing memory operations on the
same
system bus simultaneously. Having a large L2 cache can reduce the number
of
bus transactions required and can help achieve better scalability and
throughput.
Memory
With the introduction of VLM support as described earlier, Oracle8i on
Windows NT v4.0 Server Enterprise Edition and Windows 2000 Datacenter
Server
can now utilize up to 16GB of RAM for database buffers. (For Windows
2000
Server and Advanced Server, Oracle can support 4GB and 8GB of RAM for
database buffers, respectively, the maximum amount of memory allowed on
these systems.) As a result, memory sizing is no longer limited to 4GB,
even
though this number still implies a very high end system. At this point,
the
number of applications running on Windows that need 16GB of database
buffers
are very few indeed as most applications that need to scale this high
typically are hosted on UNIX servers. With the release of 8-way SMP
servers
that run Windows, however, the number of large applications moving to
Windows will increase and 16GB of RAM will become a more common
configuration.
For smaller installations, memory should obviously be sized such that no
paging (or minimal paging) occurs during normal database operations.
Determining the right amount of memory ahead of time is extremely
difficult
since an application often needs to be up and running before it's
possible
to determine the minimum required memory usage and SGA settings needed
to
achieve acceptable performance.
Disk Subsystem
Besides the CPU and memory, the disk subsystem is another crucial
component
of an Oracle8i server machine. This is particularly true for
transaction-intensive applications that post many changes to the
database.
The following tips come from a relevant article in Microsoft's Knowledge
Base (http://support.microsoft.com/support/kb/articles/Q199/1/60.ASP):
When selecting a disk subsystem, these general rules should be followed:
Operating System Tuning
Compared to UNIX, Windows offers considerably fewer user-configurable
parameters that can be adjusted to tune the operating system. This
reduces
the ability of system administrators to optimize Windows performance,
but
helps to make Windows easier to use than some operating systems.
There are still ways, however, to make Windows a better application
server
environment for the Oracle8i database. Most of these Windows-specific
procedures have the effect of reserving more system resources for the
Oracle8i database (for example, CPU, memory, and I/O bandwidth). These
procedures are described in the following sections.
Use a Dedicated Server for Oracle
In general, the Windows computer that is running your Oracle8i database should not serve as any of the following:
These services consume network, memory, and CPU resources. In addition,
the
Windows computer that is running your Oracle8i database should not be
locally accessed in high frequency or intensively used for local user
processing, unless there exist significant resources to accommodate all
this
activity.
Configure NT to be an Application Server, not a File Server
The memory manager for Windows defines three different pools of memory
that
are allocated from available RAM. These pools include one pool for the
operating system kernel and other system services, one pool for the file
cache, and one pool for paged memory available to user applications. By
default, a Windows server is set up to be a file and print server. As a
result, the file cache component of system memory is large in
anticipation
of file server activity. However, when running an Oracle8i database, the
file cache is not really needed at all since all Oracle file I/O
operations
bypass the file cache altogether and force data to be written directly
to
disk. This is required in order to ensure data integrity. In addition,
the
memory Oracle allocates for the SGA acts as Oracle's own private file
cache,
again making an operating system cache unnecessary.
To change the default behavior of Windows and reduce the size of the
file
cache, go to Start ® Settings ® Control Panel ® Network ® Services.
Double-click the "Server" service and select "Maximize Throughput for
Network Applications."
Reduce Priority of Foreground Applications on the Server Console
By default, the application currently in the foreground on Windows is
given
a higher priority than background processes. On a machine running an
Oracle8i database, it is not desirable for a foreground application to
have
precedence over the database process. To change the default behavior
such
that the foreground application does not receive a performance boost, go
to
Start ® Settings ® Control Panel ® System and hit the Performance tab.
Then,
select "None" as the setting for "Performance Boost for the Foreground
Application."
Remove Unused Network Protocols
Since Windows supports many network protocols, it is common to have
several
enabled in a typical installation. However, very often not all of these
protocols are required or used when running an Oracle8i database. If
there
are unnecessary protocols installed on a system, it is recommended that
they
be removed so that the operating system does not devote processing time
to
these protocols.
To remove unnecessary network protocols, go to Start ® Settings ®
Control
Panel ® Network. Click the Protocols tab. Select any unnecessary
protocols
not needed for the system's configuration and click Remove.
Reset the Network Protocol Bind Order
If there is a need to have several protocols installed on the server
machine, the ones used most by Oracle should be given priority over
those
that are not.
To reset the network protocol bind order, go to Start ® Settings ®
Control
Panel ® Network. Click the Bindings tab. Show bindings for all services,
and
then double-click Server to see the list of current protocols. Verify
that
the protocol used by Oracle is at the top of the list, and if it is not,
make it so by selecting it and moving it up until it is at the top. If
there
are multiple network cards installed in the machine, then verify that
the
card used most frequently by Oracle is at the top of the list for each
protocol as well. To do this, double click each protocol that Oracle
uses,
and move the appropriate network card to the top of the list for that
protocol.
Page File Sizing
Although excessive paging is always discouraged when trying to achieve
good
performance from an application, it is recommended that the total
combined
size of all page files in the system be at least equal to the amount of
RAM
in the computer. Many installations go beyond this amount and have
combined
page file sizes that are two or more times as large as the amount of RAM
in
the machine. Sizing page files in this way provides enough cushion to
avoid
a situation in which Windows runs out of page file space and is unable
to
successfully perform the tasks required of it. In addition, since
Windows
balances page file activity across page files, it is important to place
different page files on different physical disks in order to balance the
I/O
load on those disks.
Apply Latest Reliable Service Pack and Device Drivers
As Microsoft releases service packs for Windows, it is usually desirable
to
upgrade to these new releases, since they often fix critical bugs,
increase
operating system stability, contain performance enhancements, or even
add
new functionality or programmatic interfaces to the operating system. In
general, however, it is best to wait a few weeks after a service pack is
released before upgrading since there are frequently bugs and
incompatibilities that arise in certain instances. By waiting, these
problems can be weighed against the benefits of the service pack to
determine if upgrading is indicated. The latest Windows NT Service Packs
may
be downloaded from
http://support.microsoft.com/Support/NTServer/Content/ServicePacks/Default.a
sp.
Oracle's current support policy with regard to Windows Service Packs is
that
Oracle does not specifically certify all products against specific
Microsoft
operating system service packs, but does support the use of its products
on
any service pack when that service pack becomes generally available.
Depending upon the severity, quantity and impact of the service pack
related
issues found, Oracle may recommend that customers wait until relevant
Oracle
patches have been released before upgrading to a particular service
pack.
Oracle does not, and will not, recommend or discourage the installation
of
specific service packs unless the service packs will significantly
affect
the operation of Oracle software, either positively or negatively. If
such a
statement is deemed necessary, then Oracle will disseminate this
statement
in as timely a fashion as possible after the release of the service pack
in
question.
In addition to the occasional service pack, device drivers for a
system's
I/O controllers, network cards, and video subsystem should also be
periodically updated as patches become available. From an Oracle
perspective, the I/O device drivers are most critical and updates to
these
software pieces can increase stability and performance of an Oracle
database. When running a RAID configuration, this is even more crucial
since
experience has shown that device drivers for this type of configuration
tend
to be more complex and more prone to problems that for other setups.
Disable Unnecessary Services
Additional memory can be provided to an Oracle8i database on Windows by
disabling unnecessary Windows services. In a typical Windows
installation,
there are usually several services that are enabled by default that are
not
necessary for the current configuration. By going into Start ® Settings
®
Control Panel ® Services, the list of all running services can be found.
By
consulting the Windows documentation and the system administrator, a few
services that are running typically prove to be extraneous. By disabling
these services, more memory becomes available for Oracle.
Close All Unnecessary Foreground Applications
In addition to stopping unnecessary services, it is also advisable to
close
any non-needed foreground applications. Some of the more common places
for
optimization occur in the areas of the Startup folder, MS-DOS Command
Prompts, and screen savers.
Startup Folder
The Startup folder of Windows frequently contains applications that are
not
vital to the operation of the server upon which Oracle8i resides. These
applications use memory that otherwise could be provided to Oracle and
can
have an effect upon the operation of the database. In particular,
applications which periodically index documents, scan files, or do other
disk related activity have been shown to have a temporary detrimental
effect
upon database performance.
MS-DOS Command Prompts
MS-DOS Command Prompts can utilize a large portion of the CPU when the
command prompt window is being repainted constantly due to scrolling of
data
or commands. A common situation in which this occurs is the running of a
SQL
script in Server Manager or SQL*Plus by a database administrator. These
scripts can complete much more quickly and save CPU cycles just by
minimizing the MS-DOS Command Prompt window in which they are running.
Screen Savers
As with MS-DOS Command Prompts, screen savers can also consume
significant
CPU resources. Instead of enabling a screen saver, either lock the
workstation or turn off the monitor altogether. If a screen saver needs
to
be run, go to Start ® Settings ® Control Panel ® Display, hit the Screen
Saver tab, and select Blank Screen as the screen saver for the system.
Oracle Tuning
In addition to the hardware and the operating system, there are
Windows-specific procedures that can be performed to tune the Oracle8i
database software and its related files. These, of course, are in
addition
to generic Oracle tuning that is common to all platforms on which Oracle
runs.
Database Files
The optimal placement of database, log and control files depends upon
how
many disk spindles are present in the system, which RAID levels are
being
run, and also performance characteristics of the I/O controllers and
subsystem. Typical Oracle recommendations from the generic documentation
certainly apply to Windows and there is little Windows-specific
information
necessary with regard to the placement of database files. However, two
areas
that should be mentioned are database file fragmentation and
compression.
Fragmentation
Once created, Oracle database files do not increase or decrease in size,
unless the expandable datafile feature is turned on. As a result, no
fragmentation can occur to database files after they are created except
for
fragmentation that existed at creation time (assuming no expandable
datafiles). So, to eliminate datafile fragmentation, do the following:
utility
By following these procedures, it is guaranteed that no database file
fragmentation can occur unless the expandable datafiles feature is
turned
on. Further de-fragmentation is unnecessary, such that the database
files
can be excluded from subsequent de-fragmentation runs.
If expandable datafiles are in use, it is recommended that
de-fragmentation
only occur when the database is shut down since the inter-operation of
Oracle software and de-fragmentation utilities is not guaranteed.
Compression
Compression of database files, log files, and control files is not
supported
by Oracle since compression of database files can cause a database
instance
to abort itself in the case of a write error to the compressed file. The
technical reason for this behavior is as follows. When Oracle database
files
are first created, they are cleared out and filled with zeros. These new
files compress extremely well since there is no data in them. As a
result,
the drive on which the database files reside continues to have a lot of
free
space available on it. If that drive were to fill up with other files
before
the Oracle database file became fully populated, the database would run
into
a situation where it tried to write to the database file but failed,
since
the compressed file could not be expanded due to lack of space. Any
write or
read error to an Oracle database file is treated as a fatal error by the
database server, which causes the whole database instance to abort itself.
Affinity and Priority Settings
The Oracle8i database supports the modification of both priority and
affinity settings for the database process and individual threads in
that
process when running on Windows.
By modifying the value of the ORACLE_PRIORITY registry setting, a
database
administrator can assign different priorities to the individual
background
threads and also to the foreground threads as a whole. Likewise, the
priority of the entire Oracle process can also be modified. In certain
circumstances, this may improve performance slightly for some
applications.
For instance, if an application generates a great deal of log file
activity,
the priority of the LGWR thread can be increased to better handle the
load
put upon it. Likewise, if replication is heavily used, those threads
that
refresh data to and from remote databases can have their priority bumped
up
as well.
Much like the ORACLE_PRIORITY setting, the ORACLE_AFFINITY registry
setting
allows a database administrator to assign the entire Oracle process or
individual threads in that process to particular CPUs or groups of CPUs
in
the system. Again, in certain cases, this can help performance. For
instance, pinning DBWR to a single CPU such that it does not migrate
from
one CPU to another can in some cases provide a slight performance
improvement. Also, if there are other applications running on the
system,
using ORACLE_AFFINITY can be a way to keep Oracle confined to a subset
of
the available CPUs in order to give the other applications time to run.
Both ORACLE_PRIORITY and ORACLE_AFFINITY are described in more detail in
the
Windows-specific documentation that accompanies Oracle8i on Windows.
Orastack
As described previously in the Architecture section, each Oracle
instance is
limited to 3GB (or 2GB if not running on NT v4.0 Enterprise Edition) of
address space from which it must allocate the SGA, PGAs for all the
threads,
application code, and stacks for each thread that are used to store
variables and thread state. By default, each thread is provided with 1MB
of
reserved address space for its stack. This reserved space is not backed
by
physical memory, but is just space in Oracle's address space that is
reserved should the thread need to use 1MB of stack. In practice, 1MB is
a
very large, conservative number that is bigger than the database
typically
needs. However, it is set this way by default in order to make sure that
for
the few cases where it is needed, it is set to a sufficiently high
value.
The downside to this large setting is that 1MB of address space per
thread
adds up very quickly when 1000 or more threads are running in the Oracle
process. For 1000 threads, 1GB of address space is used just for stacks
and
the threads do not need most of this space anyway. This large address
space
usage can limit how many connections can be made to the database or how
big
the SGA can be for a given number of connections.
To give administrators some tuning flexibility, Oracle provides a
utility
called ORASTACK that enables an administrator to lower the stack size
for
Oracle threads from 1MB down to a smaller number. This allows for either
higher connection counts or a larger SGA in those cases where Oracle is
bumping up against the 3GB address space limit. If an application does
very
little highly recursive SQL or not much in the way of nested triggers or
stored procedures, then turning the stack size down to 300K (for
instance)
is a safe procedure that will save on address space usage. If the stack
space is decreased too much, typical behavior will be "ORA-03113:
end-of-file on communication channel" errors returned to the client as
its
foreground thread terminates with a stack overflow error.
To use ORASTACK, run the following commands on the server machine:
C:\> orastack c:\oracle\ora81\bin\oracle.exe 300000 C:\> orastack c:\oracle\ora81\bin\tnslsnr.exe 300000 C:\> orastack c:\oracle\ora81\bin\svrmgrl.exe 300000
The first command will cause the stack for background threads to be
300000
bytes as opposed to 1MB for all instances running on the machine. The
second
command will cause the stack for all foreground threads running on
behalf of
network clients to be 300000 bytes. The third command causes the stack
for
all foreground threads running on behalf of Server Manager on the server
machine to be 300000 bytes. If there are other executables that run on
the
server machine that connect to the database, ORASTACK can be run on
those
executables as well to modify the stack sizes of their corresponding
foreground threads.
Operationally, all that ORASTACK does is modify part of the executable header in the .exe file to reflect the new stack specified by the user.
In general, ORASTACK is only called for in high-end installations where
there are several hundred or more connections to the database or when
the
SGA is very large (over 2GB in size). To determine how much of the 3GB
address space is in use by Oracle, run Windows Performance Monitor by
going
to Start ® Programs ® Administrative Tools ® Performance Monitor. Once
in
Performance Monitor, choose Edit ® Add to Chart... and select the
Virtual
Bytes counter of the Process object for the Oracle instance. If this
value
displayed is close to 3GB, then the amount of available address space
for
Oracle is running low. When there is no more address space free for
Oracle,
typical errors encountered are out of memory errors or connection
spawning
errors. It is in these circumstances that ORASTACK can be useful.
Optimal Flexible Architecture
While not exactly a tuning procedure, the Oracle Optimal Flexible
Architecture (OFA) is a structured method for installing Oracle
databases
and applications in a way that promotes ease of maintenance through
better
file organization, increases reliability through multiple disk support,
and
enhances performance through decreased I/O contention for disks that
contain
data files and database files (log, trace, etc.).
The addition of the Optimal Flexible Architecture to the Oracle
installation
process provides the following functionality:
database files are installed in yet another directory.
Summary
In summary, Oracle's database on Windows has evolved from a port of its
UNIX
database server to a well-integrated native application that takes full
advantage of the services and features of the Windows operating system
and
underlying hardware. Oracle continues to improve the performance,
scalability, and capability of its database server on Windows, while at
the
same time producing a stable, highly functional platform on which to
build
applications. Oracle is fully committed to providing the highest
performing,
most well integrated database on the Windows platform going forward as
Windows 2000 and 64-bit versions of Windows become available.
-- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Marin Dimitrov INET: marin_at_sirma.bg 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 Fri Mar 23 2001 - 11:47:41 CST
 |
 |