Document Type | Technical Information
Category | Administration
Document Number | TADTI072
Overview
Reading Current Mode refers to the read mode that occurs when modifying actual data blocks or locking is required during SQL execution.
Note
Name in SQL trace: current
Name in tbSQL autotrace: db block gets
This occurs when reading the final value at the point of actual block access, not at the start of the SQL, and typically appears in the following situations.
- When performing DML or large sorts requiring disk sorting
- When executing SELECT FOR UPDATE statements
- When reading extent information of the target segment during a Table Full Scan
Method
DML Execution
Below is the result of executing a DML statement that increases the salary of employees in department number 10 by 100000 in the emp1 table.
SQL> set autot on SQL> Update emp1 Set sal = sal + 100000 Where deptno = 10; 50000 rows updated.
SQL ID: 7mcvyuswan8x3
Child number: 157
Plan hash value: 2110575394
Execution Plan
--------------------------------------------------------------------------------
1 UPDATE: EMP1 (Cost:101, %%CPU:0, Rows:49999)
2 INDEX (RANGE SCAN): IDX_EMP1_DEPTNO (Cost:101, %%CPU:0, Rows:49999)
Predicate Information
--------------------------------------------------------------------------------
2 - access: ("EMP1"."DEPTNO" = 10) (0.050)
NAME VALUE
-------------------- ---------
db block gets 111568
consistent gets 4895
physical reads 6894
redo size 16758760
sorts (disk) 0
sorts (memory) 5
rows processed 50000For this query, first the blocks are read in Consistent Mode to find data where deptno = 10, and at the moment of actually changing the value, the same block is read again in Current Mode, then 100000 is added to the SAL value at that point and updated.
The block reads occurring here are divided as follows.
- Block reads that occur when querying data matching the condition deptno = 10 are recorded in consistent gets.
- The action of reading the block again to modify the actual value is shown in the db block gets entry.
Additional important points to understand are as follows.
- When retrieving data in Consistent Mode, block-level I/O occurs, so the number of blocks read is recorded in the statistics.
- On the other hand, when reading data in Current Mode, it is actually for the purpose of finding the data to be modified, so the number of rows to be modified and the number of blocks read tend to be similar.
- Also, the db block gets value can sometimes be larger than the consistent gets value.
This difference arises from the execution pattern of SQL and the characteristics of the I/O method.