Oracle implements an architecture that includes the following components:
The architecture of Oracle in a Windows environment is somewhat different (see Figure 2). Oracle takes advantage of Windows strong support for threads. In almost all operating systems, a process is forbidden to access memory belonging to another process. Threads belonging to the same process, however, share a common memory address space and are therefore able to share memory easily.
On Windows, the Oracle instance is implemented as a
single process. This process includes threads that implement each of
the tasks required for the instance. Therefore, there is a thread for
each of the background and server tasks plus a two-thread overhead.
Because each thread shares the same memory space, there is no need to
implement the SGA in shared memory; if you implement the SGA within the
instance's process memory, it is available to all threads within the
process. Oracle's architecture suits the Windows process/thread model.
However, the single process model restricts the
total memory available to threads belonging to the Oracle instance. In
Windows, a process may address up to 4GB of virtual memory.
However, 2GB of this memory is reserved for system overhead, allowing
only 2GB for Oracle.
There are currently two options for extending the
2GB limit: In Windows Enterprise Edition, you can reduce the
system component of process memory to 1GB, allowing up to 3GB of memory
for the Oracle instance. On Alpha NT platforms, the very large memory
(VLM) option allows up to 8GB of memory to be made available to the
Oracle instance.
With the release of Windows 2000, the process memory
limit will increase to 32GB, which should be sufficient for most Oracle
installations.
This multi-threaded architecture is very efficient,
permitting fast, low-overhead context switches, because all the threads
of the ORACLE process share resources. Among many other capabilities,
the Oracle Administration Assistant provides a way to identify
individual threads. The Assistant is run from:
Start > Programs > Oracle – HOME_NAME >
Database Administration > Oracle Administration Assistant for
Windows
Right-clicking on the SID, such as PROD, and choosing Process
Information... pops up a window displaying a list of the Oracle threads
with their type (background or foreground), associated Oracle user,
thread ID and % CPU used. This window also includes a Kill Thread
button. To be able to use this Process Information feature, the line
SQLNET.AUTHENTICATION_SERVICES= (NTS) must be present in the SQLNET.ORA
file, to enable Windows native authentication. If necessary, the
line should be added and the instance restarted.
Since Windows is a 32 bit operating system, it can address 4GB of
memory, 2GB of which are reserved for the operating system. Thus there
is a maximum of 2GB available to applications (including Oracle).
Oracle’s memory consumption is primarily a function Of the Shared
Global Area or SGA. We will discuss how to change the SGA later, but to
manage memory use, you need to ensure the SGA must fit into physical
memory, and you have a large enough page file. If your system is
consuming more memory that is physically available, you will observe a
phenomenon know as paging. You can isolate paging activity by placing
the page file on a separate volume. Using Perfmon, you can watch
Memory: page faults/sec, pages input/sec and pages read/sec. If you are
seeing more than 5 page faults per second over time, you have a paging
problem. Another easy indication of paging can be found on the
Performance Tab of the task manager, which shows Physical and Virtual
memory use at a point in time. to address paging problems you must
either reduce consumption (shrink SGA, remove unnecessary
services/protocols etc.) and/or add more memory. Do not allow you
system to continuously page fault!
Under Windows, a process running independently of a login session must be configured as a service. Consequently, each Oracle instance is associated with one or more services. The most significant services are:
To start an Oracle instance automatically, all you have to do is set the startup property to automatic for the respective services in the services applet or the Oracle Instance Manager applet. To start up an Oracle instance manually, either use the start button on the services applet or issue the net start command to start the appropriate services. For example, the following commands start the GDB1 instance and the SQL*Net listener service:
net start OracleServiceGDB1
net start OracleStartGDB1
net start OracleTNSListener80
You can also use the Oracle Instance Manager
(oradim.exe) in its command-line mode to start or stop Oracle
instances and services.
Terminating the OracleServiceSID service will
terminate the Oracle instance. However, simply terminating this service
aborts the instance without performing any of the normal shutdown
procedures. Thus, shutting down an NT server will crash the Oracle
instance--a process roughly equivalent to performing a "shutdown
abort." Although Oracle will almost always recover from such an abrupt
shutdown, most DBAs prefer a cleaner one.
VERY IMPORTANT!!! Oracle
introduces a mechanism of performing such a clean shutdown. If you set
the value of the registry parameter ORA_SHUTDOWN or
ORA_SID_SHUTDOWN to TRUE, Oracle will start a task to perform a
shutdown immediately whenever the OracleServicesid service is
stopped--including when NT is shut down.
Using the Oracle Database Instance Manager ORADIM.EXE
You can use this program to control all aspects of Oracle services. For
example, to shutdown the database use the command:
oradim.exe -shutdown
-sid ORCL -usrpwd passwd -shuttype (srvc,inst) -shutmode I
where shutmode can be a-abort; i-immediate or n-normal.
While the methodology for performing these tasks in Windows is
similar to
Unix, the tools may initially be unfamiliar.
Under Unix, the two most commonly used tools for
monitoring performance at the operating system level are sar
and top. Sar collects and reports on a variety of system
performance metrics while top reports on processes that consume the
most CPU. Under Windows, the equivalent tools are the Task Manager
and Performance Monitor.
You can invoke the Task Manager
by issuing the Control-Alt-Delete key sequence and selecting "Task
Manager." The applet includes three tabbed pages:
The Windows Performance Monitor displays a range of performance metrics, including overall CPU, memory, network, and disk metrics, not only at a summary level but also for individual threads and processes. The tool can record metrics to a log file for later playback and can report in both graphical and report formats. From an Oracle DBA's perspective, the best thing about the Performance Monitor is that it can also display or record Oracle performance metrics--provided you install the Oracle performance manager option. Figure 6 shows an example of the performance monitor reporting on both Oracle and NT metrics.
There are more important tools under Control Panel, they are System
and Networking.
System lets you:
Use Networking for:
Object |
Counter |
Description |
Oracle8 Buffer Cache |
%physreads/gets |
The objective is to have the cache miss ratio < 10%, if it is too high increase DB_BLOCK_BUFFERS in the INIT.ORA file. |
Oracle8 Dictionary Cache |
%getmisses/gets |
The objective is to have miss ration < 10% |
Oracle8 Data Files |
phyrds/sec, phywrts/sec |
Can add a chart entry for each datafile, showing the rate at which read/writes are being done. |
Oracle8 DBWR stats1 |
Buffer scanned/sec; LRU scans/sec |
Indication of Database writer activity. |
Oracle8 DBWR stats 2 |
Checkpoints/sec, timeouts/sec |
Indication of how much time Oracle is spending processing checkpoints - may need to increase LOG file size to reduce. |
Oracle8 Dynamic Space Mgmt |
Recursive calls/sec |
Sustained high rate indicates dynamic space management, you should review space allocation parameters. |
Oracle8 Free List |
%freelist waits / requests |
Contention on freelist means concurrent update activity is slowing - add freelist to updated objects. |
Oracle8 Library Cache |
%reloads/pins |
The ratio should be <10% or you should increase the INIT.ORA parameter SHARED_POOL_SIZE |
Oracle8 redo Log Buffer |
Redo log space requests |
Space request should be near zero, otherwise increate LOG_BUFFER and investigate LOG I/O. |
Oracle8 Sorts |
Memory/disk sorts /sec |
Sorts to DISK should be minimized. Increase INIT.ORA SORT_AREA_SIZE parameter to get more sorts into memory. |
Disk Reads/Sec |
The number of reads per second to that disk or volume. |
|
Disk Writes/Sec |
The number of writes per second to that disk or volume. |
|
Disk Transfers/Sec |
The total number of reads and writes per second to that disk or volume. |
|
Avg. Disk sec/Read |
The average time it takes for the read operation to occur. |
|
Avg. Disk sec/Write |
The average time it takes for the read operation to occur. |
|
Avg. Disk sec/Transfer |
The average time it takes for the read operation to occur. |
|
Avg. Disk Queue Length |
The average number of I/O’s in the disk I/O subsystem. |
|
Pages/sec counter |
Indicate that the system is paging. If you see significant activity with this counter, you are using too much memory. |
Although the task manager can show you the most resource-intensive processes, it does not break this information at the thread level; consequently, it can't tell you which Oracle sessions (which server threads) are consuming the most resources. However, you can use the query in Listing 1 to display the overall CPU usage for each thread (although the CPU usage for background tasks might not be shown)
LISTING 1. CPU usage query.
column program format a20
column username format a12
select p.spid thread, s.username,
decode(nvl(p.background,0),1,bg.description,
s.program ) program, ss.value/100
CPU,physical_reads disk_io
from v$process p, v$session s, v$sesstat ss, v$sess_io si, v$bgprocess bg
where s.paddr=p.addr
and ss.sid=s.sid
and ss.statistic#=12
and si.sid=s.sid
and bg.paddr(+)=p.addr
order by ss.value desc;
The first two threads in the Oracle executable
are the main thread and the dispatcher thread, which are not associated
with any background or server process. The next five threads at least
correspond to background tasks. The number of background tasks can be
greater if the database is in archivelog mode, has a dedicated
checkpoint process, or has implemented shared servers or parallel
query. Although it's possible to display individual thread
CPU and other resource usage in the Windows performance monitor,
mapping the
thread numbers shown in performance monitor to thread IDs recorded in
v$process is complex and unreliable because the mapping of thread IDs
to thread numbers can change as threads are created and destroyed. If
you need to monitor the performance of individual threads, you could
try the Oracle Top Sessions monitor, which is available as an add-on to
Oracle Enterprise manager, or use a third-party tool.
Performance tuning principles for Oracle in a Windows environment are fundamentally the same principles that govern Unix tuning. They include:
There are, however, a few Windows specific considerations:
General settings
Windows provides settings that allow the O/S to "tune itself" to favor
particular types of applications and usage. Since Windows supports a
wide
range of network services (such as file serving, web-serving,
application services, etc.), these settings allow the administrator to
instruct the operating system to allocate resources to optimize their
usage for a particular type of service. For function as an ORACLE
database server, the following settings should be verified or enabled:
1) Optimize for network throughput. This setting instructs
Windows to allocate resources such that they are more available and
useable
to application processes that execute locally on the server. This
setting defaults to "Optimize for file sharing", and modifying this
setting will typically generate a performance improvement of 5-10% in
the ORACLE instance. Note that this setting requires a reboot before it
takes effect. This setting can be found in the following location:
START -> Settings -> Control Panel -> Network ->
Services -> Server -> Properties -> Maximize throughput for
network applications.
2) Minimize foreground boost. This setting allows Windows
Servers that perform additional duties as user workstations to allocate
more resources to interactive sessions that are logged into the
console, and defaults to 50%. Assuming this box is a dedicated database
server, then this setting should be set such that interactive logins
are not given priorities higher than the ORACLE services. Due to
Windows architecture, certain administrative tasks may require console
login,
and it is undesirable for the database end-users to experience a
performance degradation each time an interactive login occurs. This
setting essentially minimizes a 5-10% degradation during interactive
login sessions. Note that this setting requires a reboot before it
takes effect. This setting can be found in the following location:
To change the
default behavior such that the foreground application does not receive
a performance boost, go to “My Computer/Properties/Performance” then,
select “None” as the setting for “Performance Boost for the Foreground
Application.”
3) DiskPerf. This setting causes disk performance objects and
counters to be loaded at boot time.
Without this setting toggled
on, examining disk performance with Windows Performance Monitor will
result
in all disk statistics being reported as zero. This setting incurs
approximately a 5-10% performance overhead, but the information made
available by enabling this setting will allow tuning analysis that it
is otherwise not possible. If the disk performance information is used
for bottleneck resolution, then having the information available to
perform the analysis more than offsets the resulting performance loss,
in the author's opinion. Note that this setting requires a reboot
before it takes effect. Enable this setting from the command line by
typing the following command:
diskperf -y (enables the objects and counters for logical and physical
disks) or
diskperf -ye (enables the objects and counters for logical and physical
disks plus individual disk drives in a stripped array)
4) Set your NT File Cache
small
You always want to set your NT File Cache to the smallest amount
possible since Oracle does not use it for DB operations.
The memory manager for Windows NT 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 NT 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.
In the control panel select “Network/ Services / Server/Properties” and
choose "Maximize Throughput for Network Applications"
The Maximize Throughput For File Sharing option is used primarily when
you're using a server for file and print services. This option
allocates all available memory to file systems caching, which will
increase disk performance.
The Maximize Throughput For Network Applications option should be used
on servers that are running memory-intensive server applications, as
well as domain controllers. There have been reports of domain
controllers responding three to four times faster simply by selecting
this option.
5)Remove unused Network
Protocols
Since Windows NT 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 Oracle
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, In the Control Panel select
“Network Neighborhood/ Protocols”. Select any unnecessary protocols not
needed for the system’s configuration and click Remove.
6)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, 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.
7) Remove Services you don’t need
If you navigate to the Control Panel folder and open the Services
dialog box, you'll see a list of services installed on a machine. They
are disabled, set to manual, or set to automatic.
The issue here is deciding what really needs to be turned on. Is your
server hooked up to a printer? If not, then why have the spooler
service enabled? What about other services such as Remote Access
Service (RAS), License Logging Service, Network DDE, or services loaded
by the manufacturer of the server? What about Plug n Play? Do these
need to be running on your server?
8) Use a dedicated server for Oracle.
In general, the Windows NT 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 Oracle database should not be locally accessed with a high frequency or intensively used for local user processing, unless there exist significant resources to accommodate all this activity.
9) 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 NT runs out of
page file space and is unable to successfully perform the tasks
required of it. In addition, since NT 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.
10) Eliminate access updates
Each time you access a directory on a Windows NTFS volume, by using
Explorer or the DIR command, for example, Windows updates the
LastAccess
time stamp on each directory it detects. If your system contains a very
large number of directories or supports a large number of users and you
don't care about the LastAccess stamp, the small performance hit
required for each directory update adds up to wasted performance
without producing any real value. To prevent the LastAccess time stamp
from being updated, start Regedit and drill down to the key
HKEY_LOCAL_MACHINE\System\CurrentControlSet\Control\FileSystem
From the Edit menu, select New, then DWORD Value. Name the new value
NtfsDisableLastAccessUpdate. Set its value to 1 to disable the
LastAccess update.
[HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\FileSystem]
"NtfsDisable8dot3NameCreation"=dword:00000001
Identifying Operating System Bottlenecks
As stated previously, there's essentially only three areas of the
operating system to check for performance bottlenecks: CPU, memory, and
disk. NT Performance Monitor provides easy access the objects and
counters storing the necessary information. Note that updating the GUI
interface provided by PM is a fairly expensive process, although
polling the object and counter information itself is reasonably
inexpensive. For this reason, adopt one of the two following practices:
1) Run Windows Performance Monitor from a remote workstation rather than at the console of the database server. PM can easily access performance information on a remote server, so remove the graphical workload from the database server to prevent the reported values from being over-inflated by the expensive graphical updates.
2) Install the DATALOG.EXE service on the database server, and
control the service using the MONITOR.EXE command. The DATALOG.EXE
(included in the NT Resource Kit) is the service-version of PM without
the graphical components. It appears as "Monitor service" on the
Control Panel-Services applet, it's memory and processor footprint are
fairly light, and it accepts a performance monitor PMW file as it's
data collection configuration information. The configuration
information includes the objects and counters to poll, the polling
interval, and the PM logfile in which to record the performance data.
This logfile of information can then be analyzed offline. Performance
data regularly collected with this method also proves useful for
constructing usage growth profiles to anticipate future requirements.
The following approach is organized in order of resources easiest to
analyze and fix addressed first, and will provide effective results.
Check CPU first, since that resource is the easiest to monitor, and
typically will be the least costly to rectify. If CPU is not the
bottleneck, then examine memory. Although a bit more complicated to
analyze, a memory shortage is still reasonably inexpensive to fix.
Memory should be examined before disk because a memory shortage will
inflate the volume of disk access. If memory is not the bottleneck,
then examine disk. If none of these resources appear to be the
bottleneck, then instance level initialization parameters should be
carefully examined, with assistance from ORACLE support if needed.
1) Check CPU utilization.
Counter: % Total Processor Time
Scrutinize average and maximum utilization. When the maximum
utilitization reaches 100%, the CPU's are the bottleneck. If the CPU's
consisently reach a maximum utilization of 100%, then perform
additional analysis to determine what percentage of the time they are
pegged at 100%. If they reach 100% only 1% of the time, but are below
the 100% mark 99% of the time, then they are the bottleneck only 1% of
the time. If CPU's are at or near 100% any portion of the time, then
the DBA should plan ahead and budget the purchase of additional CPU
resources before they are actually needed. If the CPU's never reach a
max utilization of 100%, CPU is not the current bottleneck. Options for
fixing a CPU bottleneck include:
- Add more CPU's
- Replace existing CPU's with processors that are faster and have
larger processor caches.
Note that replacing 4 - 200Mhz Pentium Pro CPU's with 4 - 400Mhz
Pentium Xeon processors is likely to result in greater overall
improvement than adding 4 additional 200Mhz Pentium Pro processors,
given NT's scaleability characteristics.
2) Check memory.
Counter: Pages/sec
The simplest measurement of memory on Windows is the rate of paging.
Average
and maximum paging should be examined, with additional analysis such as
calculating the percentile divisions for particular time windows if
maximum paging is excessive. Hit ratios for SGA memory structures (such
as buffer cache) may be within the target range that should yield
adequate performance (typically 80-95%, depending on a particular
organization's requirements and expectations), but checking only SGA
hit ratios may be misleadingWindows virtual memory works such that the
total memory available to an application (up to the maximum O/S limit)
is the combination of physical memory and paged memory (ie. memory
contents temporarily written to disk). Buffer cache hit ratio could
read 95%, but if physical memory is being paged hundreds of times per
second, a severe performance degradation will be experienced.
Typically, the best balance on Windows is 40-45% of the physical memory
available on the server allocated to the SGA. A rate of memory paging
greater than approximately 5 pages/second will result in exponential
performance degradation. If SGA hit ratios are low, then increase the
memory allocated to the necessary cache. If SGA hit ratios are high but
the O/S is paging more than the suggested minimum, then add more
physical memory to the server. Ideally, the DBA should plan ahead and
budget the purchase of additional memory before hit ratios and paging
becomes a significant bottleneck. If the SGA hit ratios are within the
target range and O/S paging is minimal, then memory is not the
bottleneck. Note that it is possible to allocate too much memory to the
SGA: in this scenario, the SGA hit ratios will be excellent, but paging
will occur at a rate than more than offsets the better hit ratios, thus
making overall performance slower than it would be with lower SGA hit
ratios. The objective for memory should be balancing between SGA hit
ratios and O/S paging. Options for fixing a memory shortage include:
- Adjusting SGA memory allocations to balance the ORACLE hit ratios and
O/S paging.
- Add physical memory to the server.
3) Check disk.
Counters (for each logical disk): Disk transfers/sec and Avg Disk
sec/transfer
Examine the workload imposed upon the disks used by the ORACLE
instance. Logical disks, whether single physical disks or disk arrays,
have a workload threshold that they can sustain without performance
degradation; after that workload threshold is exceeded, read and write
performance will degrade exponentially. Throughput is defined as the
rate of data transfer, while workload is defined as the rate of I/O
requests. In practice, the rate that data is read or written is
relatively constant, given that the disk rotates at a constant speed.
Because of the characteristic mechanical and rotational latencies,
finding the data on disk can take as long or longer than performing the
actual transfer, particularly in cases (like database systems) where
the nature of disk I/O is more random than sequential. With this
attribute in mind, it should be noted that workload is the most likely
candidate to overload a disk's performance capacity. As the workload
threshold (ie. transfers/second) of the disk or disk array is exceeded
the amount of time to complete each request (seconds/transfer) will
increase exponentially. Most hot-swappable SCSI disk is capable of
sustaining around 60-80 transfers/second, depending upon the particular
model and vendor. (Contact your hardware vendor to obtain these
attributes specific to your particular disk drive models.) With this
information available for each disk, the approximate workload threshold
can be calculated for disk arrays visible to the O/S as single logical
disks. (Please see the article
on RAID) For instance, if we
have a disk array that has a workload threshold of 180
transfers/second, the seconds/transfer should be in the millisecond
range when the actual workload is below this limit. When this workload
threshold becomes drastically exceeded, the seconds/transfer will
increase into the tens or even hundreds of milliseconds. Unfortunately,
I/O-bound situations are typically the most difficult and costly to
fix. Options include:
- Balancing the disk I/O over multiple disks or disk arrays/controllers
using information obtained from the ORACLE instance's BSTAT/ESTAT
reports (please see the article on instance tuning for more
information).
- Add physical disks individually or to arrays to increase the workload
threshold.
- Add more independent disk arrays and controllers to the database
server.
- Change the selected RAID technology (RAID5 to RAID1 or RAID10,
non-RAID to RAID0, etc.).
- Replace disks or arrays with faster disk and/or array controllers.
- If SGA hit ratios are below ideal, add a significant volume of memory
to reduce disk usage. Note that the higher the SGA hit ratios already
are, the less effective this approach will be.
Isolation from other application services and environmental
variables
One of the most effective techniques for improving the availability of
a given ORACLE on Windows instance is to isolate the database service
from
other application services. A production ORACLE instance with
requirements to support more than a handful of concurrent users and/or
with stringent availability requirements should be run on a dedicated
database server. Below are details of specific steps that can be
implemented.
1) Do not run the ORACLE instance on a primary or backup domain controller. Services on the PDC or BDC can impact the ORACLE instance, as well as the ORACLE instance having a undesired impact on the PDC.
2) Limit file service access to the database server. Only the DBA's and system or network administrators (plus perhaps a few application developers) need access to the file system. End users should not be able to store documents, spreadsheets, etc., on the file system of the database server. File serving and sharing on the database server absorbs computing resources as well as giving end users an opportunity to accidentally damage data files, executables, scripts, etc., used by the database instance, unless the file system security is tightly managed. In this case, the best course of action is avoidance. There may be lots of space available on the database server, but the assumption should be made that the database will need most or all of that space at some future point in time.
3) Limit print services on the database server. There will likely be very few cases where print services from database server are actually needed. Eliminate this overhead and any associated stability risks by refraining from using the print services through the database server.
4) Limit console login authority. Only the DBA's and system administrators need login authority to the database server. Console login privileges provide the ability to accidentally start or stop a variety of services that can impact the ORACLE instance, as well as the ability to perform a shutdown or kill operating system processes. All of these capabilities can be secured, but again the best policy is avoidance. If a well-meaning user can't accidentally break something, then he or she won't.
5) Secured physical location. The database server should be in a location that is inaccessible to everyone except the administration personnel. Ideally, this physical location should have a sufficient UPS in the event of power failure, and a fire containment system. The database server should not be physically located such that an end-user could accidentally trip over the power cord or network cabling while walking past the server.
6) One ORACLE instance and version per box. Since multiple
application services compete for computing resources, only one database
instance should be run per box. A database engine will attempt to use
all available CPU, memory, and disk workload resources when necessary.
Eliminate this competition and any instability that it generates by
running only a single instance on each box, especially if these are
production instances. Ideally, there should be at least two seperate
database servers: one for production, and one for development. This
seperation prevents development mishaps, upgrades, etc., from impacting
a production instance. In some cases, a quality assurance instance is
also justifiable. A good rule of thumb is to keep the development
server at about half of the throughput capacity of the production
server. In this way, potential performance problems are more likely to
be noticed before the application goes into production.
ORACLE provides the capability to have multiple versions installed
on the same box through the use of multiple ORACLE_HOMES. This feature
can be useful for upgrade activity, but should not be taken as an
encouragement to run multiple instances (of the same or a different
version) on the same box. Seperation of instances not only opens up
better maintenance
windows, it also removes any instabilities resulting from contention
and competition of instances.
7) Limit non-ORACLE software, applications, and processes. Due to the nature of Microsoft's Component Object Model, it's shared components, and the object resolution provided by the registry, it is entirely possible to install a non-ORACLE software component that damages an ORACLE-related software component or registry entry. A good rule of thumb is to assume that different applications will not run on the same box until you have explicitly proven that assumption to be false. Whether the non-ORACLE application is a commercial product or a custom creation, it's peaceful co-existence should at least be tested with a non-production instance before installing on a production instance. Ideally, this application or component should be run on a server seperate from the ORACLE database, even if it connects to and does work with the ORACLE instance. SQL*Net provides the transport layer that makes the physical location of the ORACLE instance completely transparent to any application, so there is absolutely no reason that a server-side application that interacts with an ORACLE instance has to be installed and run on the same box, regardless of whether it is a middle-tier component, a web-server based component, etc.
8) Do not run a multi-server backup solution from the database
server. Although this temptation is appealing because the database
server will attain higher throughput on backups with the backup
solution physically connected to and run from the database server, it
can cause instabilities through competition for resources, and it
generally severely limits the window of opportunity for server or
database maintenance. Essentially, offline corrections and
administrative work cannot be done during the day because of user
workload, and cannot be done at night because the database server and
other servers are being backed up by the database server. This is
especially true of production instances, and can also easily become
true of development instances. Avoid this particular temptation, unless
the backup apparatus is dedicated only to the database server.
Isolation from other application services and environmental variables
One of the most effective techniques for improving the availability of
a given ORACLE on Windows instance is to isolate the database service
from
other application services. A production ORACLE instance with
requirements to support more than a handful of concurrent users and/or
with stringent availability requirements should be run on a dedicated
database server. Below are details of specific steps that can be
implemented.
Tuning Memory
* Allow 40 Megabytes + for Oracle SGA
* Windows requires 12 Megabytes + of Memory
* Allow 1.5 Meg of Memory per Client user
* Allow 3 Meg per Server User
Memory Monitoring
* Ensure no process is using excessive Memory
* Windows tries to have 4MB free at all times
* If Free Memory < 4 Meg there is a definite memory problem
Virtual Memory
Another easy memory configuration change that you can make has to do
with the Windows paging file. The paging file settings can be changed
by
going to the System control panel, selecting the Performance tab, and
clicking the Change button under the Virtual Memory section.
Most experts agree that spreading the paging file on to more than one
disk is one very quick way to increase memory performance on a Windows
server. This can increase performance because more than one disk is
available to handle the requests. Another recommendation is to set the
initial and maximum sizes of the paging file(s) to the same amount.
This way Windows won't attempt to grow the paging file and cause it to
fragment.
* As a rule of thumb, set Virtual Memory to twice Physical Memory
* Have separate page files on dedicated disks (Windows handles load
balancing)
* Don’t have them on RAID5 or Network Drives
* Windows Pages only, does not swap
Critical Oracle Memory Parameters
* Set Buffer Cache = 10 Meg + (DB_BLOCK_SIZE*DB_BLOCK_BUFFERS)
* Set SHARED_POOL_SIZE = 9 Meg +
* Set LOG_BUFFER = 132K + (Compile Package 15 sec 132K 24 sec 8k)
* Set SORT_AREA_RETAINED_SIZE = 32K
* Set SORT_AREA_SIZE = 500K
Serious Memory Tuning
* 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.
Run Orastack on listener & Oracle kernel
'orastack oracle.exe 500000'
'orastack tnslsnr.exe 500000'
'orastack svrmgrl.exe 500000'
By reducing the stack of every session created in the Oracle
executable, it is possible to achieve a larger user population. In a
system with a 1000 users reducing the stack from 1Mb to 500K would
release 500Mb of the address space for other allocations or more users.
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.
* Set Registry ORA_WORKINGSETMIN to 8 (MB) in HKEP_LOCAL_MACHINE -
SOFTWARE - ORACLE
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 :
a) If used in combination with PRE_PAGE_SGA, the
working set will start above the minimum threshold and not drop below.
b) If ORA_WORKINGSETMIN is used in isolation, then
once the working set rises above the threshold it will not drop below.
Always use these parameters with caution, because they change Windows 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.
Large SGA
on 32bit Windows
While 64-bit x86 processors along with 64-bit Windows are becoming
increasingly prevalent in the modern datacenter, the overwhelming
majority of Windows nodes still continue to use the 32-bit operating
system.
When using a 32-bit platform, the Oracle database and other
applications are only able to address approximately 2GB of system
memory. However, by using the Physical Address Extensions option
(”/PAE”) in Windows, along with special Oracle initialization
parameters, one can overcome this limitation.
The following Windows operating systems can use PAE to take
advantage of physical memory beyond 4GB:
* Windows 2003 DataCenter Edition (32-Bit)
* Windows 2003 Enterprise Edition (32-Bit)
* Windows 2000 DataCenter Server
* Windows 2000 Advanced Server
To enable PAE, use the /PAE switch in the Boot.ini file.
With the /PAE option Oracle is able to reserve an SGA. The size of this
footprint is a function of (DB_BLOCK_BUFFER * DB_BLOCK_SIZE) plus some
additional parameters. Direct setting of the DB_BLOCK_BUFFERS and
USE_INDIRECT_DATA_BUFFERS must be used in place of the more modern
TARGET_SGA or the DB_BUFFER_CACHE parameters.
The following Oracle RDBMS servers support 32-bit large memory:
* Oracle 8.1.6.x
* Oracle 8.1.7.x
* Oracle 9.2.x
* Oracle 10.1.x
Because of 32-bit limits, the OS/processor still cannot concurrently
address all of the physical memory you could allocate. Instead there is
a configurable floating window of memory (”AWE_WINDOW_MEMORY”) that is
mapped and unmapped as needed. Optimizing the size if this window
increases system performance by reducing these map and un-map
operations. So why can’t you make the window wide enough to fit all the
memory?
Because:
(Total Physical RAM - 4GB + AWE_WINDOW_MEMORY) = MAX TOTAL BUFFER CACHE
Example: 12GB - 4GB + 1GB = 9GB BUFFER CACHE
On a machine with 12GB of RAM, using the default value of 1GB for
AWE_WINDOW_MEMORY, your total buffer cache could be as high as 9GB. As
you increase the size of the AWE_WINDOW_MEMORY (which defaults to 1GB)
you reduce the potential size of your SGA. AWE_WINDOW_SIZE is set in
the Windows Registry to the number of bytes of address space to use for
your window.
Once your OS install is prepared, the following steps are used to
configure Oracle:
1) Log in to the server as Local Administrator
2) Check $ORACLE_HOME/database for the file init.ora
3) If the file does NOT exist:
copy the file
$ORACLE_BASE/admin/pfile/init.ora.
to:
$ORACE_HOME/database/init.ora
4) Edit this file adding:
###########################################
# Large Memory Support
###########################################
USE_INDIRECT_DATA_BUFFERS=TRUE
###########################################
# Block buffers base on 16k block size
###########################################
#DB_BLOCK_BUFFERS=65536 #1 GB
#DB_BLOCK_BUFFERS=80000 #1.4 GB
#DB_BLOCK_BUFFERS=117963 #1.96
#DB_BLOCK_BUFFERS=131072 #2 GB
#DB_BLOCK_BUFFERS=196608 #3 GB
#DB_BLOCK_BUFFERS=262144 #4 GB
#DB_BLOCK_BUFFERS=318920 #5 GB
DB_BLOCK_BUFFERS=393216 #6 GB
java_pool_size=4194304
large_pool_size=4194304
shared_pool_size=159383552
5) Comment out the line:
target_sga=NNNNNNNNN (i.e.
#target_sga=NNNNNNNNNN )
6) Finally, from the command line execute
the following statements:
sqlplus /nolog
> conn / as sysdba;
> shutdown immediate;
> startup pfile=$ORACE_HOME/database/init.ora
> create spfile from pfile;
> shutdown;
> startup;
Commands to Monitor Disks
* Diskperf requires rebooting machine, is a file that let you monitor
disk I/O
diskperf - y # for unstriped disks
diskperf - e # for striped disks
diskperf - y \\ofst # turns on the monitoring for a
remote computer called ofst
diskperf -n to unload it
* You can run Windows performance monitor with Oracle Performance
Monitor
Disk Striping
* Do not use Raid-5 in write intensive environments
* Balance I/O across controllers
* Oracle recommends the following: Online or archived redo log files
can be put on RAID 1 devices. You should not use RAID 5. ’TEMP’
tablespace data files should also go on RAID1 instead of RAID5 as well.
The reason for this is that streamed write performance of distributed
parity (RAID5) isn’t as good as that of simple mirroring (RAID1). Swap
space can be used on RAID devices without affecting Oracle. Place REDO
logs on separate fast devices, separate DATA and INDEX datafiles and
avoid RAID 5 for Oracle files.
When selecting a disk subsystem, these general rules should be
followed:
· SCSI is preferable to IDE.
· Fast wide SCSI is preferable to narrow/standard SCSI.
· RAID level 5 is preferable to a single disk.
· A hardware-based RAID controller is preferable to software
RAID.
· More spindles are preferred over fewer spindles, to distribute
writes.
· A caching disk controller is preferable to a non-caching
controller, but a caching controller must have a battery backup to
ensure data integrity.
Disk Striping Advice
* Striping runs well on NT 4.0 Server
* OLTP ideal is both RAID 0 and RAID 1
* Do not use RAID 0 without RAID 1
* RAID 5 is ideal for Data Warehouses, Reporting databases
* Mixed OLTP and DSS may consider RAID 0,1,5 combination
Tuning CPU
Oracle CPU Usage
* Oracle makes excellent use of Windows multiprocessors
* Set DB_BLOCK_LRU_LATCHES and LOG_SIMULTANEOUS_COPIES = #
CPUs (not sure)
Since Windows uses asynchronous I/O by default, using multiple DBWR
processes may not necessarily improve performance.
Increasing this parameter could, in fact, reduce performance on systems
where the CPU's are already over burdened. In cases where the
main performance bottleneck is that a single DBWR process cannot keep
up with the work load, then increasing the value for
DB_WRITER_PROCESSES may improve performance.
The main reason to manually set this parameter is to allow you to
configure multiple buffer pools in Oracle or if you want to use
multiple DBWR processes. There must be at least one latch per DB writer
gatherer process.
* Check 'cpu_count' to make sure it is reflecting the correct number of
cpu(s). You should not have to set it, it is automatically set by
Oracle, but it would be interesting to know if it is reporting the
correct value.
* Multiple CPUs best used with striped data files
* The amount of data read in a multiblock read is specified by the
Oracle initialization parameter DB_FILE_MULTIBLOCK_READ_COUNT. The
value for this parameter should always be set high because there is
rarely any disadvantage in doing so. The size of the individual I/O
requests depends on both DB_FILE_MULTIBLOCK_READ_COUNT and
DB_BLOCK_SIZE. A good value for multiblock reads is 64K.
* Multiblock writes are similar in nature to multiblock reads and have
many of the same requirements. Multiblock writes are available through
the direct path loader as well as through sorts and index creations. As
with multiblock reads, the multiblock writes reduce I/O and CPU
overhead by writing multiple database blocks in one larger I/O
operation. The amount of data written in a multiblock write is
specified by the Oracle initialization parameter
DB_FILE_MULTIBLOCK_WRITE_COUNT. The size of the individual I/O requests
depends on both DB_FILE_MULTIBLOCK_WRITE_COUNT and DB_BLOCK_SIZE. As
with multiblock reads, a good value is 64K.
* Consider parallel query
The Oracle Parallel Query option makes it possible for some Oracle
functions to be processed by multiple server processes. The functions
affected are queries, index creation, data loading, and recovery. For
each of these functions, the general principle is the same: keep the
processing going while Oracle is waiting for I/O. For most queries, the
time spent waiting for the data to be retrieved from disk usually
overshadows the amount of time actually spent processing the results.
With the Parallel Query option, you can compensate for this "wasted
time" by using several server processes to execute the query. While one
process is waiting on I/Os to complete, other processes can execute. If
you are running on a Symmetric Multiprocessor (SMP) computer, a
cluster, or a Massively Parallel Processing (MPP) machine, you can take
maximum advantage of the Parallel Query option. The amount of
parallelism can be tuned with several of the Oracle initialization
parameters:
- PARALLEL_DEFAULT_MAX_SCANS = Specifies the maximum number of query
servers to used by default for a query. This valued is used ONLY if no
value is specified in a PARALLEL hint or in the PARALLEL definition
clause. This parameter limits the number of query servers used by
default when the value of the PARALLEL_DEFAULT_SCANSIZE is used by the
query coordinator.
- PARALLEL_DEFAULT_SCANSIZE = Specifies the number of query servers to
be used for a particular table. The size of the table divided by
PARALLEL_DEFAULT_SCANSIZE determines the number of query servers, up to
PARALLEL_DEFAULT_MAX_SCANS.
- PARALLEL_MAX_SERVERS = Maximum number of query servers or parallel
recovery processes available for this instance.
- RECOVERY_PARALLELISM = The number of processes to be used for
instance or media recovery. A large value can greatly reduce instance
recovery time. A value of zero or 1 indicates that parallel recovery
will not be done and that recovery will be serial. A good value for
this parameter is in the range of the number of disks you have (up to
50). I am a real fan of the Parallel Query option. I have seen great
improvements from the use of parallel queries as well as dramatic
reductions in recovery time when the parallel recovery feature is used.
With Oracle8i.new parameters allow for more automatic configuration of
parallel query.
Tuning Network
Network Tuning
* For best performance make heavy use Oracle stored code
* Simple ping or tnsping command is often best to identify bottlenecks
* Windows Performance Monitor has some network info after connection
made
* Best to use “Network Monitor”
Using Network Monitor
* Select Network icon in Control Panel
* Click Services tab and click “Add”
* In the Network Services box select Network Monitor Tools and Agent
* Now you can check the Performance Monitor. View the “% Network
Utilization” figure
* “#Frames Dropped” is frames not processed because network buffers full
The Windows registry is a central location for programs to store initialization and configuration information.Oracle uses this facility to store various configuration setting. You can change these using the REGEDIT32 program, however "use at your own risk" as you can completely toast your system if you mess up. Having said that, it is often necessary to change some of the settings.
Under the HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE key. Some entries you
might want to change are:
Registry Key |
Value |
Comments |
ORA_%SID%_PWFILE %ORACLE_HOME%\DATABASE |
Change location of PASSWORD file for Connect Internal |
|
ORA_%SID%_SHUTDOWN |
TRUE or FALSE |
TRUE cause shutdown immediate when service terminates |
ORA_%SID%_SHUTDOWN_TIMEOUT |
Number Seconds to wait for timeout -numbers < 30 are ignored |
|
ORACLE_SID |
SID |
Default SID |
SQLPATH %ORACLE_HOME%\SQLPLUS |
Directory to search for SQL files |
There are a few more under the
HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\Oracle\Performance\Oracle%HOME%
Registry Parameters to Change
* HKEY_LOCAL_MACHINE\System\
CurrentControlSet\Control\Session Manager\Memory
Management\DisablePagingExecutive
Change from default 0 to 1 will force NT to keep all of its kernel
function resident in memory, thus helping them to execute faster.
* HKEY_LOCAL_MACHINE\System
\CurrentControlSet\Control\FileSystem
\NtfsDisableLastAccessUpdate
Change REG_DWORD from 0 to 1 will prevent updates of last access date
time stamp on directories
* HKEY_LOCAL_MACHINE\System\
CurrentControlSet\Control\ Session Manager \ Memory
Management \LargeSystemCache
Change to 3 for “Network Maximizing”
n.b. 0 = Minimize Memory Usage, 1 = Balance all memory
usage, 2 = Maximize File Sharing
Database security for Oracle on Windows is exactly like Oracle for other platforms and will not be covered in this paper. Two main tasks that you will want to do when setting up Oracle security under Windows are:
The starter database for installed by Oracle is not secure and should be modified as follows
The Identified Externally authorization mechanism is a means for
Oracle users to connect to ORACLE without providing a password.
Essentially, you tell Oracle that you trust the operating system to
authenticate a user. In order to have this work some preparation is
required. If correctly configured, you can provide the following
privileges:
You may use Windows authentication across Windows domains, or for
LOCAL
access. These examples will demonstrate the DOMAIN-based access.
For Windows authentication to work:
1. You must modify the %ORACLE_HOME%\NETWORK\ADMIN\SQLNET.ORA file on
the
server and client to include the following line:
SQLNET.AUTHENTICATION_SERVICES = (NTS)
2. You must modify the INITSID.ORA file to include the following line
and shutdown/restart Oracle :
OS_AUTHEN_PREFIX = ""
3. Use Windows User Manager to create a USER for the domain
4. Set/Create
HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\HOMEn\OSAUTH_PREFIX_DOMAIN to TRUE.
This optional step requires the Oracle username to include the DOMAIN
(i.e. DBCORP\SCOTT)
5. Set/Create
HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\HOMEn\OSAUTH_ENFORCE_STRICT to TRUE
This optional step requires any user to be a member of the LOCAL group
ORA_USER or ORA_SID_USER.
You need to create these groups and assign the user.
6. Use SQLPLUS to "create user DBCORP\SCOTT IDENTIFIED
EXTERNALLY" and grant the user the roles you wish.
You should now be able to log into oracle with the connect string
“Connect /@DBNAME” when you are connected to the Windows server. Note
that
you must either LOG ON to a client computer with the correct USERNAME
and Domain or access a shared area in the Windows server using the
NET USE command or Windows Explorer in order to be authenticated under
Windows. In order to connect as SYSOPER or SYSDBA without a
password, you must create Windows groups. Create the groups ORA_OPER or
ORA_DBA for access to all instances, or ORA_SID_OPER and ORA_SID_DBA
for specific instances on the Windows server. Then add the Windows user
to these
groups, and then you can "connect/@DBNAME AS SYSDBA". To use SYS AS
SYSDBA without as password, you must additionally add the following
entry into the INIT.ORA file:
REMOTE_LOGIN_PASSWORD=NONE
SYS AS SYSDBA then will work from any Oracle tool if you are
connected LOCALLY, but only Server Manager will work remotely. Finally,
if you want to use Windows to grant roles, it is important to know that
you
must use OS roles exclusively. You can't use a mixture of Windows roles
and
Oracle roles. To enable this feature you need to put the following line
in the INITSID.ORA:
OS_ROLES = TRUE
And then simply create the Windows groups you want to assign roles to
with
the following naming convention:
ORA_SID_ROLENAME [_D] [_A]
Where rolename is the Oracle role you have created and the optional _A
or _D indicate whether you want to have the role as DEFAULT or WITH
ADMIN OPTION.
As with Unix and other environments, you can
back up an Oracle instance in Windows using either online backups with
archive logging, cold (offline) backups, or database exports.
In Unix, job scheduling is usually achieved
using the cron facility, which lets you schedule jobs for regular
execution, or the at command, which lets you schedule jobs for once-off
execution.
The Windows at command can submit jobs for both
regular and once-off processing. The basic syntax for the AT command is:
AT time [/every:date] [/next:date] command.
Time is the time of execution specified using 24-hour notation.
Date is one or more days of the week (M, T, W, TH, F, S, SU) or month
(1 to 31).
For example, the following command runs an archive script every day
at 3 p.m.:
AT
15:00 /EVERY:m,t,w,th,f,s,su cmd /c "c:\bin\archive.bat
>c:\logs\archive.log"
rem -- List jobs in scheduling queue --
AT
rem -- Remove all jobs from scheduling queue --
rem AT /DELETE /YES
rem Note: The AT command does not utilize the PATH environment
variable.
rem Use full path name to the batch
file and other commands
You must start the Windows scheduler service in order to use the at command.
Notification On Windows
Many operating systems provide an intrinsic method for sending messages
via email. However, one common irritation with Windows is the lack
of a UNIX-like "sendmail" command. Luckily, here are some Windows
Utilities to add this functionality:
http://www.ipass.net/~davesisk/oont_management_scripting_2.htm
http://www.users.globalnet.co.uk/~jchap/tvde.htm
http://www.komaromi.com/dos_email/
http://home.concepts.nl/~fiber/dose.htm
In fact, one such command-line utility (MailTo.exe) is available as
freeware. This particular MailTo command sends STMP mail, and can
include text files as the message body or attachments, and binary files
as attachments. The syntax is very simple, and since it is a
command-line executable, calls to it can be included in BAT files or
more advanced scripts. Below is a list of possible uses, some
of which have examples in the scripting article of this section:
1) Scan the ORACLE alert log for errors using the FINDSTR command, and,
if any errors are found, email them to a list of email addresses. A job
such as this can be scheduled to execute every 60 minutes, for instance.
findstr /N "ORA- incomplete" C:\oracle\admin\DIE\bdump\dieALRT.LOG
2) Check services to make sure they are started, and, if not, send an
email to operational personnel. Again, this type of job can be
automated to run at short periodic intervals, such as every 15 minutes
or once per hour. Consequently, the job could be constructed in a
manner that attempts to restart the particular service, as well as
mailing a note to an administrator.
3) Scan the spooled output from a hot backup for errors using FINDSTR,
then email any errors that were found to an administrator. This
functionality could be added to the job that runs the hot backup.
4) Scan the spooled output from a full export for errors using FINDSTR,
and, if errors are found, send an email containing the error listing to
a DBA. This functionality could be added to the job that actually runs
the full export.
5) Perform a TNSPING or even connect to a database instance. If
connection is not achieved, send an email to an administrator, and
perhaps even attempt to restart the database instance if the connection
error indicates the instance is not available. This type of job could
be scheduled to run several times per hour, if necessary.
6) Spool the output of a DIR command, or the output of the DIRUSE
command, to a text file and email this output to an administrator. This
type of job can be used to monitor available disk space, and could be
scheduled to run each morning, once per week, etc.
A. Removing Components on Windows NT/2000/XP.
To remove all Oracle components from a computer on Windows NT/2000/XP:
1. Check privileges:
1.a. Ensure you are logged in as a user with Administrator privileges.
1. Stop all Oracle services (if any are running):
1.a. NT: Choose Start > Settings > Control Panel > Services.
2000,XP: Right click My Computer > Manage
> Services and Applications > Services
1.b. If any Oracle services (their names begin with Oracle) exist and
have the status Started, select the service and click Stop.
1.c. Click Close to exit the Services window.
1.d. Close the Control Panel/Computer Management window.
2. Use
the Oracle Uninstaller to remove most of the software
Choose Start/Programs/orabd10g_home_1_oracle / Installation Products /
Universal Installer
3. Remove the entries in the Windows registry:
3.a. Start the registry editor:
Choose Start > Run > regedit
Note: On Windows you can use regedt32 instead. The searching
capabilities of regedt32 is limited compared to regedit. It will be
needed to be able to edit 32-bit entries in the registry. Since we are
not going to update any 32-bit entry it is not needed.
3.b. Go to HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE
Note the value of the key INST_LOC, this is the location of the Oracle
Universal Installer (OUI). The default location is C:\Program
Files\Oracle\Inventory. If this value is different, make note of it, so
we can delete these files later. Delete this ORACLE key.
3.c. Go to HKEY_LOCAL_MACHINE\SOFTWARE\ODBC and expand all subkeys and
remove all keys under here which are related with the "Oracle ODBC
Driver"
3.d. Go to HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services and
remove all keys under here that begin with ORACLE or ORAWEB.
3.e. Go to
HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\EventLog\......\Application
and remove all keys under here that begin with ORACLE.
3.f. Go to
HKEY_LOCAL_MACHINE\Software\Microsoft\Windows\CurrentVersion\......\Uninstall
and remove any entries related to Oracle.
3.g. Go to HKEY_CLASSES_ROOT, remove all keys that begin with Ora or
ORCL (e.g. Oracle..., ORADC..., ORAMMC..., OraOLE..., OraPerf... and
ORCL...).
3.h. Close the registry.
4. Clean up the environment settings:
4.a. Choose Start > Settings >
Control Panel > System > Advanced tab > Environment variables.
4.b. At "System Variables" click on the variable PATH in order to
modify the value. For example, you may see a path similar to this one:
C:\ORACLE\ORA92\BIN;C:\PROGRAM
FILES\ORACLE\JRE\1.1.7\BIN
4.c. If an %ORACLE_HOME% was installed, remove this %ORACLE_HOME%\BIN
path.
4.d. If JRE was installed by Oracle, remove the JRE path.
4.e. If there is a CLASSPATH variable under "System Variables", first
make note of the path defined, then delete it. This variable can be
added back at a later date if needed.
4.f. Check if there are any other Oracle variables set in "System
Variables", ORACLE_HOME, ORACLE_SID, TNS_ADMIN, JSERV or
WV_GATEWAY_CFG. If these exist, delete them also.
4.g. Click on APPLY and OK.
4.h. Close the Control Panel window.
5. Delete the software and icons:
5.a. Choose Start > Programs >
Accessories > Windows Explorer.
5.b. Go to %SystemDrive%\DOCUMENTS AND
SETTINGS\ALL USERS\......\START MENU\PROGRAMS
Note 1: These locations depend on whether OS was upgraded from NT, or
this was a fresh install of 2000/XP.
Note 2: To locate your System Drive, type in DOS-box: echo
%SystemDrive% and delete the following icons:
5.c. Go to %SystemDrive%\Program Files\Oracle or the location of
INST_LOC as noted earlier in step 3.b. and delete this directory.
Note: In order to successfully delete all files, you may have to reboot
your computer first, in order to clear Operating System locks on those
files.
5.d. Go to the temporary directory and delete all files and directories
in here (see note in 5.c.).
%SystemDrive%\Documents and
Settings\<username>\Local Settings\Temp\
5.e. Go to the drive where the Oracle software is installed on your
machine and delete all ORACLE_BASE directories on your hard drive (see
note in 5.c.).
5.f. Close the Windows Explorer.
6. Finish the removal:
6.a. Empty the recycle bin Right click on recycle bin > Empty
Recycle Bin.
6.b. Reboot your computer.
6.c. Optionally: If you are on Windows 2000 or XP run the System
Defragmenter utility:
- from Control Panel, select Administrative Tools > Computer
Management
- expand Storage, then select Disk Defragmenter
- highlight each virtual drive, in turn, and click Defragment
- reboot your computer when finished.
I was curious about the performance characteristics
of the various alternatives, so I performed a simple benchmark to
compare insert performance, indexed I/O, and full tablescan I/O. I
created a simple database on FAT, NTFS, compressed NTFS, and raw
partitions. For each database, I imported an 8MB export file, performed
a complex query that joined (via index) four of the larger tables I had
imported, and performed a full export.
The tests were performed on a Pentium Pro 256MHz
single-CPU host with 64MB of memory and a single 2.5GB IDE disk drive.
The machine was rebooted before each test. The results--as indicated in
Figure 7--revealed no significant performance difference between the
FAT and NTFS file systems. However, compressed NTFS file systems
performed much worse than either FAT or uncompressed NTFS.
The raw partition database significantly
outperformed both NTFS and FAT file systems for all operations. I
expected that raw partitions would perform well for write-intensive
operations, but I was surprised to see that operations involving
tablescans and index lookups also improved.
These results should not be seen as in
any way conclusive. However, on the basis of these results, I would
consider raw partitions for high-performance Windows databases but
steer
clear of compressed NTFS file systems. Don't forget to take issues such
as security, maintainability, and backup, into account when deciding on
a type of file system
Oracle's implementation of database on Windows has some specifics not seen on other platforms (*nix, Linux). Main difference is that all Oracle processes (PMON, SMON, even server processes) run within single OS process as separate threads. The reason for this is that on Windows you can't start a background process other than a "service". Otherwise you have to start it in a user session and it will be stopped when session is ended (user logged out). So, Oracle had to "pack" all the background processes as threads within a single process that you see in "Services" panel on Windows as your "OracleServiceORCL" or something else with ORCL representing your database's SID.
Main impact of this architecture is that the maximum amount of
memory that can be used for both
SGA and PGA is limited by a maximum amount of memory available to
single process. On Windows (we assume a 32-bit platform in this
article), the default maximum user addressable memory is 2GB. It is a
limitation imposed by a 32-bit addressing (4GB) and fact that Windows
reserves 2GB out of user space for kernel memory.
So, this means
that sum of SGA and PGA cannot be over 2GB... Not a very pleasant
situation, given that nowadays it is rather common to have a cheap
Intel server running Windows with Oracle with 4GB of RAM.
What can be done to get around this? Well, good news is that you can do something...
First, you can use a /3GB switch in boot.ini file. Just put it like this:
[boot loader]
timeout=30
default=multi(0)disk(0)rdisk(1)partition(2)\WINNT
[operating systems]
multi(0)disk(0)rdisk(1)partition(2)\WINNT="Microsoft Windows 2000
Server" /fastdetect /3GB
What does it do? Basically, what it looks like - pushes the limit of 2GB to 3GB? How? - Windows reserves not 2GB but only 1GB for kernel use. This gives us extra 1GB for user process.
If you have over 4GB of RAM, you can also use another mecahnism in Windows to leveragr this memory. It is called Address Windowing Extensions (AWE) and is enabled by /PAE switch in boot.ini:
[boot loader]
timeout=30
default=multi(0)disk(0)rdisk(1)partition(2)\WINNT
[operating systems]
multi(0)disk(0)rdisk(1)partition(2)\WINNT="Microsoft Windows 2000
Server" /fastdetect /3GB /PAE
But just setting the switch is not enough. Now you need to configure
Oracle to use the extra memory you've got in there. How Oracle does it
is that it "maps" the extra memory through a "window" in the usual
memory space. One limitation here is that Oracle can do it only for
buffer cache, not shared pool.
So, what you need to set in Oracle is:
So, to summarize all of the above:
One thing to be aware of is that Windows has a default behavior
that is different from Unix for running scripts. In Unix, if you call a
HOST command on SQL*PLUS, your scripts waits for return before
executing the next HOST command. With Windows, your calling script will
continue without waiting and this can cause problems if commands need
to be executed in order. To get around this in your scripts, you can
use the command-line SQLPLUS and call the programs with the following
syntax:
HOST start/wait PROGRAM.EXE
Or
HOST start CALL PROGRAM.EXE