Document Type | Technical Information
Category | Administration
Applicable Product Version | 7FS02PS
Document Number | TADTI100
Overview
Method
Test Environment
[tibero7@rocky9.4:T724:/home/tibero]tbboot -version
Tibero 7 (DB 7.2.4) Build 303667
Linux bistro-build-centos_8.4 5.15.0-134-generic #145~20.04.1-Ubuntu SMP Mon Feb 17 13:27:16 UTC 2025 x86_64 x86_64 x86_64 GNU/Linux version (little-endian)
Patch files (FS02PS_700093a FS02PS_700103a FS02PS_344166f FS02PS_700110a FS02PS_342949f)
Compiled with "cc -no-pie -ggdb -fstack-protector-all -fno-strict-aliasing -fno-omit-frame-pointer -pipe -O3 -D_OPT_COMPILED -U_FORTIFY_SOURCE -D_USE_VIP -Wall -W -Werror-implicit-function-declaration -Wno-unused-parameter -Wpointer-arith -Wdeclaration-after-statement -DHAVE_CONFIG_H -D_WORDSIZE=64 -D_OS_LINUX -D_LINUX_X86_64 -D_X86 -DUSE_ASSERT -DTSAM -DTSAM_NO_ESDS_SEQUENCE -D_LARGEFILE_SOURCE -D_FILE_OFFSET_BITS=64 -D_USE_FSYNC -D_CHECK_LICENSE -D_STBLOG -D_TAC -DFD_SETSIZE=8192 -D_CM_OBSERVER -D_USE_FALLOCATE -D_GNU_SOURCE -D_GNU_SOURCE -D_GNU_SOURCE -D_GNU_SOURCE -DHAVE_EPOLL"
Compiled at Oct 13 2025 01:59:51 build seq 303667 init rev {2023-11-24}
Create Example Table Data
drop table acc1;
drop table acc2;
create table acc1(accno number, amt number, tamt number);
create table acc2(accno number, amt number, tamt number);
insert into acc1 select level, 1000, 2000 from dual connect by level <= 100000;
insert into acc2 select level, 1000, 2000 from dual connect by level <= 100000;
ALTER TABLE acc1 ADD CONSTRAINT PK_acc1_accno PRIMARY KEY(accno);
ALTER TABLE acc2 ADD CONSTRAINT PK_acc2_accno PRIMARY KEY(accno);
exec dbms_stats.gather_table_stats('TIBERO','ACC1',estimate_percent=>100,method_opt=>'FOR ALL COLUMNS SIZE 254');
exec dbms_stats.gather_table_stats('TIBERO','ACC2',estimate_percent=>100,method_opt=>'FOR ALL COLUMNS SIZE 254');
Example Execution
The following example demonstrates a case where Current and Consistent mode reads are used together.
- Current mode: Account1.balance
- Consistent mode: Account2.balance update Account1
update Account1
set Account1.total_balance
= Account1.balance + (select Account2.balance from Account2 where Account2.account_number=Account1.account_number)
where Account1.account_number=7788;Account1.balance is read in Current mode, and Account2.balance is read in Consistent mode.
Even if changes occur in Account2 during the update, the update and delete statements use the starting point (pre-change) values.
In the following execution example, through the UPDATE statement of Session B, Account1.total_balance becomes 1100+1000, that is 2100.
No | Session A | Session B |
| 1 | SQL>select acc1.amt acc1_amt,
| |
| 2 | SQL>update acc1 set amt=amt+100 where accno=7788; | |
| 3 | SQL>update acc2 set amt=amt+200 where accno=7788; | |
| 4 | SQL>select acc1.amt acc1_amt, acc2.amt acc2_amt, acc1.tamt acc1_tamt, acc1.amt+acc2.amt sum_amt from acc1, acc2 where acc1.accno=7788 and acc2.accno=acc1.accno; ACC1_AMT ACC2_AMT ACC1_TAMT SUM_AMT ---------- ---------- ---------- ---------- 1100 1200 2000 2300 | SQL>select acc1.amt acc1_amt, acc2.amt acc2_amt, acc1.tamt acc1_tamt, acc1.amt+acc2.amt sum_amt from acc1, acc2 where acc1.accno=7788 and acc2.accno=acc1.accno; ACC1_AMT ACC2_AMT ACC1_TAMT SUM_AMT ---------- ---------- ---------- ---------- 1000 1000 2000 2000 |
| 5 | SQL>update acc1 set acc1.tamt = acc1.amt +(select acc2.amt from acc2 where acc2.accno = acc1.accno) where acc1.accno = 7788; | |
| 6 | (no.2) is waiting for the row lock (row corresponding to accno=7788 in acc1 table) that occurred | |
| 7 | SQL>commit; | |
| 8 | - Update is performed after (no.7) commit - acc1.amt is the row being updated, so it is read in current mode (acc1.amt value of 1100 is used). | |
| 9 | SQL>select acc1.amt acc1_amt,
| SQL>select acc1.amt acc1_amt,
|
| 10 | SQL>commit; | |
| 11 | SQL> select acc1.amt acc1_amt,
| SQL> select acc1.amt acc1_amt,
|
Note
Consistent Mode
- Name in SQL trace: query
- Name in tbSQL autotrace: consistent gets
- Reads data blocks with read consistency guaranteed by checking TSN (Tibero System Number).
- Mostly applies to SELECT queries, reading data consistently based on the query start time, so even if values change during reading, it reads the values as of the query start time.
- Reads while checking TSN values; if values change, the TSN number increases, causing CR (Consistent Read) block operations that read past blocks from the undo segment, which is displayed as consistent gets in SQL AUTOTRACE.
- Even when reading current blocks, if read in consistent mode, it is displayed as consistent gets in SQL AUTOTRACE.
Current Mode
- Name in SQL trace: current
- Name in tbSQL autotrace: db block getsReads the final value at the actual block access time, not at the SQL start time. Common cases include:
- DML or large-scale sorting requiring disk sort
- SELECT FOR UPDATE execution
- Reading extent information of target segments during full table scans