Skip to main content

Search

Welcome to Tibero GTS!

Reading Current Mode in Tibero and Example_DML Execution

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           50000
For 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.
 
 
 

Related to