Physical Reads (Physical Reads)

Physical reads occur when data blocks are read from disk into memory. This happens when the required data block are not present in the SGA's cache buffer. Operations like full table scans or disk sorting may also trigger physical reads due to the large volume of data blocks involved.

Logical Reads (Logical Reads)

Logical reads represent the number of data blocks read from memory. It can be calculated as:

logical reads = db block gets + consistent gets

Alternatively, logical reads can be categorized into current mode reads and consistent reads based on the access pattern.

Consistant Reads (Consistent Gets)

Oracle ensures data consistency in multi-user environments through consistent reads. Each data block in the buffer cache carries an SCN (System Change Number) of its last modification. When a transaction modifies a block, it first saves the original data and SCN in the rollback segment. If a process reads a block with a higher SCN than its own, it retrieves the original data from the rollback segment to maintain consistency.

Current Mode Reads (DB Block Gets)

Current mode reads fetch the most recent version of a data block. These typically occur during DML operations (INSERT, UPDATE, DELETE) where the process locks rows and marks data as "dirty." Current reads generate db block gets, while query operations produce consistent gets.

Example Enalysis

-- Create test table
CREATE TABLE sample_data AS SELECT * FROM dba_objects;

-- Enable tracing
ALTER SESSION SET sql_trace=true;
SET autotrace ON;

-- First execution
SELECT object_type, COUNT(1) FROM sample_data GROUP BY object_type;
-- Statistics: 0 db block gets, 1109 consistent gets, 1029 physical reads

-- Second execution
SELECT object_type, COUNT(1) FROM sample_data GROUP BY object_type;
-- Statistics: 0 db block gets, 1034 consistent gets, 0 physical reads

The physical reads decrease to zero in subsequent executions as data blocks remain in the buffer cache.

Calculating Consistent Reads

Consistent reads depend on factors like arraysize (fetch size). For example:

SET arraysize 15;
SELECT * FROM large_table;
-- Consistent gets: 5846

SET arraysize 50;
SELECT * FROM large_table;
-- Consistent gets: 2456

Special Case: Direct Path Reads

In some scenarios, physical reads may exceed logical reads due to direct path reads that bypass the buffer cache. This occurs during:

• Disk sorting
• Parallel query slave operations
• Read-ahead operations

Hot Table Scenario

When a table is frequently modified while being queried with SERIALIZABLE isolation, consistent reads increase significantly:

-- Session 1: Frequent updates
BEGIN
  FOR i IN 1..100000 LOOP
    UPDATE hot_table SET x=x+1;
    COMMIT;
  END LOOP;
END;
/

-- Session 2: Serializable query
ALTER SESSION SET isolation_level=serializable;
SELECT * FROM hot_table;
-- Consistent gets: 23681