Skip to Content

Remark: All statements and testcases were done on Oracle 10.2.0.4.

Recently, I made a (at least for me) suprising discovery on one of our testsystems:

SQL> show parameter undo_retention;

NAME                                 TYPE        VALUE
------------------------------------ ----------- -----
undo_retention                       integer     43200

SQL> select nvl(retention,0), count(*)
  2  from dba_lobs
  3  where owner = 'SAPSR3'
  4  group by retention;

NVL(RETENTION,0)   COUNT(*)
---------------- ----------
               0       1949
           43200        279

As background information: When it comes to the undo management for lobs, there are basically 2 possibilities how this can be done:

  • Specifiy PCTVERSION when creating a table with a lob segment:
    The Oracle documentation states the following here:
    PCTVERSION is the percentage of all used LOB data space that can be occupied by old versions of LOB data pages. As soon as old versions of LOB data pages start to occupy more than the PCTVERSION amount of used LOB space, Oracle Database tries to reclaim the old versions and reuse them. In other words, PCTVERSION is the percent of used LOB data blocks that is available for versioning old LOB data.

The DDL statement used for setting PCTVERSION for a lob column at table creation looks like this:

CREATE TABLE "SAPSR3"."REPOSRC"
 (    "PROGNAME" VARCHAR2(120) DEFAULT ' ' NOT NULL ENABLE,
      "MAXLINELN" NUMBER(5,0) DEFAULT 0 NOT NULL ENABLE,
...
        "DATA" BLOB
 )
  PCTFREE 10 PCTUSED 0 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "PSAPSR3700"
  LOB ("DATA") STORE AS (
  TABLESPACE "PSAPSR3700" ENABLE STORAGE IN ROW CHUNK 8192
  PCTVERSION 0 CACHE
....
  • The second possibility is to specify RETENTION. This is the definition according to the Oracle documentation:
    As an alternative to the PCTVERSION parameter, you can specify the RETENTION parameter in the LOB storage clause of the CREATE TABLE or ALTER TABLE statement. Doing so, configures the LOB column to store old versions of LOB data for a period of time, rather than using a percentage of the table space.

The 2 possibilities are mutually exclusive, either PCTVERSION or RETENTION can be set. Until now, I always had the belief that the SAP objects would only use pctversion for undo management in case of lobs which is obviously not true
In the next step, lets check which datatypes are affected by this (CLOBs, BLOBs or both):

select dtc.data_type dtype, count(*) cnum
from dba_lobs dl, dba_tab_columns dtc
where dl.column_name = dtc.column_name and
      dl.table_name = dtc.table_name and
      dl.retention = 43200 and
      dl.owner = 'SAPSR3'
group by dtc.data_type

CLOB    172
BLOB    107

This of course sums up to the 279 entries in dba_lobs that were returned by the query at the beginning of the blog.
Lets have a look at one of the concerned tables/columns:

table RSBRATLOG
column XMLSTRING
data type CLOB

When looking at the DDL statement for this particular table, we can see that it was originally created with pctversion:

select dbms_metadata.get_ddl('TABLE','RSBRATLOG','SAPSR3') FROM DUAL;
...
 LOB ("XMLSTRING") STORE AS (
  TABLESPACE "PSAPSR3" ENABLE STORAGE IN ROW CHUNK 8192 PCTVERSION 10
  CACHE
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)) ;

This is now contradicting information. The DDL tells us that PCTVERSION was set while the dictionary claims the use of retention?
Can we actually reproduce this? Lets have a look at a table with pctversion set to 0:

SQL> select column_name, nvl(retention, 0) retention, pctversion from dba_lobs where table_name = 'DWTREE';

 column_name   retention    PCTVERSION
 -----------   ----------   ----------
    CLUSTD         0             0

alter table dwtree modify lob(CLUSTD) (retention);

select column_name, nvl(retention, 0) retention, nvl(pctversion,0) pctversion
from dba_lobs where table_name = 'DWTREE';

 column_name   retention    PCTVERSION
 -----------   ----------   ----------
    CLUSTD         43200         0

This is now working as designed: When we alter the lob in order to set the RETENTION, it will be automatically set to undo_retention. How does the DDL statement for this table look like: 

select dbms_metadata.get_ddl('TABLE','DWTREE','SAPSR3') FROM DUAL;

...
 LOB ("CLUSTD") STORE AS (
 TABLESPACE "PSAPSR3" ENABLE STORAGE IN ROW CHUNK 8192 PCTVERSION 0
 CACHE
...

Again, the DDL displays PCTVERSION although RETENTION was set. 

To make a long story short: It seems that we are running into a bug here. There are a couple of candidates on metalink (for example 5577093 which was closed as ‘not a bug’). But there is still the issue that we have lob segments that have ‘RETENTION’ set. I have even compared this against a new NW70 installation and there the situation was even more different: All lobs without a single exception had RETENTION set.

In the next step, I had to dig really deep: Let’s check the part of the R3load source code that is generating the DDL statements out of the TPL files that are delivered via the installation media.
The standard TPL entry for a table looks like this:

cretab: CREATE TABLE &tab_name&
        ( /{ &fld_name& &fld_desc& /-, /} )
        TABLESPACE &tablespace&
        &lob_cache&
        STORAGE (INITIAL     &init&
                 NEXT        &next&
                 MINEXTENTS  &minext&
                 MAXEXTENTS  &maxext&
                 PCTINCREASE &pctinc& )

After verifying the R3load coding (which I can not disclose here), it becomes clear that in case of a lob column the resulting DDL statement always looks like this:

create table zlob_test (
  aaa number,
  bbb blob
 )
 tablespace PSAPSR3
 lob (bbb) store as (cache)
 storage...

This means neither pctversion nor retention is specified. After consulting the Oracle documentation the reason for the high number of lobs having retention set is revealed:

When creating a table with a CLOB column in an ASSM tablespace without specifying PCTVERSION or RETENTION, the default value will be RETENTION – old versions of the LOB column are retained. The RETENTION value is taken from the UNDO_RETENTION parameter value.

The metalink note states CLOB but this is obviously true for BLOB as well.
This consequently means that in this system where some lobs with retention and others with pctversion are existing the retention setting was obviously lost perhaps due to the above mentioned bug respectivly a bevaviour that is working as designed (but not as expected). This particular system has quite a long history, so my first guess is that a reorganisation led to the retention setting being lost.
This can be tested for the table DWTREE now:

Before the reorg:

select column_name, nvl(retention, 0) retention, nvl(pctversion,0) pctversion
from dba_lobs where table_name = 'DWTREE';

 column_name   retention    PCTVERSION
 -----------   ----------   ----------
    CLUSTD         43200         0
brspace -f tbreorg -t DWTREE

After the reorg:

select column_name, nvl(retention, 0) retention, nvl(pctversion,0) pctversion
from dba_lobs where table_name = 'DWTREE';


 column_name   retention    PCTVERSION
 -----------   ----------   ----------
    CLUSTD         0            0

As expected, the retention was lost, which then explains the presence of both pctversion and retention lobs.

What are now the consequences of this?

In an ABAP system where there are mostly infrequent updates to lobs, it will not make much difference. However, when thinking about applictions like the portal based knowledge management, we could see an increased space usage in LOB segments due to the usually high retention period compared to the default pctversion setting of 10. But there are for sure other applications as well that are performing regular updates on LOBs.

To report this post you need to login first.

Be the first to leave a comment

You must be Logged on to comment or reply to a post.

Leave a Reply