Optimize Oracle9i performance in Windows 2000

 I. Optimized disk configuration
Oracle is a disk I/O-intensive application. Make sure that you configure the disk and file system properly:

Run the disk fragment program before creating a data file on the disk.
To securely organize disk fragments, You need to disable the instance that opens the data file and stop the service. If you have enough continuous disk space to create data files, you can easily avoid data file fragments.
Do not use disk compression

Oracle data files do not support disk compression.

Do not use disk encryption

Encryption adds a processing layer like disk compression to reduce disk read/write speeds. If you are worried that your data may be leaked, use the dbms_obfuscation package and label security to selectively Encrypt sensitive data.

Do not use more than 70% disk space

The remaining disk space is used to store temporary system data and intermediate data as the disk fragment program.

Use RAID

Select hardware RAID exceeding software RAID;
With hardware RAID Controller;
Do not place log files on RAID 5 because RAID 5 has high read performance and poor write performance.
Place log files and archive logs in the disk control system that is separated from control files and data files.

Detach Page Swap files to multiple Disk Physical volumes

Create two page files across at least two disks. You can create four page files and benefit from the performance. Make sure that the total size of all page files is at least twice the physical memory size.
　Ii. Optimize CPU usage and Configuration

Cancel Screen Protection

Screen Protection absorbs a large amount of CPU resources and provides meaningless use for database servers. In particular, 3GL screen protection must be disabled, if you must use screen protection, use "empty screen" to reduce CPU usage.

Configure the system as an application server

On the "System" tab of the running control panel, set "performance options" to "background programs", which provides priority to applications, such as services similar to Oracle, you are opposed to starting a clumsy program on the graphic user interface.

Monitors interrupted hardware consumption in the system

Hardware that consumes CPU interruptions and time should be avoided. Generally, this hardware is cheap because it loads work into the CPU, and the CPU needs to process peripheral hardware with advanced performance. Note:
1. Nic that supports Bus Control
2. Disk controllers that support DMA instead of PIO
Use the performance monitor to track the % Interrupt Time counter value of the processor object and the baseline and standard of this counter, and then monitor the problem.
3. A hardware RAID controller is used to replace the software RAID supported by Windows 2000.

Keep minimum security audit records

Run Oracle on a dedicated server

Oracle is a large memory consumer. Do not run Oracle databases on systems that execute the following functions:
1. The primary domain or backup domain controller is called a domain controller in Windows 2000)
2. File Server
3. Print Server
4. Remote Server Access
5. Routers, proxies, or firewalls
Do not use Fancy wallpapers. If you use them, minimize the wallpaper file size)

Disable non-essential services

It is best to disable non-essential services in the system. If some service functions are required from time to time, you can set the Startup Type to "Manual". To do this, first verify the actual service requirements with the network administrator:
1. If your system does not need a printer, usually stop the service and set it to manual;
2. Stop the License Logging Service unless you have special requirements for it;
3. Do not use the DHCP service and disable it;
4. Do not start unnecessary programs automatically;
Check the content in "Start/Program/start" and delete unnecessary programs.

3. Optimizing Network Configuration

Network configuration is an important part of performance adjustment, and it is easy to hide performance bottlenecks.

Configure the fastest speed and effective mode for NICs

For automatic detection, most of the default installations are NIC. If this is optional, try to adjust it to "Full Duplex" and maximize wire speed.

Delete unnecessary network protocols

Only the TCP/IP protocol is retained.

Optimized Network Protocol binding sequence

Set the master protocol on each Nic, typically TCP/IP, to the top of the protocol list.

Disable or optimize file sharing for Oracle

Ideally, the file sharing function should be disabled to minimize security leaks and network traffic. However, if you need to use file and print sharing, configure the "connection attribute" of each network card in the system ", set "maximize network application data throughput"

Iv. Memory Optimization

Memory optimization is a key setting for the Windows platform. First, let's take a look at the Oracle structure of the Windows 2000 Platform:

Thread-based structure

Windows2000 is a thread-based structure. On the contrary, Unix operating systems are process-based structures. This means that more applications in Windows, including Oracle, are executed in the form of a single process with multiple threads, this thread-based structure indeed brings Window2000 advantages-easier to share memory. Memory space is allocated to each process. The shared memory between processes is clumsy and requires additional encoding. The thread is a subset of the process and uses much less memory than the process. All threads of a specific process share the same process memory space. The shared memory between threads of the same process is faster than the shared memory of different processes. This gives a thread-based structure great advantage and is more effective.

Each application running on the server has one process. A process is the carrier of an application and used to hold the threads that actually execute the application. From the user's perspective, a process can be seen without any special tools. Processes act like containers on other application components. It holds virtual storage space, Data, system resources, and application settings. Although the thread can allocate, re-allocate, and release the memory, the process accepts the initial memory allocation and distributes it to all the threads in the request memory. A thread is a single execution path that contains all process resources shared within a process. It also contains the variables and other data stored in the memory of the stack), CPU register status information, so the thread can restore its environment) and a logon entry in the execution list of the system scheduler. Each thread specifies a job that the task application needs to do.

The main problem with using Task Manager is that no threads are visible. Task Manager is designed to treat applications as a whole at the process layer. Of course, you can also use performance monitor to monitor Oracle memory usage in Windows.

Start "system monitor" from "Start/Program/Management Tools" and select "performance ". Note: "performance" includes two MMC plug-ins: "system monitor" and "Performance Logs and changes ". In this case, some counters to be monitored are performance indicators for statistics on special objects in Windows 2000, such as the processor time required by a specific thread ). Click the "add" button, which looks like a plus sign in the figure. The "add counter" dialog box is displayed. First, select the "performance" object to be monitored. Select from the "performance" drop-down list that contains the "process" object and "Thread" object.

Process Viewer) is one of the methods that are easier to check threads and processes. You can find some Tools in Windows 2000 Support Tools or Windows 2000 Resource Kit. Windows 2000 Support Tools is part of Windows 2000, but is not automatically installed. The Windows Installer files that SUPPORT installation are in the % CDROM % \ SUPPORT \ TOOLS \ directory. Right-click 2000RKST. MSJ and select "Install" from the context menu.

Memory

The thread-based structure does have some memory restrictions, because a single process is composed of threads, and the address space of the process is limited, so there is little space to maneuver. Because Windows 2000 is still 32 as the operating system, the address space of a single process is limited to 4 GB, half of which are reserved by the operating system, the 2 GB system memory retained by the OS is also considered as the system address space, which includes OS Kernel encoding and hardware abstraction layer encoding HAL) and other structures that need to manage processes and OS interaction, the 2 GB system address space prohibits application process access.

Therefore, a single application process on a Windows 2000 standard server can use 2 GB of memory space. Start the file boot on the Windows 2000 Advanced Server. there is a/3 GB switch in ini, and this ratio is changed to 3 GB. This technology is called 4 GB tuning 4-gigabyte tuning, or 4GT). We will discuss this in detail.

The reserved memory is the memory allocated to the thread and reserved for future use, but there is no actually used memory. Because it is not actually used, it is still valid for other processes. However, because it has been allocated, it is still generated by the total memory limit on processes with threads. Therefore, the reserved memory computing is limited to 2 GB or 3 GB, the total memory reserved and used by the process cannot exceed this limit.

In addition to the physical memory installed in the system, Windows 2000 also uses virtual memory. This is actually the memory that resides on the hard disk. However, Windows 2000 makes it the same for applications as installing the memory on the machine. When an application block requires access to that memory, Windows 2000 copies another memory block to the disk and places the required memory in the physical memory, the size of these memory blocks is 4 kb. That is to say, each time an application requests memory, the memory is allocated to a 4 kb page. A file simulating memory on a disk is called a page scheduling file. Virtual Memory ManagerVMM Virtual Memory Manager) is a Virtual Memory component used to manage machines in the operating system. All memory access is through VMM. This means that every time the operating system requires Memory Page adjustment, a VMM request is required.

Memory optimization method:

1) use more than 4 GB memory
In addition, there is a way to allow a single process or application to allocate more than 32-bit address space memory, to achieve this, Windows 2000 uses physical address extension physical address extensions, or PAE ), in essence, PAE increases the address space from 32-bit to 36-bit, but you must have a Pentium Pro or an updated processor to enjoy this advantage. In Windows NT 4.0, Intel provides the iis36 driver to enjoy the advantage of all 36-bit address space, but the support for 36-bit address space in Windows 2000 Advanced Server has been established in the operating system, however, the application must use the Address window extension Address extenwing Extensions, or AWE) API to write the Address, Oracle9i Release Number 1 Release 1) does not support AWE, all Oracle 8i release numbers releases 8.1.5-8.1.7) Support AWE. Oracle implements AWE support in 9i Release 2 Release 2.

Ii) AWE and Windows 2000
AWE allows you to use any additional memory of more than 4 GB in the system. To reflect this advantage, you must have more than 4 GB of memory and must have a Pentium Pro or an updated processor, you must run Windows 2000 Advanced Server or Windows 2000 data center server without special drivers because Windows 2000 supports AWE.

To take advantage of this, you must use the/PAE switch in the boot. ini file when Starting Windows 2000. Make sure that the account running the Oracle service has the Lock Pages in Memory permission. After you grant the Lock Pages in Memory permission to the account running Oracle services, restart the OracleService.

3) AWE and Oracle
It can be determined that all Oracle8i release number versions and Oracle9i Release Number 2 only allow you to configure memory space that exceeds 4 GB for the database block buffer. Therefore, to release the memory of the standard process address space less than 3 GB for the user connection), PGA memory and different memory buffer pools that constitute the SGA.

In the initialization parameter file init. ora, set the parameter USE_INDIRECT_DATA_BUFFERS = TRUE. Without this parameter, Oracle cannot address an address space larger than 4 GB. Next we need to set the buffer pool size that determines the total memory usage, and set the DB_BLOCK_SIZE and DB_BLOCK_BUFFERS parameters.

In Oracle9i Release 2, The DB_BLOCK_BUFFERS parameter is replaced by the DB_CACHE_SIZE parameter, which changes the original number of specified buffer blocks to the specified number of buffer bytes. Similarly, it also explains that multiple database block sizes are supported in a database with Oracle9i Release number 2. Either way, if you set the parameter USE_INDIRECT_BUFFERS = TRUE, you can only define and use a single database block size and block buffer, as in the release number before 9i). Therefore, if the default database block size is 4 k, 8 k, or other, it is not allowed to set DB_2k_CACHE_SIZE.

Next, you need to set the appropriate AWE_WINDOW_MEMORY parameter value for ORACLE_HOME in the registry, that is, in HKEY_LOCAL_MACHINE \ Software \ Oracle \ HOME0, this parameter specifies the number of bytes. If not set, the default value is 1 GB. The size of this parameter depends on the size of the buffer to be set and is considered as the regular memory from the 3 GB process address space. Taking the buffer size as 6 GB as an example, set AWE_WINDOW_MEMORY to the default value of 1 GB. You want 1 GB to be treated as the regular memory, and the remaining 5 GB of the buffer comes from the address space larger than 4 GB. You want more buffer pools to be retained in the regular address space as much as possible, because access to buffer pools larger than 4 GB is slower than access to the virtual address space buffer pool, although still faster than disk I/O operations ).

4) Solve AWE-related memory problems
Memory Requirements of worker processes, including coding, other SGA components, pgamemory, and the general 3 GB virtual address space of each user connecting to the oracle.exe process.

Make sure that you have enough physical memory to process DB_BLOCK_BUFFERS outside of AWE_WINDOW_MEMORY. In our example, we define that the buffer pool size is 6 GB and 1 GB is from the regular address space, the remaining 5 GB comes from the system and process address space valid for the entire process other than 4 GB. Therefore, this example can only work on machines with at least 9 GB of memory, you should also reserve some space for other processes. Only one process can access the additional memory at a certain time.

As mentioned above, the/PAE switch is only used when the system has more than 4 GB physical memory. However, if the system memory is less than 4 GB, it can also be imitated. Set the MAXMEM parameter value in the boot. ini file. In the example below, set it to 2 GB, which means that any memory larger than 2 GB will be reserved as AWE memory.
Multi (0) disk (0) rdisk (0) partition (1) \ WINNT = "Microsoft Windows 2000 Advanced
Server "/fastdetect/PAE/MAXMEM: 2048

Use multiple processes attached to a database

Real Applications Clusters, RAC) provides the ability to run multiple instances and access the same data. Normally, this is used for projects with two or more nodes, and one instance runs on each node. In any case, it supports two instances running on a node in Oracle9i to access the same database. This can overcome the memory limit of each process and provide some other benefits, such as application failure detection.