Oracle consistent read Summary

What is logical read? The process in which the cpu reads these blocks in the memory is called the logical read (consistentget). The IO generated during the reading process is the logical IO.

What is logical read? The process in which the cpu reads these blocks in the memory is called the consistent get. The IO generated during the reading process is the logical IO.

Consistent reads are generated when SQL statements are executed.

What is logical read? The process in which the cpu reads these blocks in the memory is called the consistent get. The IO generated during the reading process is the logical IO. cpu resources are greatly consumed during logical reading. therefore, the fewer logical reads, the better. for SQL optimization, you must adjust the SQL statements with large buffer get values.

Logical reads = consistent gets + db block gets. logical read is actually the sum of db block gets and consistents Gets.

DB block gets --- number of blocks requested currently

Consistent gets --- data block required for data consistency read of the total number of data requests in the buffer of the rollback segment

Physical Reads --- number of data blocks read from the disk (main reason: the buffer does not contain these blocks; full table scan; disk sorting)

For the entire system, we use the AWR report as an example.

1. The first thing to note is the highest Elapsed Time. The longest execution Time usually means that more resources are consumed (whether physical or logical read)

2. The second concern is the SQL statement with the largest Buffer Gets, which is the SQL statement with the largest DB block gets + Consistent gets. A large amount of unnecessary buffer gets will cause CPU time to rise. In a healthy system with low load and not primarily computing, top events with the CPU time at the Top of the list usually require attention.

3. second, focus on Physical Reads, which is not to say that it is not easy to adjust Physical Reads. In fact, Physical Reads are usually adjusted by basic optimization methods, if an OLTP system has a large number of physical reads, it should have been suspended for a long time. This is an obvious performance problem and will be adjusted first.

There are two ways to read data from Oracle:

1. Physical reading is the process of reading data from the disk to buffer catch.

Generally, if you find that the data does not exist in buffer catch when you need the data, that is, oracle performs physical read.

2. Logical reading

Logical read refers to reading data blocks from (or from the View) Buffer Cache. Different data block access modes can be divided into real-time Read and Consistent Read ).

(1) Instant read db block gets

Real-time reading refers to reading the latest data of the Data Block. At any time, there is only one copy of the current data block in the Buffer Cache. Real-time reading usually occurs when you modify or delete data. At this time, the process will add a row lock to the data and identify the data as "dirty" data.

(2) consistent read consistent get

Oracle is a multi-user system. Before a session starts to read data, it may modify the data to be read by another session. If the session reads the modified data, data inconsistency may occur.

Consistent reading is to ensure data consistency. The data block in the Buffer Cache has the SCN of the last data block modification.

2. Why consistent reading? To maintain data consistency

If a transaction needs to modify the data in the data block, it will first save a copy of the data block before the modification and the data block of the SCN In the rollback segment, then, update the data block and Its SCN in the Buffer Cache, and identify it as "dirty" data.

When other processes read data blocks, the SCN on the data blocks and the process's own SCN are compared first. If the SCN on the data block is smaller than or equal to the SCN of the process itself, the data on the data block is directly read;

If the SCN on the data block is greater than the SCN of the process itself, the data block read data before modification will be found from the rollback segment. Generally, common queries are consistent reads.