The Oracle DBA's Guide to Windows

• ORACLE ARCHITECTURE ON NT
• STARTUP AND SHUTDOWN
• PERFORMANCE MONITORING
• PERFORMANCE TUNING
• TUNING MEMORY
• TUNING DISK I/O
• TUNING CPU
• TUNING NETWORK
  
• WINDOWS REGISTRY
• SECURITY
• PERFORMING BACKUPS
• Notification On Windows
• CLEANING A WINDOWS MACHINE
• Which File System To Use?\
• Oracle Over 2 GB
  
• Last thoughts

ORACLE ARCHITECTURE ON Windows

Oracle implements an architecture that includes the following components:

• An area of memory available to all Oracle sessions, known as the system global area (SGA). This area of memory includes recently accessed data blocks (the buffer cache), SQL and PL/SQL objects (the library cache), and transaction information (the redo log buffer). The SGA may also contain session information.
• Several tasks that perform dedicated database activities, including the database writer (DBWR), redo log writer (LGWR), system monitor (SMON), process monitor (PMON), and log archiver (ARCH). Other tasks may be configured if required to support Oracle options such as parallel query, distributed database, or multithreaded servers. We will refer to these tasks as background tasks (although they are also often referred to as background processes ).
• Oracle data files that contain the tables, indexes, and other segments that form the Oracle instance
• Redo logs, which record critical transaction information required for roll-forward in the event of instance failure
  
• A separate task created to perform database operations on behalf of each Oracle session, which I'll refer to as a dedicated server. If the multithreaded server option is implemented, many sessions can be supported by a smaller number of shared servers.
  
• A SQL*Net listener task that establishes connections from external systems.
  • Database and redo log files are generally implemented using the operating system's native file system or raw disk partitions, and port-specific  differences at the file level are relatively minor. However, the memory and process structure of an Oracle instance will vary significantly depending on how the operating system implements process and memory management.
    
  • In Unix, each Oracle task is implemented as a Unix process. Accordingly, a process is created for each Oracle background task and for each (dedicated or shared) server task. The SGA exists within Unix shared memory, which any of the background or server tasks can access. Figure 1 illustrates the Oracle/Unix architecture.

    

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.

MEMORY

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!

 
STARTUP AND SHUTDOWN

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:

• OracleServiceSID. This service must be defined for each Oracle instance on the NT server. SID represents the instance identifier; in other words, OracleServiceABC1 represents the instance ABC1. Starting this service creates the Oracle process.
• OracleStartSID. Starting this service performs an automatic startup for the instance in question--equivalent to issuing a startup command from Server Manager.
• OracleTNSListener. This service implements the SQL*Net listener.

    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.
 

 
PERFORMANCE MONITORING

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 application list tracks open windows and applications that are currently running.
• The process list shows all active processes together with configuration information such as CPU usage, physical and virtual memory usage, and priority. Figure 5 shows a Task Manager process list sorted by virtual memory. You can see that the Oracle executable is consuming the most virtual memory. Its virtual memory size of 19.4MB includes the SGA and memory for all server threads. You can also see that the process contains 15 threads, which indicates the number of sessions connected to the database. In this case, the 15 threads correspond to six background threads, seven dedicated server threads, and two overhead threads.
  
• The final page of the task manager shows detailed memory and CPU usage. It provides a quick overview of CPU and memory consumption

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:

• Prevent a performance boost for any foreground application that may also be running
• View and Modify the size of the paging file(s)
• Examine the values of, and set, System and user environment variables

Use Networking for:

• Choose "Maximize Throughput for Network Applications" for the Server service
• Move the most commonly used protocols, such as TCP/IP, to the top of the Binding list
  
• Remove any protocols that were installed but are not being used.

• The first is Memory counter. This counter measures such things as available RAM, the amount of pages per second, cache faults and much more. This is often considered to be the most crucial counter because memory can have such a negative effect on other counters. For example, if your server is paging out to the disk a lot, this can cause the disk performance on a server to slow considerably. This might cause someone to replace or update the disks on a server, which will have little or no impact on performance. Increasing the memory, however, will more than likely allow disk access speeds to return to normal.
• Another counter to monitor is the Processor counter. This counter measures how much time the processor is in use and the number of interrupts it's experiencing per second. As with other counters, it's very important to look at the Memory counter results along with the processor counter resluts so you're getting the entire picture.
• The next set of counters that you'll want to monitor is the Disk counters. These counters not only measure how often a disk is servicing requests and how quickly, but they can also tell you things such as how much disk space is currently available. Note: By default, Disk counters aren't enabled on Windows machines. This is something that an administrator has to do manually. To enable these counters, you need to type diskperf -y either in the Run dialog box or at a command prompt. You'll then be prompted to restart, and once you do, you'll be able to monitor disk performance and usage.
• The final counter that's important to monitor is the Network Interface. This counter allows you to view items such as bytes per second and measure packets.

MONITORING THREADS

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

Performance tuning principles for Oracle in a Windows environment are fundamentally the same principles that govern Unix tuning. They include:

• Tuning SQL and the application design
• Ensuring suitably sized hardware--particularly that memory, CPU, network, and disk resources are adequate for the demands of the application
  
• Spreading data files evenly across a sufficient number of disks to eliminate disk bottlenecks or excessive queuing
  
• Separating redo logs from data files and the archive log destination, and possibly alternating redo logs across multiple disks
  
• Adequately sizing Oracle internal resources such as the SGA, rollback segments, sort areas, and temporary tablespace.

There are, however, a few Windows specific considerations:

• Windows supports asynchronous I/O; configuring multiple db writers is not required.
• You can create raw partitions in Windows for highly I/O-intensive applications. The pros and cons of  Windows raw partitions are the same as those for Unix raw partitions. If you're not using raw partitions, you will have the choice of either FAT or NTFS ones (see sidebar, "Which File System To Use?"). You may get some small performance improvement using NTFS partitions, but more important, only NTFS partitions can properly implement Windows security. If you implement data files on FAT partitions, they can be viewed by anyone with access to the Windows server. On NTFS, you can restrict access to DBA accounts only

 
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:

•     Primary or backup domain controller
•     File or print server
•     Remote access server
•     Router
•     Network File Server
•     Active Workstation

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

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;

Tuning Disk I/O

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

WINDOWS REGISTRY

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

SECURITY

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:

• Tighten security for the "starter database", and
  
• Provide password-less connections via the Identified Externally Option.

The starter database for installed by Oracle is not secure and should be modified as follows

1. Change the SYS and SYSTEM Password
2. Change the INTERNAL or DBA Password
3. Protect Oracle REGESTRY ENTRIES from view/modification
   
4. Protect STRTSID.CMD from viewing
   
5. Protect ORACLE EXECUTABLES from spoofing or deletion
   
6. Protect ORACLE datafiles, redo logs, control files from deletion or unauthorized capture
   
7. Protect EXPORTS and BACKUPS deletion or unauthorized capture

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:

• Connecting without a password as a 'normal' user
• Connecting as SYSDBA or SYSOPER without a password
• Connecting as INTERNAL without a password
  
• Grant Oracle ROLES exclusively through Windows groups

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.
 

PERFORMING BACKUPS

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.

CLEANING A WINDOWS MACHINE

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.

Which File System To Use?

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:

Last thoughts

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