The process of reading and modifying data blocks in Oracle

The process of reading and modifying data blocks in Oracle

Oracle databases process SQL statements in three steps: parsing (parse), executing (exec), and returning results (fetch ). For ease of understanding, we first omit the SQL processing process and directly introduce the reading or modification process of data blocks.

Concepts of physical read and logical read

1. HASH the block address to be accessed

HASH (FILE #, BLOCK #) = hash_value (HV)

2. Locate the corresponding HASH bucket based on HV

3. Search for the HASH chain List in the bucket and compare the buffer_header (BH) on the chain ).

Logical read

1. Find BH and read (buffer_address) BA in BH

2. Access the target buffer in buffer_cache Based on BA.

Physical read

1. I searched for the target BH ON THE hash_chain.

2. Load the block from DBF to the buffer.

3. link the buffer header to the hash chain in the corresponding BUCKET.

4. Access buffer.

Latch and pin

Buffer in buffer_cache is only public resources for each server_process. Anyone can modify the buffer to ensure that these links are not modified concurrently. Oracle uses latch and buffer_pin locks to control the concurrency of memory structures.

CBC latch

When a server process searches for BH on the chain, the other process performs the inbound and outbound chain operations. For a buffer, both processes need to perform the inbound and outbound chain at the same time, and there is no security guarantee in the memory. To ensure that these links are not modified concurrently

For example, when I search for buffer_header on the linked list, you cannot change the linked list. When I change the linked list, you cannot

Oracle must protect hash_chain. The protection method adopted by oracle is latch, specifically: cache buffer chain latch, referred to as: CBC latch.

Buffer pin

When the target BH is found on the hash_chain, You need to lock the BH according to the access method to access the buffer according to (buffer_address) BA. The lock on buffer_header is called the buffer pin.

Sharing and exclusive

Both CBC latch and buffer pin have lock holding modes. They all have a shared and exclusive mode. For public resources, an exclusive mode means competition.

When to share and hold CBC latch:

1. Read the root block + branch block of a non-unique index

Note: exclusive leaf blocks are used to read non-unique indexes.

2. When accessing through a unique index, the index's root block + branch block + leaf block + Table Block

Cause: the access frequency of the root block and branch block is high, and few changes are made.

When to exclusively hold CBC latch:

1. All operations involving changes.

2. As well as all read operations other than share holdings listed above.

Whether the index unique scan and index range scan can be UNIQUE or not through the above comparison.

What is the purpose of obtaining CBC latch?

1. Search for BH using a two-way linked list on the hash chain

2. Modify the buffer pin lock status in BH

When to share the buffer pin: Read the buffer through the BA in BH

Buffer pin exclusive hold: write buffer through BA in BH

When the buffer pin does not need to be held:

1. Read (split) of all root and branch block changes that do not involve Indexes)

2. Unique index leaf block + Table Block

Queue on BH:

Users list: list of buffer-related processes. Operation in compatible mode

The waiter list is not compatible with the access mode and is waiting for users in the queue.

We will further introduce the relationship between Hash Latches, HashBucket, buffer Header, and Buffer cache.

The process for Oracle to read or modify data blocks:

I. (read operation) Non-unique index method, non-index root block and branch block

1. HASH the block address to be accessed

HASH (FILE #, BLOCK #) = hash_value (HV)

2. Locate the corresponding HASH bucket based on HV

3. Apply for the CBC latch for the bucket in an exclusive manner

4. Search for the HASH chain List in the bucket, compare the buffer_header (BH) on the chain, and find the target BH.

5. Modify the buffer pin lock in BH to the S (share) mode (previously 0, no lock)

6. Release CBC latch.

7. Find the buffer Based on the BA pointer in BH

8. Read the data in the buffer to the PGA.

9. Apply for the CBC latch for the bucket in an exclusive manner

10. Modify the buffer pin lock in BH to 0 (no lock) mode.

11. Release CBC latch

II. (read operation) unique index method all blocks, non-unique index root blocks and branch blocks

1. HASH the block address to be accessed

HASH (FILE #, BLOCK #) = hash_value (HV)

2. Locate the corresponding HASH bucket based on HV

3. Apply for the CBC latch for this bucket as a shared object

4. Search for the HASH chain List in the bucket, compare the buffer_header (BH) on the chain, and find the target BH.

5. Find the buffer Based on the BA pointer in BH

6. Read the data in the buffer to the PGA.

7. Release CBC latch

III. (write operation)

1. HASH the block address to be accessed

HASH (FILE #, BLOCK #) = hash_value (HV)

2. Locate the corresponding HASH bucket based on HV

3. Apply for the CBC latch for the bucket in an exclusive manner

4. Search for the HASH chain List in the bucket, compare the buffer_header (BH) on the chain, and find the target BH.

5. Modify the buffer pin lock in BH to the X (exclusive) mode (previously 0, no lock)

6. Release CBC latch

7. Find the buffer Based on the BA pointer in BH

8. Generate redo data

8. Modify the data in the buffer.

9. Apply for the CBC latch for the bucket in an exclusive manner.

10. Modify the buffer pin lock in BH to the 0 mode.

11. Release CBC latch.