High theory of Oracle Buffer Optimization

Welcome to the Oracle community forum and interact with 2 million technical staff. I like Oracle very much. I also like to sum up my experience and lessons on Oracle Buffer Optimization, the following is a detailed description of the Oracle optimized buffer. (1) obtain the buffer activity. To optimize the buffer for Oracle, first understand the activity of the buffer.

Welcome to the Oracle community forum and interact with 2 million technical staff> I like Oracle very much. I also like to sum up my experience and lessons on optimizing the Oracle buffer zone at work, the following is a detailed description of the Oracle optimized buffer. (1) obtain the buffer activity. To optimize the buffer for Oracle, first understand the activity of the buffer.

Welcome to the Oracle community forum and interact with 2 million technical staff> enter

I like Oracle very much. I also like to sum up the experience and lessons of optimizing the Oracle buffer zone in my work. Let's talk about this question in detail.

(1) obtain the buffer Activity

To optimize the buffer zone for Oracle, we should first understand the activity of the buffer zone. This can be obtained by querying a dynamic performance TABLE (with the select any table Privilege required) V $ SYSSTAT.

1. SVRMGR> select name, value from v $ sysstat

2.2> where name in ('db block gets', 'consistent gets', 'Physical reads ');

3. NAME VALUE

4. db blockgets 3437

5. consistent gets 30500

6. physica reads 1963

7.3 rows selected.

The values of db block gets and consistent gets are the total number of reads in the request data buffer. "Physical reads" indicates the number of times files are read from the disk when data is requested.

(2) buffer hit rate

The possibility of reading from a buffer zone is called the buffer hit rate. It can be calculated using the following formula: Hot Ratio = 1-(physical reads/(db block gets + consistent gets)

The higher the buffer hit rate, the faster the speed. If the hit rate is lower than 60% or 70%. Add a buffer (DB_BLOCK_BUFFERS) to improve performance. According to the formula, we can calculate the Hot Ratio = 1-(1963/(3437 + 30500) = 92% in this example. If the buffer hit rate is high and you want to reduce the buffer while maintaining good performance, you can reduce the value of DB_BLOCK_BUFFERS. The minimum value is 4.

When the input/output (I/O) requests of a file interfere with the input/output requests of the Second file, the two files will compete for use. Therefore, two random files can be stored on the same device only when they are not accessible at the same time. There are two types of I/O contention: parallel I/O contention and interference contention. Parallel I/O contention occurs when multiple accesses are requested to the same device at the same time. This type of contention can be eliminated by splitting the Index associated with the table. Interference contention occurs when a write operation is performed on an ordered file while a READ command is executed.

I/O optimization aims to solve the I/O bottleneck. I/O optimization must be performed after memory optimization, i/O can be optimized by reducing disk contention, effectively allocating data block space, and avoiding dynamic space management.

Reduce disk contention

The V $ FILESTAT view provides physical read/write information. Physical I/O for disks and logical I/O for memory. If multiple processes access the same-disk at the same time, a disk becomes a competition.

Monitor Oracle disk Activity

To solve the disk contention, you should first know the disk activity and determine whether the competition has occurred. This can be determined by querying the dynamic performance TABLE (with the select any table Privilege required) V $ FILESTAT and V $ DATAFILE. For example:

1. SQL> SELECT Name, phyrds, phywrts

2. from v $ DATAFILE df, V $ FILESTAT fs

3. WHERE df. file # = fs. file #;

Where:

◆ Phyrds: records the number of times each database file is read from the disk.

◆ Phywrts: records the number of times database files are written to the disk.

The total number of I/O operations on a disk is the sum of phyrds and phywrts of all database files on the disk. The total amount of physical reads and writes must be controlled within the optimal limits of the user's hardware and operating system. The preceding section describes how to optimize the Oracle buffer.