Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> Re: NT Server very slow

Re: NT Server very slow

From: Marin Dimitrov <marin_at_sirma.bg>
Date: Fri, 23 Mar 2001 09:47:41 -0800
Message-ID: <F001.002D642A.20010323091732@fatcity.com>

> 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



"When someone is seeking, it happens quite easily that he only sees the thing that he is seeking; that he is unable to find anything, unable to absorb anything, because he is only thinking of the thing he is seeking, because he is obsessed with his goal. Seeking means: to have a goal; but finding means: to be free, to be receptive, to have no goal. ..."
                                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.

For more information on the specifics regarding OFA, consult the Windows-specific documentation that comes with Oracle8i for Windows.

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

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US