106 - [Oracle 19C] Automatic Tuning of Undo Retention, example

 

 

Automatic Tuning of Undo Retention

 

Oracle Database automatically tunes the undo retention period based on how the undo tablespace is configured.

•   If the undo tablespace is configured with the AUTOEXTEND option, the database dynamically tunes the undo retention period to be somewhat longer than the longest-running active query on the system. However, this retention period may be insufficient to accommodate Oracle Flashback operations. Oracle Flashback operations resulting in snapshot too old errors are the indicator that you must intervene to ensure that sufficient undo data is retained to support these operations. To better accommodate Oracle Flashback features, you can either set the UNDO_RETENTION parameter to a value equal to the longest expected Oracle Flashback operation, or you can change the undo tablespace to fixed size.
• If the undo tablespace is fixed size, the database dynamically tunes the undo retention period for the best possible retention for that tablespace size and the current system load. This best possible retention time is typically significantly greater than the duration of the longest-running active query.
  If you decide to change the undo tablespace to fixed-size, you must choose a tablespace size that is sufficiently large. If you choose an undo tablespace size that is too small, the following two errors could occur:

 

-  DML could fail because there is not enough space to accommodate undo for new transactions.

-  Long-running queries could fail with a snapshot too old error, which means that there was insufficient undo data for read consistency.

 

 

 

Test example

 

show user ;
show con_name;
/*
USER is "PDB10_ADMIN"
CON_NAME 
------------------------------
PDB10
*/

 

 

 

show parameter undo
/*
NAME              TYPE    VALUE    
----------------- ------- -------- 
temp_undo_enabled boolean FALSE    
undo_management   string  AUTO     
undo_retention    integer 900      
undo_tablespace   string  UNDOTBS1 
*/



--select * from DBA_TABLESPACES;
select tablespace_name, status, contents, logging, retention from DBA_TABLESPACES;
/*
TABLESPACE_NAME                STATUS    CONTENTS              LOGGING   RETENTION  
------------------------------ --------- --------------------- --------- -----------
SYSTEM                         ONLINE    PERMANENT             LOGGING   NOT APPLY  
SYSAUX                         ONLINE    PERMANENT             LOGGING   NOT APPLY  
UNDOTBS1                       ONLINE    UNDO                  LOGGING   NOGUARANTEE
TEMP                           ONLINE    TEMPORARY             NOLOGGING NOT APPLY  
USERS                          ONLINE    PERMANENT             LOGGING   NOT APPLY  
TS1                            ONLINE    PERMANENT             LOGGING   NOT APPLY  
TBSALERT                       ONLINE    PERMANENT             LOGGING   NOT APPLY  
UNDOTBS_K                      ONLINE    UNDO                  LOGGING   NOGUARANTEE

8 rows selected. 
*/



SELECT DBMS_METADATA.GET_DDL('TABLESPACE','UNDOTBS1') FROM dual;
-- so it is AUTOEXTEND
-- so oracle will will adjust the undo_retention automaticlly ( for active query )
-- but flashback operations ( maybe or maybe not work )
/*
DBMS_METADATA.GET_DDL('TABLESPACE','UNDOTBS1')                                  
--------------------------------------------------------------------------------

  CREATE UNDO TABLESPACE "UNDOTBS1" DATAFILE 
  '/u01/app/oracle/oradata/ORCL/PDB10/undotbs01.dbf' SIZE 104857600
  AUTOEXTEND ON NEXT 5242880 MAXSIZE 32767M
  BLOCKSIZE 8192
  EXTENT MANAGEMENT LOCAL AUTOALLOCATE
   ALTER DATABASE DATAFILE 
  '/u01/app/oracle/oradata/ORCL/PDB10/undotbs01.dbf' RESIZE 183500800
*/

 

 

 

# 아래 쿼리값은 논리적이지 않다고 오라클이 자체 판단하고 무시한다. 테스트를 통해서 확인 예정.
alter system set undo_retention=1;
/*
System SET altered.
*/



create table xyz
(id number primary key, val number );
/*
Table XYZ created.
*/



begin
  for i in 1..500000
  loop
    insert into xyz values (i,500 );
  end loop;
  commit;
end;
/*
PL/SQL procedure successfully completed.
*/



select * from xyz
order by 1;
/*
        ID        VAL
---------- ----------
         1        500
         2        500
         3        500
         4        500
....
*/

# now do this without comment

update xyz set val=0 where id =500000;
/*
1 row updated.
*/

# so this record will be in undo

 

 

 

# open new session

SQL> show user
USER is "PDB10_ADMIN"


SQL> show con_name
CON_NAME
------------------------------
PDB10


SQL> create table xyz_copy (id number primary key, val number ); 
Table created.



# this wil insert the data from xyz to xyz_copy 
# note that the value of id 500000 still 500 because the user did not commit
# exec the code and while it execute back to first session and commit
begin
  for i in (select * from xyz)
  loop
    insert into xyz_copy values (i.id, i.val );
  end loop;
  commit;
end;
/*
PL/SQL procedure successfully completed.
*/


# 상기 프로시져가 실행중에 첫번째 세션에서 commit 실행해도 
# xyz_copy 테이블 데이터는 실행시점을 기준으로 한 데이터를 복사하기때문에 VAL은 500을 유지함.
select * from xyz_copy
where id=500000;

        ID        VAL
---------- ----------
    500000        500


# so oracle will adjust the undo_retention automaticlly to be more than 1s
# but flashback maybe or maybe not work

select versions_starttime,versions_endtime, val from xyz
versions between scn minvalue and maxvalue
where id=500000;


/*
VERSIONS_STARTTIME              VERSIONS_ENDTIME                       VAL
------------------------------- ------------------------------- ----------
18-DEC-23 09.55.28.000000000 PM                                          0
                                18-DEC-23 09.55.28.000000000 PM        500


일정시간이 지나면 상기 결과값은 아래처럼 변경된다.


VERSIONS_STARTTIME              VERSIONS_ENDTIME                       VAL
------------------------------- ------------------------------- ----------
                                                                         0
*/

 

 

 

# 최정결과값은 아래와 같다.

select * from xyz_copy where id=500000;
        ID        VAL
---------- ----------
    500000        500



select * from xyz where id=500000;
        ID        VAL
---------- ----------
    500000          0