Skip to main content

Search

Welcome to Tibero GTS!

Consistent Mode Reading and Example in Tibero

Document Type | Technical Information

Category | Administration

Document Number | TADTI071

 

Overview

In Tibero, Consistent mode reads data blocks in a way that guarantees read consistency by going through the TSN (Tibero System Number) verification process. This mainly applies to SELECT queries, and even if data changes during query execution, the read operation is performed based on the data state at the start of the query.

Note

  • Name in SQL trace: query
  • Name in tbSQL autotrace: consistent gets
At this time, data blocks are read after verifying the TSN (Tibero System Number), and if there is a change history in the block, a CR (Consistent Read) block operation occurs by referring to the UNDO Segment to restore and read the block as it was at a past point in time.
 
Therefore, even if the current block is actually read, when reading in consistent mode, it is displayed as Consistent gets in SQL Autotrace.
 
 

Method

Create Table and Data
SQL> CREATE TABLE EMP1(EMPNO NUMBER, ENAME VARCHAR(20), DEPTNO NUMBER, SAL NUMBER);
SQL> INSERT INTO EMP1
SELECT LEVEL, 'TIBERO'||LEVEL, MOD(LEVEL,20),
   trunc(DBMS_RANDOM.value(2000000,20000000))
 FROM DUAL CONNECT BY LEVEL <= 1000000;
SQL> ALTER TABLE EMP1 ADD CONSTRAINT PK_EMP1_EMPNO PRIMARY KEY(EMPNO);
SQL> CREATE INDEX IDX_EMP1_DEPTNO ON EMP1(DEPTNO);
SQL> CREATE INDEX IDX_EMP1_SAL ON EMP1(SAL);
SQL> exec dbms_stats.gather_table_stats('TIBERO','EMP1',estimate_percent=>100
                                       , method_opt=>'FOR ALL COLUMNS SIZE 254');
 
Consistent Mode Reading Example
Using the autotrace feature, query a specific employee from the emp1 table, then check related block access statistics and the execution plan.
set autot on

select * from emp1 where empno=7788;
SQL

Check the execution plan and resource usage information together through Autotrace.
EMPNO   ENAME                DEPTNO       SAL
------  ------------------  -------  ----------
 7788   TIBERO7788                8    7546894

1 row selected.

SQL ID: 498189sf7nc2k
Child number: 152
Plan hash value: 358002526

Execution Plan
--------------------------------------------------------------------------------
1 TABLE ACCESS (ROWID): EMP1 (Cost:4, %%CPU:0, Rows:1) 
2 INDEX (UNIQUE SCAN): PK_EMP1_EMPNO (Cost:3, %%CPU:0, Rows:1)

Predicate Information
--------------------------------------------------------------------------------
2 - access: ("EMP1"."EMPNO" = 7788) (0.000)

NAME                  VALUE
--------------------  ----------
db block gets                0
consistent gets             16
physical reads               0
redo size                    0
sorts (disk)                 0
sorts (memory)               3
rows processed               1
 
 
 

Related to