Optimizing Oracle9i performance under Windows 2000

oracle|window| Performance | Optimizing Oracle Performance Optimization under Windows 2000 requires consideration of disk I/O, CPU, network subsystem, memory subsystem, which focuses on Oracle memory tuning in Windows 2000 environments.

Optimizing Disk Configuration

Oracle is a strong disk I/O application to ensure that you properly configure the disk and file system:

Run Disk Defragmenter first before you set up data files on disk

To securely defragment the disk, close the instance of the Open data file and stop the service. If you have enough contiguous disk space to create data files, it's easy to avoid data files from fragmentation.

Do not use disk compression

The Oracle data file does not support disk compression.

Do not use disk encryption

Encryption, like disk compression, adds a processing layer that reduces disk read and write speed. If you are concerned that your data may leak, use the Dbms_obfuscation package and the label security to selectively encrypt the sensitive part of the data.

Do not use more than 70% of disk space

The remaining disk space holds system temporary data and holds intermediate data as Disk Defragmenter.
　　
Using raid

Select hardware RAID over software RAID;

With a hardware RAID controller;

Log files should not be placed on a RAID 5 volume because of the high performance of RAID 5 read and poor write performance.

A disk control system that separates log files and archive logs from control files and data files.

Detach a paging file to multiple disk physical volumes

Create two paging files across at least two disks. You can create four paging files and benefit from performance, ensuring that all paging files are at least twice times the size of physical memory.

Optimizing CPU Usage and configuration

Remove screen protection

Screen saver absorbs a large amount of CPU resources and provides a meaningless use of the database server, especially to prohibit 3GL screen protection, and use "empty screen" to reduce CPU usage if you have to use screen saver.

To configure the system as an application server

Running system in Control Panel, setting performance options to background programs in the Advanced tab, provides priority to applications, like Oracle, against users starting a clumsy program in the graphical user interface.

Monitor hardware that is consuming interrupts in the system

Hardware that consumes CPU interrupts and time should be avoided. Usually such hardware is cheap because it loads the work into the CPU, and the CPU is going to handle the hardware of the peripheral advanced performance, which usually requires attention:

1, support the Bus control network card

2. Disk controllers that support DMA and do not support PIO

Use Performance Monitor to track the%interrupt time counter value of the Processor object, and the baseline and standard for this counter, and then monitor the problem.

3. A good way to avoid interruptions is to use a hardware RAID controller instead of the software RAID supported by Windows 2000.

Maintain minimum security audit record

Running Oracle on a dedicated server

Oracle is a large memory consumer and do not run an Oracle database on a system that performs the following functions:

1, primary domain or backup domain controller (known as domain Controller under Windows 2000)

2, File server

3. Print server
　
4. Remote Access server

5, routers, agents or firewalls

Do not use fancy wallpapers (if used, try to reduce the size of the wallpaper file

Prohibit services that are not required

It is best to prohibit the services that are not required in the system, and if some service functions are required, set the startup type to Manual, and to do this first verify the actual service requirements with the network administrator:

1, if your system does not need a printer, usually stop this service and set to manual;

2. Stop License Logging service unless you have special requirements for it;

3, should not use the DHCP service, and prohibit it;

4, do not automatically start the program you do not need;

Check the contents of the menu "Start/Program/startup" to remove unwanted programs.

Optimizing Network Configuration

Network configuration is an important aspect of performance tuning and it is easy to hide performance bottlenecks.

Configure the network card to use the fastest speed and effective mode

For automatic detection, most default installations are NICs, and if this is optional, try to adjust to full duplex and maximize line speed.

Remove unwanted network protocols

Only the TCP/IP protocol is retained.

Optimizing Network Protocol binding Order

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

Prohibit or optimize file sharing for Oracle

Ideally, you should disable file sharing to minimize security leaks and network traffic, but if you need to use file and print sharing, configure the connection properties for each network adapter in your system and set "maximize network application Data throughput"

Memory tuning

Memory optimization is a key setting for Windows platform, first to understand the Oracle architecture of the Windows 2000 platform:
　
Thread-based architecture

Windows2000 is a thread-based structure, whereas the UNIX operating system is a process-based architecture. This means that more applications in Windows, including Oracle, are executed in the form of a single process with multiple threads, and this thread-based architecture does give Window2000 an advantage-easier to share memory. Memory space is allocated for each process, and shared memory between processes is clumsy, and with additional encoding, the thread is a subset of the process and uses much less memory than the process. All threads of a particular process share the same process memory space, and shared memory between the threads of the same process is faster than the shared memory of different processes, which gives a great advantage to the thread-based structure and is more efficient.

Each application running on the server has one and only one process. A process is the carrier of an application and is used to hold threads that perform the actual work of the application. From a user's perspective, a process is a component that can be seen without any specialized tools. Processes work with other application components just like containers. It holds virtual storage space, data, system resources, and application settings. Although the thread can allocate, reassign, and free memory, the process accepts the initial memory allocation and assigns it to all threads that request memory. A thread is a single execution path that contains all process resources that are shared within a process. It also contains stacks (variables and other data stored in memory), state information for the CPU registers (so that the thread can recover its environment), and a login entry in the execution list of the system scheduler. Each thread prescribes some kind of work that needs to be done to complete the task application.

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

Select performance from Start/program/Administrative Tools to start System Monitor. Note that performance includes two MMC Plug-ins: System Monitor and Performance logs and changes. At this point, you need some monitored counters (counters are performance indicators that are used to count Windows 2000 special objects, such as counting the processor time required by a specific thread). Click the Add button (which looks like a plus sign in the diagram) and you will see the Add Counters dialog box. You first need to select the performance object you want to monitor. Select in the Performance Drop-down list that contains the process object and the thread object.

The process Viewer is one of the more easy ways to check threads and processes. You can find tools within Windows Support Tools or Windows Resource Kit. Windows Support Tools is part of Windows 2000, but is not installed automatically. Windows Installer files that support installation are in the%cdrom%\support\tools\ directory. Just right click on the 2000RKST.MSJ and choose Install from the context menu.

Memory

The thread-based structure does have some memory limitations, because a single process consists of threads, and the process's address space is limited, so very little space is mobile. Because Windows 2000 is still 32 for the operating system, a single process address space is limited to 4GB, half of which is reserved by the operating system, and the 2GB OS-reserved system memory is also considered the system address space, including OS kernel encoding, Hardware Abstraction Layer Encoding (HAL) and different structures that need to manage processes and OS interactions, this 2GB system address space is forbidden for application processes to access. Therefore, the Windows 2000 Standard Server single application process can use a total of 2GB of memory space. There is a/3GB switch in the Windows 2000 Advanced Server startup file boot.ini that changes this to 3GB, which is called 4GB tuning (4-gigabyte tuning, or 4GT) and we will discuss this issue in detail.

The reserved memory is memory that is allocated to the thread and is reserved for future use, but is not actually used. Because it is not actually used, it is still valid for other processes. However, because it is already assigned, it is still generated by the total memory limit on the process that owns the thread, so the calculation of reserved memory is limited to 2GB or 3GB, and the sum of the memory that the process retains and uses cannot exceed this limit.

In addition to the physical memory installed in the system, Windows 2000 uses virtual memory. This is actually the memory that resides on the hard disk. But Windows 2000 makes it as much of an application as it is installed on a machine. When an application block requires access to that memory, Windows 2000 copies the additional memory blocks to disk and places the required memory in physical memory, which is 4KB. That is, each time the application asks for memory, the memory is allocated to the 4KB page. A file that simulates memory on disk is called a paging file. Virtual Memory Manager (VMM Virtual Memory manager) is an operating system management machine that is part of a dummy memory component. All memory accesses are through VMM. This means that the VMM request is made whenever the operating system needs to make a memory paging.

Memory Tuning Method:

(i) Use of more than 4GB of memory

In addition, there is a way to allow memory to be allocated over 32-bit address space for a single process or application, and for this to happen, Windows 2000 uses a physical Address extension (physical addressing extensions, or PAE), PAE essentially increases the address space from 32 bits to 36 bits, but it must have Pentium Pro or newer processors to enjoy this advantage. Under Windows NT 4.0, Intel offers the PSE36 driver The advantage of all 36-bit address space, but support for the 36-bit address space in Windows Advanced Server has been built into the operating system, however, The application must be written with an address window extension (addressing windowing Extensions, or AWE) API, Oracle9i issue 1 (Release 1) does not support AWE, and all Oracle 8i release numbers (releases 8.1.5 –8.1.7) all support awe. Oracle supports AWE in 9i distribution number 2 (Release 2).

(ii) AWE and Windows 2000

AWE allows you to use any additional 4GB of RAM in the system, to reflect this advantage, you must have more than 4GB of RAM, must have Pentium Pro or newer processors, must be running Windows 2000 Advanced servers or Windows 2000 Data center servers, No special drivers are required because Windows 2000 already supports AWE.

To take advantage of this advantage, you must use the/PAE switch in the Boot.ini file when you start the Windows 2000 machine, and you must ensure that the account running the Oracle service has the lock Pages in memory permission. To restart the Oracleservice service after adding lock Pages in memory permissions to the account running Oracle Services.

(iii) AWE and Oracle

You can determine that all oracle8i release and oracle9i Release 2 allow you to configure more than 4GB of memory space for the database block buffers, so that you want to connect the user with memory that releases the standard process address space (memory below the 3GB limit), PGA memory and the different memory buffer pools that comprise the SGA.

In the initialization parameter file Init.ora to set the parameter use_indirect_data_buffers=true, without this parameter, Oracle cannot address to more than 4GB of addressing space. Next, set the buffer pool size that determines the total memory usage, and set the Db_block_size and db_block_buffers two parameters.

In Oracle9i Release 2, the parameter db_block_buffers is replaced by the parameter db_cache_size, which changes the number of buffer blocks originally specified to the specified buffer byte. Also explains the support for multiple database block sizes in a database in Oracle9i Release 2. Either way, if you set the parameter use_indirect_buffers=true, you will only be able to define and use a single database block size and block buffer (as in a 9i release number), so if the default database block size is 4k, 8k, or otherwise, set the Db_2k_ Cache_size is not allowed.

Next, you need to set the appropriate awe_window_memory parameter values for Oracle_home in the registry, which means that, under 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 how much buffer size you want to set and treats as normal memory from the 3GB process address space. For example, with a buffer size of 6 GB, set Awe_window_memory to the default value of 1GB, you want 1GB to be considered regular memory, and the remaining 5GB buffers are from the address space above the 4GB limit. You want more buffer pools to remain in the regular address space as much as possible, because accessing more than 4GB of buffer pools is slower than accessing the virtual address space buffer pool (although still faster than disk I/O operations).

(iv) Addressing AWE-related memory issues

It is important to note that the block buffers above each 4GB boundary need to retain approximately 200 bytes of buffer headers in the regular address space, so in the above example we have about 312,000 buffer headers that point to the buffer in the extended address space, and the buffer header accounts for approximately 80MB normal memory space, if the database block is small , this number will be quite high, so you must ensure that the memory requirements of these buffer headers, awe_window_memory, and all Oracle.exe processes, including coding, SGA other components, PGA memory and each user connection stack are suitable for the regular 3GB virtual address space of the Oracle.exe process.

Verify that you have enough physical memory to handle more than Awe_window_memory db_block_buffers, in our example, define the buffer pool size to be 6GB,1GB from the regular address space, The remaining 5GB is an effective system and process address space for the entire process from outside 4GB, so this example can only work on machines with at least 9GB memory, and you should also reserve some space for other processes, and only one process can access additional memory at some point.

As mentioned earlier, the/PAE switch is only used when the system has more than 4GB of physical memory, but it can also be emulated if the system has less than 4GB of memory. Set the value of the MAXMEM parameter in the Boot.ini file, as in the following example, set to 2GB, which means that any 2GB or more memory will remain as AWE memory.

multi (0) disk (0) rdisk (0) partition (1) \winnt= "Microsoft Windows Watts Advanced Server"/fastdetect/pae/maxmem:2048

Use additional processes for a database

A true application cluster (real applications Clusters,rac) provides the ability to run and access the same data with multiple instances. Typically, this is used for projects with two or more nodes, one instance running on each node. In any case, it supports two instances in oracle9i to run on one node to access the same database. This overcomes the memory limitations of each process and provides some other benefits, such as application failure detection.