Skip to main content

Search

Welcome to Tibero GTS!

Preventing Snapshot too old Error

Document Type | Troubleshooting

Category | App Development

Applicable Product Versions | 5SP1FS01, 5SP1FS02, 5SP1FS03, 5SP1FS04, 5SP1FS06, 6FS01, 6FS02, 6FS03, 6FS04, 6FS05, 6FS06, 6FS07, 6FS07PS, 7FS01, 7FS02, 7FS02PS

Error Code | 21003

Document Number | TDETS013

 

Issue

Intermittent snapshot too old errors may occur during DB operation, causing desired tasks to fail to complete.
The following error message appears during query execution.
[09-07T02:48:26.170817] [FRM-1203] [I] THROW. ec=ERROR_TX_SNAPSHOT_TOO_OLD(-21003) [ Snapshot is too old.] (csr_id:0) [tx_usgmt.c:2123:usgmt_snapshot_too_old]

 

Cause

This error mainly occurs when query execution time is long, and the Undo block data has been reused by another transaction, making it impossible to reference data from that Undo block.
 
This is one of the mechanisms to ensure data read consistency, and the snapshot too old error occurs when the required Undo information has disappeared.
 
 

Solutions

1. Change _USGMT_ONLINE_MAX and _USGMT_ONLINE_MIN Settings

_USGMT_ONLINE_MIN
Sets the minimum number of online usgmt per node. After Tibero startup, this number of usgmts will be online to handle initial load.
  • Number of sessions * Number of nodes * 20% =
    ex) 3000 (sessions) * 2 (TAC 2 nodes) * 0.2 (20%) = 120
 
_USGMT_ONLINE_MAX
Sets the maximum number of online usgmt nodes overall. If many transactions run concurrently, it is recommended to increase this number so that each transaction can use one usgmt. Setting it approximately equal to the expected number of concurrent transactions (sessions) is recommended.
  • Number of sessions * Number of nodes = 3000 * 2 = 6000
    ex) 3000 (sessions) * 2 (TAC 2 nodes)
 
Set in Tibero parameter file
$ vi $TB_HOME/config/$TB_SID.tip
_USGMT_ONLINE_MIN=1200
_USGMT_ONLINE_MAX=6000

Apply after restart 
$ tbdown immediate
$ tbboot
$ tbsql sys/tibero
SQL> select count(*) from DBA_UNDO_SGMTS;

SQL> select name, value from vt_parameter where name in ('_USGMT_ONLINE_MIN','_USGMT_ONLINE_MAX');
Note
For more details, please refer to Undo Tablespace Size Estimation .
 

2. Change UNDO_RETENTION

Set the UNDO_RETENTION value longer than the longest running transaction time.
  • You can check the duration of long-running queries using the BEGIN_TIME and END_TIME columns of the V$UNDOSTAT view.
  • It is recommended to set UNDO_RETENTION to a value higher than the default 900 seconds. (Unit: seconds)
SQL> show param UNDO_RETENTION

NAME                         TYPE     VALUE
---------------------------- -------- ----------------------------------------
UNDO_RETENTION               INT32    900

- Can be applied dynamically
SQL> ALTER SYSTEM SET UNDO_RETENTION=1800;

- To apply permanently, set in Tibero parameter file
$ vi $TB_HOME/config/$TB_SID.tip
UNDO_RETENTION=1800

 

3. Increase Undo Tablespace Size

Increasing the UNDO TABLESPACE size can reduce the chance of UNDO SEGMENTS being overwritten while SQL executes.
Below is an example of adding a 32GB UNDO002.dtf file when the UNDO TABLESPACE name is UNDO0. 
SQL> ALTER TABLESPACE UNDO0 ADD DATAFILE 'UNDO002.dtf' SIZE 32G AUTOEXTEND OFF;

- Query UNDO TABLESPACE information
SQL> set linesize 140
SQL> set pagesize 100
SQL> set feedback off
SQL> col tablespace_name format a15
SQL> select  ts_name as "TABLESPACE_NAME",
       ROUND(total_size/1024/1024,2) as "TOTAL_SIZE(MB)",
       ROUND((total_size - free_size) / 1024/1024 ,2)as "USED_SIZE(MB)",
       ROUND(free_size/1024/1024,2) as "FREE_SIZE(MB)",
       ROUND(( free_size / total_size) * 100,2) as "FREE_SIZE(%)"
from v$undo_free_space;

 

4. Modify SQL

Note
This method requires cooperation from the application business team.
  • For DML batch processing, adjust the commit count to enable fast commits.
  • Tune queries to reduce SQL execution time.
  • The shorter the query execution time, the fewer UNDO SEGMENTS are needed.
  • If SNAPSHOT_TOO_OLD errors occur in a normally operating system, first check if there have been changes to the queryโ€™s history or execution plan.
  • Also check if large batch jobs that are not normally run have been executed.
 
 
 

Related to