Indeed it looks like we have made some improvements in that area. Thanks for bringing it to my attention!
SQL> set verify off
SQL>
SQL> define tab_logging = logging
SQL> define clob_logging = logging
SQL>
SQL> drop table t purge;
Table dropped.
SQL>
SQL> create table t ( x clob ) &&tab_logging lob ( x )
2 store as securefilefile (
3 tablespace users enable storage in row chunk 8192 nocache &&clob_logging );
Table created.
SQL>
SQL> conn scott/tiger@db19_pdb1
Connected.
SQL>
SQL> select s.name, st.value
2 from v$statname s, v$mystat st
3 where st.STATISTIC# = s.STATISTIC#
4 and s.name = 'redo size';
NAME VALUE
------------------------------------------------------------ ----------
redo size 676
1 row selected.
SQL>
SQL> insert /*+ APPEND */ into t
2 select rpad('x',100,'x')
3 from dual
4 connect by level <= 100000;
100000 rows created.
SQL> commit;
Commit complete.
SQL>
SQL> select s.name, st.value
2 from v$statname s, v$mystat st
3 where st.STATISTIC# = s.STATISTIC#
4 and s.name = 'redo size';
NAME VALUE
------------------------------------------------------------ ----------
redo size 27589960
1 row selected.
SQL>
SQL>
SQL>
SQL> define tab_logging = nologging
SQL> define clob_logging = logging
SQL>
SQL> drop table t purge;
Table dropped.
SQL>
SQL> create table t ( x clob ) &&tab_logging lob ( x )
2 store as securefilefile (
3 tablespace users enable storage in row chunk 8192 nocache &&clob_logging );
Table created.
SQL>
SQL> conn scott/tiger@db19_pdb1
Connected.
SQL>
SQL> select s.name, st.value
2 from v$statname s, v$mystat st
3 where st.STATISTIC# = s.STATISTIC#
4 and s.name = 'redo size';
NAME VALUE
------------------------------------------------------------ ----------
redo size 564
1 row selected.
SQL>
SQL> insert /*+ APPEND */ into t
2 select rpad('x',100,'x')
3 from dual
4 connect by level <= 100000;
100000 rows created.
SQL> commit;
Commit complete.
SQL>
SQL> select s.name, st.value
2 from v$statname s, v$mystat st
3 where st.STATISTIC# = s.STATISTIC#
4 and s.name = 'redo size';
NAME VALUE
------------------------------------------------------------ ----------
redo size 133280
1 row selected.
SQL>
SQL>
SQL> define tab_logging = logging
SQL> define clob_logging = nologging
SQL>
SQL> drop table t purge;
Table dropped.
SQL>
SQL> create table t ( x clob ) &&tab_logging lob ( x )
2 store as securefilefile (
3 tablespace users enable storage in row chunk 8192 nocache &&clob_logging );
Table created.
SQL>
SQL> conn scott/tiger@db19_pdb1
Connected.
SQL>
SQL> select s.name, st.value
2 from v$statname s, v$mystat st
3 where st.STATISTIC# = s.STATISTIC#
4 and s.name = 'redo size';
NAME VALUE
------------------------------------------------------------ ----------
redo size 676
1 row selected.
SQL>
SQL> insert /*+ APPEND */ into t
2 select rpad('x',100,'x')
3 from dual
4 connect by level <= 100000;
100000 rows created.
SQL> commit;
Commit complete.
SQL>
SQL> select s.name, st.value
2 from v$statname s, v$mystat st
3 where st.STATISTIC# = s.STATISTIC#
4 and s.name = 'redo size';
NAME VALUE
------------------------------------------------------------ ----------
redo size 27589944
1 row selected.
SQL>
SQL>
SQL> define tab_logging = nologging
SQL> define clob_logging = nologging
SQL>
SQL> drop table t purge;
Table dropped.
SQL>
SQL> create table t ( x clob ) &&tab_logging lob ( x )
2 store as securefilefile (
3 tablespace users enable storage in row chunk 8192 nocache &&clob_logging );
Table created.
SQL>
SQL> conn scott/tiger@db19_pdb1
Connected.
SQL>
SQL> select s.name, st.value
2 from v$statname s, v$mystat st
3 where st.STATISTIC# = s.STATISTIC#
4 and s.name = 'redo size';
NAME VALUE
------------------------------------------------------------ ----------
redo size 564
1 row selected.
SQL>
SQL> insert /*+ APPEND */ into t
2 select rpad('x',100,'x')
3 from dual
4 connect by level <= 100000;
100000 rows created.
SQL> commit;
Commit complete.
SQL>
SQL> select s.name, st.value
2 from v$statname s, v$mystat st
3 where st.STATISTIC# = s.STATISTIC#
4 and s.name = 'redo size';
NAME VALUE
------------------------------------------------------------ ----------
redo size 133180
1 row selected.
SQL>
SQL>
and here is the same script for cached lobs
SQL> set verify off
SQL>
SQL> define tab_logging = logging
SQL> define clob_logging = logging
SQL>
SQL> drop table t purge;
Table dropped.
SQL>
SQL> create table t ( x clob ) &&tab_logging lob ( x )
2 store as securefilefile (
3 tablespace users enable storage in row chunk 8192 cache &&clob_logging );
Table created.
SQL>
SQL> conn scott/tiger@db19_pdb1
Connected.
SQL>
SQL> select s.name, st.value
2 from v$statname s, v$mystat st
3 where st.STATISTIC# = s.STATISTIC#
4 and s.name = 'redo size';
NAME VALUE
------------------------------------------------------------ ----------
redo size 676
1 row selected.
SQL>
SQL> insert /*+ APPEND */ into t
2 select rpad('x',100,'x')
3 from dual
4 connect by level <= 100000;
100000 rows created.
SQL> commit;
Commit complete.
SQL>
SQL> select s.name, st.value
2 from v$statname s, v$mystat st
3 where st.STATISTIC# = s.STATISTIC#
4 and s.name = 'redo size';
NAME VALUE
------------------------------------------------------------ ----------
redo size 27589920
1 row selected.
SQL>
SQL>
SQL>
SQL> define tab_logging = nologging
SQL> define clob_logging = logging
SQL>
SQL> drop table t purge;
Table dropped.
SQL>
SQL> create table t ( x clob ) &&tab_logging lob ( x )
2 store as securefilefile (
3 tablespace users enable storage in row chunk 8192 cache &&clob_logging );
Table created.
SQL>
SQL> conn scott/tiger@db19_pdb1
Connected.
SQL>
SQL> select s.name, st.value
2 from v$statname s, v$mystat st
3 where st.STATISTIC# = s.STATISTIC#
4 and s.name = 'redo size';
NAME VALUE
------------------------------------------------------------ ----------
redo size 564
1 row selected.
SQL>
SQL> insert /*+ APPEND */ into t
2 select rpad('x',100,'x')
3 from dual
4 connect by level <= 100000;
100000 rows created.
SQL> commit;
Commit complete.
SQL>
SQL> select s.name, st.value
2 from v$statname s, v$mystat st
3 where st.STATISTIC# = s.STATISTIC#
4 and s.name = 'redo size';
NAME VALUE
------------------------------------------------------------ ----------
redo size 133196
1 row selected.
SQL>
SQL>
SQL> define tab_logging = logging
SQL> define clob_logging = nologging
SQL>
SQL> drop table t purge;
Table dropped.
SQL>
SQL> create table t ( x clob ) &&tab_logging lob ( x )
2 store as securefilefile (
3 tablespace users enable storage in row chunk 8192 cache &&clob_logging );
Table created.
SQL>
SQL> conn scott/tiger@db19_pdb1
Connected.
SQL>
SQL> select s.name, st.value
2 from v$statname s, v$mystat st
3 where st.STATISTIC# = s.STATISTIC#
4 and s.name = 'redo size';
NAME VALUE
------------------------------------------------------------ ----------
redo size 676
1 row selected.
SQL>
SQL> insert /*+ APPEND */ into t
2 select rpad('x',100,'x')
3 from dual
4 connect by level <= 100000;
100000 rows created.
SQL> commit;
Commit complete.
SQL>
SQL> select s.name, st.value
2 from v$statname s, v$mystat st
3 where st.STATISTIC# = s.STATISTIC#
4 and s.name = 'redo size';
NAME VALUE
------------------------------------------------------------ ----------
redo size 27589876
1 row selected.
SQL>
SQL>
SQL> define tab_logging = nologging
SQL> define clob_logging = nologging
SQL>
SQL> drop table t purge;
Table dropped.
SQL>
SQL> create table t ( x clob ) &&tab_logging lob ( x )
2 store as securefilefile (
3 tablespace users enable storage in row chunk 8192 cache &&clob_logging );
Table created.
SQL>
SQL> conn scott/tiger@db19_pdb1
Connected.
SQL>
SQL> select s.name, st.value
2 from v$statname s, v$mystat st
3 where st.STATISTIC# = s.STATISTIC#
4 and s.name = 'redo size';
NAME VALUE
------------------------------------------------------------ ----------
redo size 564
1 row selected.
SQL>
SQL> insert /*+ APPEND */ into t
2 select rpad('x',100,'x')
3 from dual
4 connect by level <= 100000;
100000 rows created.
SQL> commit;
Commit complete.
SQL>
SQL> select s.name, st.value
2 from v$statname s, v$mystat st
3 where st.STATISTIC# = s.STATISTIC#
4 and s.name = 'redo size';
NAME VALUE
------------------------------------------------------------ ----------
redo size 133196
1 row selected.
SQL>
SQL>