[20230227][20230109]Oracle Global Temporary Table ORA-01555 and Undo Retention.t

[20230227][20230109]Oracle Global Temporary Table ORA-01555 and Undo Retention.txt

--//http://ksun-oracle.blogspot.com/2022/08/oracle-global-temporary-table-ora-01555.html
--//按照作者的提示,問題應該出現在19c版本.作者演示了temp_undo_enabled=TRUE情況修改塊數量大於6次的情況下,再次查詢前image出
--//現ora-01555的情況.

1.環境:
SYS@192.168.100.235:1521/orcl> @ pr
==============================
PORT_STRING                   : x86_64/Linux 2.4.xx
VERSION                       : 19.0.0.0.0
BANNER                        : Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
BANNER_FULL                   : Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
BANNER_LEGACY                 : Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
CON_ID                        : 0
PL/SQL procedure successfully completed.

SYS@192.168.100.235:1521/orcl> show parameter undo
PARAMETER_NAME    TYPE    VALUE
----------------- ------- --------
temp_undo_enabled boolean FALSE
undo_management   string  AUTO
undo_retention    integer 30000
undo_tablespace   string  UNDOTBS1
--//注意temp_undo_enabled= TRUE,我的測試環境預設是false.設定等於true一定程度減少redo log,因為這些undo記錄在臨時檔案,減少
--//了redo log的記錄.

SYS@192.168.100.235:1521/orcl> alter system set temp_undo_enabled=true scope=memory;
System altered.
--//temp_undo_enabled= TRUE,如果是false,無法測試出來遇到的情況!!.

2.測試建立:

--//truncate table gtt_tab_1;
--//drop table gtt_tab_1 cascade constraints;
create global temporary table gtt_tab_1 (x number, y number) on commit preserve rows nocache;
insert into gtt_tab_1 select level, level from dual connect by level <= 3;
commit;
--//分析略.

3.測試:

SYS@192.168.100.235:1521/orcl> select count(*) from gtt_tab_1;
  COUNT(*)
----------
         3

$ cat undo1555.txt
declare
  l_x          number;
  l_y          number;
  l_update_cnt number := &&1;   --hit ORA-1555 when l_update_cnt >= 6
  cursor c_gtt_cur is select /*+ GATHER_PLAN_STATISTICS MONITOR */ * from gtt_tab_1;
begin
  open c_gtt_cur;

  for i in 1..l_update_cnt loop
    update gtt_tab_1 set y = -i where x = 2;
    commit;
  end loop;

  loop
    fetch c_gtt_cur into l_x, l_y;
    exit when c_gtt_cur%notfound;
    dbms_output.put_line(l_x ||', '||l_y);
  end loop;
  close c_gtt_cur;

  rollback;
end;
/

SYS@192.168.100.235:1521/orcl> @ undo1555.txt 6
declare
*
ERROR at line 1:
ORA-01555: snapshot too old: rollback segment number 12 with name "$TEMPUNDOSEG" too small
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
ORA-06512: at line 15

SYS@192.168.100.235:1521/orcl> @ undo1555.txt 5
PL/SQL procedure successfully completed.

4.繼續:

--//作者還提示修改_db_block_max_cr_dba引數,因為要重啟資料庫,測試無法繼續.
--//連結:http://ksun-oracle.blogspot.com/2022/08/oracle-global-temporary-table-ora-01555.html

SYS@192.168.100.235:1521/orcl> @ hide _db_block_max_cr_dba
SYS@192.168.100.235:1521/orcl> @ pr
==============================
NAME                          : _db_block_max_cr_dba
DESCRIPTION                   : Maximum Allowed Number of CR buffers per dba
DEFAULT_VALUE                 : TRUE
SESSION_VALUE                 : 6
SYSTEM_VALUE                  : 6
ISSES_MODIFIABLE              : FALSE
ISSYS_MODIFIABLE              : FALSE
PL/SQL procedure successfully completed.

--//_db_block_max_cr_dba    Maximum Allowed Number of CR buffers per dba  default 6 (5 CR buffers and 1 Current buffer)
--//In fact, increasing "_db_block_max_cr_dba" to 20:

--//alter system set "_db_block_max_cr_dba" = 20 scope=spfile;
--// alter system reset "_db_block_max_cr_dba";
startup force

--//ORA-01555 only occurs when l_update_cnt >= 20, but not when l_update_cnt <= 19.

5.另外作者還提供了ORA-01555 Errorstack Trace Event.

--//We can wrap above test with 1555 errorstack trace and 10046 trace, and then look the trace file:

alter system set max_dump_file_size = UNLIMITED;
alter session set events='1555 trace name errorstack level 3: 10046 trace name context forever, level 1';
-- above GTT ORA-01555 Test
alter session set events='1555 trace name errorstack off: 10046 trace name context off';

--//我的測試:
SYS@192.168.100.235:1521/orcl> alter session set events='1555 trace name errorstack level 3: 10046 trace name context forever, level 1';
Session altered.

SYS@192.168.100.235:1521/orcl> @ undo1555.txt 6
declare
*
ERROR at line 1:
ORA-01555: snapshot too old: rollback segment number 6 with name "$TEMPUNDOSEG" too small
ORA-06512: at line 15

SYS@192.168.100.235:1521/orcl> alter session set events='1555 trace name errorstack off: 10046 trace name context off';
Session altered.

--//跟蹤檔案內容如下:

=====================
PARSING IN CURSOR #139705987089112 len=61 dep=1 uid=0 oct=3 lid=0 tim=38207660187240 hv=638116098 ad='153ee9ed0' sqlid='4ancgbsm0js82'
SELECT /*+ GATHER_PLAN_STATISTICS MONITOR */ * FROM GTT_TAB_1
END OF STMT
PARSE #139705987089112:c=722,e=931,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=1,plh=589414948,tim=38207660187240
EXEC #139705987089112:c=61,e=61,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=1,plh=589414948,tim=38207660187382
=====================
PARSING IN CURSOR #139705988905696 len=41 dep=1 uid=0 oct=6 lid=0 tim=38207660187557 hv=2270018982 ad='116e5f150' sqlid='37ny3du3nvgd6'
UPDATE GTT_TAB_1 SET Y = -:B1 WHERE X = 2
END OF STMT
PARSE #139705988905696:c=122,e=122,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=1,plh=0,tim=38207660187557
EXEC #139705988905696:c=917,e=992,p=0,cr=3,cu=3,mis=1,r=1,dep=1,og=1,plh=2169629811,tim=38207660188595
STAT #139705988905696 id=1 cnt=0 pid=0 pos=1 obj=0 op='UPDATE  GTT_TAB_1 (cr=3 pr=0 pw=0 str=1 time=266 us)'
STAT #139705988905696 id=2 cnt=1 pid=1 pos=1 obj=221651 op='TABLE ACCESS FULL GTT_TAB_1 (cr=3 pr=0 pw=0 str=1 time=116 us cost=2 size=6 card=1)'
CLOSE #139705988905696:c=2,e=1,dep=1,type=3,tim=38207660188744
=====================
PARSING IN CURSOR #139705988490568 len=6 dep=1 uid=0 oct=44 lid=0 tim=38207660188773 hv=255718823 ad='0' sqlid='8ggw94h7mvxd7'
COMMIT
END OF STMT
PARSE #139705988490568:c=6,e=5,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=0,plh=0,tim=38207660188772
XCTEND rlbk=0, rd_only=1, tim=38207660188822
EXEC #139705988490568:c=82,e=82,p=0,cr=0,cu=2,mis=0,r=0,dep=1,og=0,plh=0,tim=38207660188892
CLOSE #139705988490568:c=0,e=1,dep=1,type=3,tim=38207660188925
EXEC #139705988905696:c=210,e=211,p=0,cr=3,cu=3,mis=0,r=1,dep=1,og=1,plh=2169629811,tim=38207660189148
CLOSE #139705988905696:c=1,e=1,dep=1,type=3,tim=38207660189188
XCTEND rlbk=0, rd_only=1, tim=38207660189207
EXEC #139705988490568:c=47,e=48,p=0,cr=0,cu=2,mis=0,r=0,dep=1,og=0,plh=0,tim=38207660189249
CLOSE #139705988490568:c=1,e=1,dep=1,type=3,tim=38207660189279
EXEC #139705988905696:c=89,e=89,p=0,cr=3,cu=3,mis=0,r=1,dep=1,og=1,plh=2169629811,tim=38207660189383
CLOSE #139705988905696:c=0,e=0,dep=1,type=3,tim=38207660189409
XCTEND rlbk=0, rd_only=1, tim=38207660189426
EXEC #139705988490568:c=47,e=47,p=0,cr=0,cu=2,mis=0,r=0,dep=1,og=0,plh=0,tim=38207660189468
CLOSE #139705988490568:c=0,e=0,dep=1,type=3,tim=38207660189486
EXEC #139705988905696:c=146,e=136,p=0,cr=3,cu=3,mis=0,r=1,dep=1,og=1,plh=2169629811,tim=38207660189648
CLOSE #139705988905696:c=1,e=1,dep=1,type=3,tim=38207660189683
XCTEND rlbk=0, rd_only=1, tim=38207660189700
EXEC #139705988490568:c=43,e=43,p=0,cr=0,cu=2,mis=0,r=0,dep=1,og=0,plh=0,tim=38207660189738
CLOSE #139705988490568:c=0,e=0,dep=1,type=3,tim=38207660189756
EXEC #139705988905696:c=153,e=153,p=0,cr=3,cu=3,mis=0,r=1,dep=1,og=1,plh=2169629811,tim=38207660189920
CLOSE #139705988905696:c=1,e=0,dep=1,type=3,tim=38207660189946
XCTEND rlbk=0, rd_only=1, tim=38207660189962
EXEC #139705988490568:c=28,e=28,p=0,cr=0,cu=2,mis=0,r=0,dep=1,og=0,plh=0,tim=38207660189986
CLOSE #139705988490568:c=0,e=0,dep=1,type=3,tim=38207660190017
EXEC #139705988905696:c=111,e=111,p=0,cr=3,cu=3,mis=0,r=1,dep=1,og=1,plh=2169629811,tim=38207660190145
CLOSE #139705988905696:c=1,e=0,dep=1,type=3,tim=38207660190175
XCTEND rlbk=0, rd_only=1, tim=38207660190189
EXEC #139705988490568:c=29,e=29,p=0,cr=0,cu=2,mis=0,r=0,dep=1,og=0,plh=0,tim=38207660190214
CLOSE #139705988490568:c=1,e=1,dep=1,type=3,tim=38207660190231

*** 2023-02-27T09:45:53.672983+08:00

dbkedDefDump(): Starting a non-incident diagnostic dump (flags=0x0, level=3, mask=0x0)
----- Error Stack Dump -----
<error barrier> at 0x7ffc6e3c2860 placed dbkda.c@296
ORA-01555: snapshot too old: rollback segment number 6 with name "???" too small
<error barrier> at 0x7ffc6e3c6198 placed ktu.c@27465