Optimize and adjust the Oracle 8i Database

Oracle 8i Database Server is a highly optimizable software product. Regular adjustments can optimize system performance and prevent data bottlenecks. By adjusting the database system, we can achieve optimal performance to meet user needs. Below, I will introduce some commands and methods to optimize and adjust the Oracle 8i database server on the Sun's iSCSI Solaris system platform.

Solaris performance monitoring command

Solaris provides performance monitoring commands to monitor database performance and determine database requirements. In addition to statistics for Oracle processes, they also provide CPU usage statistics and interrupt, swap, paging, and context conversion functions for the entire system. Monitoring Commands include:
1. vmstat
The vmstat Command reports the activities of processes, virtual memory, disks, pages, and CPUs on Solaris. The following command will display the summary of what the system does every five seconds:
% Vmstat 5

2. sar
The sar command is used to monitor swap, paging, disk, and CPU activity. The following command is used to display the summary of 10 paging activities every 10 seconds:
$ Sar-p 10 10

3. iostat
The iostat Command reports terminal and disk activities. This report shows which disks are busy. This information is useful when balancing I/O load ). The following command is used to display 5 terminal and disk activities every 5 seconds:
$ Iostat 5 5

4. swap
The swap Command reports information about swap space usage. Insufficient swap space can cause system suspension and slow the response time.

5. mpstat
The mpstat Command reports statistics for each processor.

Adjust Memory Management

1. allocate enough swap space
Memory swap swapping) can cause a large memory overhead, it should be minimized. Use the sar-w or vmstat-S command on Solaris to check the exchange. If the system is switching and memory saving is required, the following measures should be taken:

Avoid running unnecessary system daemon processes or application processes;
Reduce the number of database buffers to release some memory;
Reduce the number of UNIX file buffers, especially when the original device is used ).
On Solaris, run the swap-l command to determine the number of swap space in use. Use the swap-a command to add a swap zone to the system. Start the database with two to four times the system RAM swap space. If you want to use Oracle Developer, Oracle Applications, or Oracle InterOffice, a higher value is used. Monitor the use of swap space and add it if necessary.

2. Control Paging
Memory paging) may not be as serious as SWAP, because in order to run, the entire application does not have to be fully stored in the memory. A small number of pages cannot significantly affect the system performance. To detect too many pages, run the measurement during fast response or idle time and compare it with the measurement during slow response. Use vmstat or sar-p monitoring pagination.

If the system has too many page activities, consider the following solutions:

Install more memory;
Move some work to another system;
Configure the system core to use less memory;
Keep the SGA in a single shared memory segment.
If there is not enough shared memory, the database cannot be started. At this time, we can reconfigure the UNIX core to increase the shared memory.

Adjust disk I/O

I/O bottlenecks are the easiest performance problems to identify. Balance I/O evenly across all available disks to reduce disk access time. For smaller databases and databases that do not use the parallel query option, ensure that different data files and tablespaces are distributed across available disks.

1. Adjust DBWR to increase write bandwidth
Oracle provides the following methods to prevent DBWR database write processes from becoming a bottleneck:
Asynchronous I/O allows the process to continue to process the next operation, without having to wait until the write is sent, minimizing the idle time, thus improving the system performance. Solaris supports asynchronous I/O of raw devices and file system data files.

Use I/O slave) is a dedicated process, its only function is to execute I/O. They replace multiple DBWR features of Oracle 7. In fact, they are a summary of multiple DRWR, which can be distributed by other processes ). Regardless of whether asynchronous I/O is available, they can be operated. If they are set, they are allocated from LARGE_POOL_SIZE; otherwise, they are allocated from the shared memory buffer.

The initialization parameters control the I/O subordination. DISK_ASYNCH_IO and TAPE_ASYNCH_IO allow the disk and tape devices to disable asynchronous I/O, because the default I/O subordination for each process type is 0, unless explicitly set, no I/O slave is released ).

If DISK_ASYNCH_IO or TAPE_ASYNCH_IO is invalid, DBWR_IO_SLAVES should be set to greater than 0; otherwise, DBWR will become a bottleneck. In this case, the optimal value of DBWR_IO_SLAVES on Solaris is 4, while in the case of LGWR_IO_SLAVES, there should be no more than 9 slave posts.

DB_WRITER_PROCESSES replaces the DB_WRITERS parameter of Oracle 7 and specifies the initial number of database write processes for an instance. If DBWR_IO_SLAVES is used, only one database write process is used, regardless of DB_WRITER_PROCESSES settings.

2. Use IOSTAT to find a large disk Request queue
The request queue shows the number of I/O requests waiting for service on a specific disk device. The request queue is caused by a large number of disk I/O or by the average query time I/O. The disk Request queue should be 0 or close to 0.

3. select an appropriate file system type
You can select a file system for Sun iSCSI Solaris. File systems have different features. The technologies used to access data have a substantial impact on database performance. File systems generally include:

. S5: UNIX System V file system;
. Ufs: UNIX File System Derived from bsd unix );
. Vxfs: Veritas File System;
. Original device: No file system.

There is usually no proof that the file system matches the file system, and even different ufs file systems are difficult to compare because of different execution), although ufs is usually a high-performance choice, however, the performance varies greatly depending on the selected file system.

Monitor disk Performance

You can use sar-B and sar-u to monitor disk performance.

The importance of sar-B on disk performance is as follows:
1) bread/s and bwrit/s: block read and block write, which are very important to the file system.
2) pread/s and pwrit/s: partition read and write, which are very important to the original partition database system.
The Oracle block size should match the disk block size or the multiple of the disk block size. If possible, perform a file system check on the partition before the database file system is used. then create a new file system to ensure that it is clean and non-broken. Evenly distribute disk I/O as much as possible and separate database files from log files.

Adjust CPU usage

1. Maintain all Oracle users and processes with the same priority
In Oracle, all user and background process operations are performed at the same priority. Modifying priority has an unexpected impact on competition and response time.
For example, if the LGWR log writing process obtains a low priority, it cannot be fully executed, and LGWR becomes a bottleneck. On the other hand, if LGWR has a high priority, user processes may have to endure bad response times.

2. Use processor affinity/bundle on a multi-processor system
In a multi-processor environment, use processor affinity/Bind affinity/binding if it is available in the system ). The processor bundle prohibits a process from moving from one CPU to another, allowing the information in the CPU high-speed cache to be better utilized, and the server process can be bundled to make full use of the high-speed cache because it is always active, allows background processes to flow between CPUs ).

3. Export for Export)/Import) and SQLLoader use a single task Link
To transmit a large amount of data between the user and Oracle 8i, such as using Export/Import), it is very efficient to use a single task structure, because as a single task link to the Oracle executable program, allows a user process to directly access the entire SGA. However, running a single task requires more memory. To use single-task Import and Export and SQLLoader (sqlldrst) executable programs, we can call the make file ins_rdbms.mk in the $ ORACLE_HOME/rdbms/lib directory ).

The following executable programs are used to import and export a single task and SQLLoader (sqlldrst:
% Cd $ ORACLE_HOME/rdbms/lib
% Make-f ins_utilities.mk singletask

Adjust the block size and file size
On Solaris, the default value of the Oracle block is 2 kb. You can set the actual size to a multiple of 2 kb, and the maximum value is 16 kb.

The optimal block size is usually the default value, but changes with the application. To create a database with different Oracle block sizes, add the following line to the initsid. ora file before creating the database: db_block_size = new_block_size

Adjust the speed cache size of the Solaris Buffer

To make full use of the advantages of the original device, we need to adjust the size of the Oracle 8i buffer cache and the Solaris buffer cache.
The Solaris buffer cache is provided by the operating system. It stores data blocks in the memory before they are transferred from the memory to the disk, and vice versa ).

The Oracle 8i buffer high-speed cache is the region where the Oracle database buffer is stored in the memory. Because Oracle 8i can use the original device, it does not need to use the Solaris buffer cache.

Increase the cache size of the Oracle 8i buffer when moving the original device. If the number of memory in the system is limited, the cache size of the Solaris Buffer is reduced accordingly.

The Solaris Command sar can help determine which buffer caches should increase or decrease:

Sar-B: reports the activity of the Solaris buffer cache;
Sar-w: Reports Solaris memory SWAp activities;
Sar-u: Reports CPU utilization;
Sar-r: Reports memory utilization;
Sar-p: Reports Solaris memory paging activity.

When the cache hit rate increases, we need to increase the cache size of the Oracle 8i buffer. When the SWAP/paging activity increases, we need to reduce the cache size.

We can also use the Trace and alert Alter files created by Oracle 8i to diagnose and solve running problems.
As long as we make full use of the above commands and methods, we can optimize and adjust the Oracle 8i database.