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
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