Optimize and adjust the Oracle 8i Database

Guidance:
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:
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
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
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
Swap
The swap Command reports information about swap space usage. Insufficient swap space can cause system suspension and slow the response time.
Mpstat
The mpstat Command reports statistics for each processor.
Adjust Memory Management
Allocate enough swap space
Memory switching (swapping) can cause a large memory overhead and 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 applications Program Process; reduce the number of database buffers to release some memory; reduce the number of UNIX file buffers (especially when using the original device ). 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.
Control Paging
Paging in memory may not be as serious as switching, because in order to run, the entire application does not have to be fully stored in 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.
Adjust dbwr to increase write bandwidth
Oracle provides the following methods to prevent dbwr (Database write process) activity 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.
Using an I/O slave (slave) is a dedicated process and 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 and 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 parameter controls the I/O-dependent behavior, disk_asynch_io and tape_asynch_io allow the asynchronous I/O to be disabled for disks and tape devices respectively (because the default I/O dependency for each process type is 0, unless explicitly set, otherwise, 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.
. Use iostat to find the maximum number of I/O requests waiting for service on a disk device in the Request queue of a large disk. 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.
Select the appropriate file system type. You can select a file system for sun or 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, or even different UFS file systems are difficult to compare (because the execution is different), 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:
Bread/s and bwrit/S: block read and block write. They are very important to the file system.
Pread/s, pwrit/S: partition read and write, they 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
Keep all oracle users and processes on the same priority in Oracle, and all users and background process operations on the same priority. Modifying priority has an unexpected impact on competition and response time. For example, if lgwr (a log Writing Process) gets 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.
Use the processor affinity/bundle in a multi-processor system, and use the processor affinity/bundle (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 ).
Is Export (export)/import (import) and SQL