Wednesday, April 17, 2019

Blog List

LOB ORA-22924: snapshot too old and Fix

To continue the discussion in Blog: UNDO Practice, this one will demonstrate LOB special UNDO error with code examples in Plsql and provide a fix.
    ORA-01555: snapshot too old: rollback segment number  with name "" too small
    ORA-22924: snapshot too old
We will test "too old" in two dimensions, one is according to space usage (LOB pctversion), other is according to life time (LOB retention).

At beginning, it was thought to find some concrete code examples to reproduce ORA-22924. Googled with "Oracle LOB ORA-22924: snapshot too old example", and paged over a dozen of returned results, it was still empty.

Note: All tests are done in 12.1.0.2.0 (12cR1)


1. Test Setup


First we create a table containing one LOB column and fill some data. The LOB column is stored as basicfile and using pctversion to control the old versions of LOB data. The pctversion is set to special value 0 so that ORA-22924 can be reproduced in each short run. We also tested pctversion default value 10, the same ORA-22924 is still reproducible (later we will also test retention Parameter).

---==================== PCTVERSION Test Setup ====================---

drop tablespace test_ts including contents and datafiles;

create tablespace test_ts datafile '/oradb/oradata/testdb/test_dbf.dbf' size 100m online;

drop table tab_lob cascade constraints;

create table tab_lob(id number, mylob clob) 
  lob (mylob) store as basicfile 
  (tablespace  test_ts
   enable      storage in row
   chunk       8192
   pctversion 0
   --pctversion 10        -- default of 10 (%)
   --retention   none
   nocache
   logging)
tablespace test_ts; 

declare
  l_cnt     number := 1e1;
  l_clob    clob   := to_clob(rpad('abc', 10000, 'x'));
begin
  for i in 1..l_cnt loop
    insert into tab_lob values(i, l_clob);
  end loop;
  commit;
end;
/   
Show LOB meta info:

---==================== PCTVERSION Test Meta Info ====================---
 
column table_name format a14;
column column_name format a14;
column segment_name format a28;
column column_name format a14;
column retention_type format a20;
  
select table_name, column_name, segment_name, pctversion, retention, retention_type 
  from dba_lobs where table_name in ('TAB_LOB');  
  
TABLE_NAME COLUMN_NAME SEGMENT_NAME              PCTVERSION  RETENTION RETENTION_TYPE
---------- ----------- ------------------------- ---------- ---------- --------------
TAB_LOB    MYLOB       SYS_LOB0003449207C00002$$          0            NO
  
 
select segment_name, segment_type from dba_segments where tablespace_name in ('TEST_TS');

SEGMENT_NAME                 SEGMENT_TYPE
---------------------------- ------------
TAB_LOB                      TABLE
SYS_IL0003449207C00002$$     LOBINDEX
SYS_LOB0003449207C00002$$    LOBSEGMENT
Create 3 procedures for our test.

---==================== PCTVERSION Test Meta Info ====================---

create or replace procedure lob_22924_select(p_id number, p_cnt number, p_sleep number) as
  l_clob           clob;
  l_null_check     boolean;
begin
  select mylob into l_clob from tab_lob where id = p_id;
  
  for i in 1..p_cnt loop
   dbms_output.put_line('------- Seq: '||i);
   
   -- getlength, no error
    dbms_output.put_line('LOB length check OK, length = '||dbms_lob.getlength(l_clob));
    
    -- null check, no error
    l_null_check := l_clob is null;
    l_null_check := l_clob is not null;
    dbms_output.put_line('LOB null check OK');
    
    -- content access, throw ORA-22924 under ORA-01555
    dbms_output.put_line('LOB content check, substr ='||dbms_lob.substr(l_clob, 10, 2000));
    
    dbms_lock.sleep(p_sleep);
  end loop;
end;
/

create or replace procedure lob_22924_update(p_id number, p_cnt number, p_sleep number) as
  l_clob      clob;
  l_pad       varchar2(1000) := rpad('abc', 100, 'x');
begin
  select mylob into l_clob from tab_lob where id = p_id;
  
  for i in 1..p_cnt loop
   update tab_lob set mylob = mylob||l_pad where id = p_id;
    commit;
    dbms_lock.sleep(p_sleep);
  end loop;
end;
/

create or replace procedure lob_22924_select_update(p_id number, p_cnt number, p_sleep number) as
  l_clob        clob;
  l_pad         varchar2(1000) := rpad('abc', 100, 'x');
  l_clob_upd    clob := to_clob(rpad('abc', 10000, 'x'));  
  l_null_check  boolean;
begin
  select mylob into l_clob from tab_lob where id = p_id;
  
  for i in 1..p_cnt loop
    dbms_output.put_line('------- Seq: '||i);  
    update tab_lob set mylob = mylob||l_pad where id = p_id;
    commit;
    
    -- getlength, no error
    dbms_output.put_line('LOB length check OK, length = '||dbms_lob.getlength(l_clob));
    
    -- null check, no error
    l_null_check := l_clob is null;
    l_null_check := l_clob is not null;
    dbms_output.put_line('LOB null check OK');
    
    -- content access, throw ORA-22924 under ORA-01555
    dbms_output.put_line('LOB content check, substr ='||dbms_lob.substr(l_clob, 10, 2000));
    
    dbms_lock.sleep(p_sleep);
  end loop;
end;
/


2. Test Run


We will make two different tests to generate ORA-22924. Once with two Sqlplus sessions, once with a single session.


2.1. Two Sessions.


We open two Sqlplus Sessions. In Session_1, call lob_22924_select to start a query:

-------------- Session_1@T1 select --------------
 
10:22:15 Sql > exec lob_22924_select(3, 1e2, 1);
  ------- Seq: 1
  LOB length check OK, length = 10000
  LOB null check OK
  LOB content check, substr =xxxxxxxxxx
  ------- Seq: 2
  LOB length check OK, length = 10000
  LOB null check OK
  LOB content check, substr =xxxxxxxxxx
  
  ...
  ------- Seq: 7
  LOB length check OK, length = 10000
  LOB null check OK
  BEGIN lob_22924_select(3, 1e2, 1); END;
  
  *
  ERROR at line 1:
  ORA-01555: snapshot too old: rollback segment number  with name "" too small
  ORA-22924: snapshot too old
  ORA-06512: at "SYS.DBMS_LOB", line 1109
  ORA-06512: at "S.LOB_22924_SELECT", line 19
  ORA-06512: at line 1
In Session_2, call lob_22924_update to start a loop update.

-------------- Session_2@T2 update --------------

10:22:24 Sql > exec lob_22924_update(3, 1e4, 0.01);
After a couple of seconds, Session_1 throws error ORA-01555 and ORA-22924. If we look procedure lob_22924_select, error occurs when we access content by dbms_lob.substr. For dbms_lob.getlength and LOB null check, there is no such error. Probably both dbms_lob.getlength and LOB null check are using LOB index, and LOB index is based on normal Oracle UNDO mechanism.

Session_1 output shows that LOB length is always 10000, which implies that the checked LOB data is pointing to the fetched data and never changed. It acts like a consistent view in READ ONLY transaction (or SERIALIZABLE transaction).


2.2. One Session


We open one single Sqlplus Session Session_2, call lob_22924_select_update to start a query, then make updates:

-------------- Session_3@T3 update --------------

10:28:33 Sql > exec lob_22924_select_update(7, 1e2, 1);
  ------- Seq: 1
  LOB length check OK, length = 10000
  LOB null check OK
  LOB content check, substr =xxxxxxxxxx
  ------- Seq: 2
  LOB length check OK, length = 10000
  LOB null check OK
  LOB content check, substr =xxxxxxxxxx
  
  ...
  ------- Seq: 54
  LOB length check OK, length = 10000
  LOB null check OK
  BEGIN lob_22924_select_update(7, 1e2, 1); END;
  
  *
  ERROR at line 1:
  ORA-01555: snapshot too old: rollback segment number  with name "" too small
  ORA-22924: snapshot too old
  ORA-06512: at "SYS.DBMS_LOB", line 1109
  ORA-06512: at "S.LOB_22924_SELECT_UPDATE", line 23
  ORA-06512: at line 1
After about 50 seconds, it throws error ORA-01555 and ORA-22924.

lob_22924_select_update is a merge of previous select (lob_22924_select) and update (lob_22924_update). When we run both in one single session, we get the same error. That means even though we update the current LOB version, the old consistent version is still kept in the same session. And any access to the content can hit ORA-22924 error.


3. Retention Test


LOB column can also be configured to store old versions of LOB data for a period of time by normal retention, rather than using a percentage of the table space by above pctversion.

First we change the DDL to use retention, fill data and show meta info:

---==================== RETENTION Test ====================

alter system set undo_retention = 900;    --(Default 900)

drop table tab_lob cascade constraints;

create table tab_lob(id number, mylob clob) 
  lob (mylob) store as basicfile 
  (tablespace  test_ts
   enable      storage in row
   chunk       8192
   --pctversion 0
   retention   none
   nocache
   logging)
tablespace test_ts; 

declare
  l_cnt     number := 1e1;
 l_clob    clob   := to_clob(rpad('abc', 10000, 'x'));
begin
  for i in 1..l_cnt loop
    insert into tab_lob values(i, l_clob);
  end loop;
  commit;
end;
/   

select table_name, column_name, segment_name, pctversion, retention, retention_type 
  from dba_lobs where table_name in ('TAB_LOB');  
  
TABLE_NAME COLUMN_NAME SEGMENT_NAME              PCTVERSION  RETENTION RETENTION_TYPE
---------- ----------- ------------------------- ---------- ---------- --------------
TAB_LOB    MYLOB       SYS_LOB0003449216C00002$$                   900 YES
We can see that default retention is picked from undo_retention default 900 seconds. To speed up out test, we can low it to a short time, for example, 3 seconds:

alter system set undo_retention = 3;  

alter table tab_lob modify lob (mylob) (retention);  
  
select table_name, column_name, segment_name, pctversion, retention, retention_type 
  from dba_lobs where table_name in ('TAB_LOB');  
  
TABLE_NAME COLUMN_NAME SEGMENT_NAME              PCTVERSION  RETENTION RETENTION_TYPE
---------- ----------- ------------------------- ---------- ---------- --------------
TAB_LOB    MYLOB       SYS_LOB0003449216C00002$$                     3 YES
Then restore original undo_retention, and recompile invalidated procedures:

alter system set undo_retention = 900; 

alter procedure lob_22924_select compile;
alter procedure lob_22924_select_update compile;
alter procedure lob_22924_update compile;    
Now we can repeat the same tests (two or one sessions) as pctversion, and get the same errors.

With retention, it can require longer time to erase the kept CR copy because of Oracle AUM (Automatic Undo Management). Therefore it needs to a higher update loops (p_cnt) when calling lob_22924_select_update to hit ORA-22924.

We also noticed that when running several concurrent update sessions (each updates a different row), the error appears quicker and more frequent because all LOB data (from different rows) are stored in the same datafile.


4. Fix


The problem of ORA-22924 is that we are retaining a LOB Locator to an old version of LOB data, and if this old version gets too old (overwritten by newer versions), we hit the error when accessing that Locator.

Oracle permanent LOB CR views are implemented by versions (different copies) to conform to the ANSI standard (ACID regime). LOB data does not generate rollback information (redo/undo). Only LOB Index generates undo/redo because it is implemented in normal Oracle undo/redo mechanism.

However for Oralee temporary LOBs, CR, undo and versions are not supported. They are stored in Temporary Tablespace and are session private.

In the fix below, LOB content is at first copied to a local temporary LOB so that we always preserve a CR data for later access (analogue to normal CR view at the point of query start).

Note that dbms_lob.copy is used to create a new CR copy (new instance) of LOB content. Direct LOB assignment by Sql or Plsql do not fix ORA-22924: snapshot too old.

create or replace procedure lob_22924_select_fix(p_id number, p_cnt number, p_sleep number) as
  l_clob           clob;
  l_null_check     boolean;
  l_clob_temp      clob;
begin
  -- create temporary lob
  dbms_lob.createtemporary(lob_loc => l_clob_temp, cache => true, dur => dbms_lob.call);
  
  select mylob into l_clob from tab_lob where id = p_id;
  
  -- copy content of Permanent LOB Locator to Temporary LOB Locator (pass by value)
  dbms_lob.copy(dest_lob => l_clob_temp, src_lob => l_clob, amount => dbms_lob.getlength(l_clob));
  
  -- Note: two following approaches not fix ORA-22924: snapshot too old 
  -- because Temporary LOB Locator is overwritten by Permanent LOB Locator
  -- and Temporary LOB Locator points to the Permanent LOB Locator (pass by pointer). 
  --   select mylob into l_clob_temp from tab_lob where id = p_id;   -- not fix ORA-22924 by Sql
  --   l_clob_temp := l_clob;                                        -- not fix ORA-22924 by Plsql
  
  for i in 1..p_cnt loop
    dbms_output.put_line('------- Seq: '||i);
   
    -- getlength, no error
    dbms_output.put_line('LOB length check OK, length = '||dbms_lob.getlength(l_clob_temp));
    
    -- null check, no error
    l_null_check := l_clob_temp is null;
    l_null_check := l_clob_temp is not null;
    dbms_output.put_line('LOB null check OK');
    
    -- content access by local temp copy, not throw ORA-22924 under ORA-01555
    dbms_output.put_line('LOB content check, substr ='||dbms_lob.substr(l_clob_temp, 10, 2000));
    
    dbms_lock.sleep(p_sleep);
  end loop;
  
  -- free temporary lob
  dbms_lob.freetemporary(lob_loc => l_clob_temp);
end;
/