Document Type | Technical Information
Category | Administration
Applicable Product Version | T6, T7
Document Number | TADTI136
Overview
This document explains queries for UNDO MONITORING in Tibero.
Method
1. Checking UNDO_RETENTION
Specifies the retention time (in seconds) for maintaining UNDO images for committed information.
SQL> SHOW PARAMETER UNDO_RETENTION
NAME TYPE VALUE
---------------------------------------------------- -------- ------------
UNDO_RETENTION INT32 900
2. Calculating the amount of UNDO BLOCKs used per second
SQL> select max(undoblks/((end_time-begin_time)*3600*24)) "UNDO_BLOCK_PER_SEC"
FROM v$undostat;
3. Checking appropriate undo size and retention time
SQL> select d.undo_size/(1024*1024) "Current UNDO SIZE",
SUBSTR(e.value,1,25) "UNDO RETENTION",
(to_number(e.value)*to_number(f.value)*g.undo_block_per_sec) / (1024*1024) "Necessary UNDO SIZE"
from (
select sum(bytes) undo_size from dba_data_files
where tablespace_name = 'UNDO'
) d,
v$parameters e,
v$parameters f,
(
select max(undoblks/((end_time-begin_time)*3600*24)) undo_block_per_sec
from v$undostat
) g
where e.name = 'UNDO_RETENTION'
and f.name = 'DB_BLOCK_SIZE';
4. Other UNDO-related query checks
SQL> select * from v$undo_free_space;
TS_ID TS_NAME TOTAL_BLOCKS FREE_BLOCKS TOTAL_SIZE FREE_SIZE
---------- ---------- ------------ ----------- ---------- ----------
1 UNDO0 32000 25349 262144000 207659008