Saturday, August 6, 2022

Space Management Enhancements in Oracle Database 21c

Oracle Database 21c, Space Management Enhancements, Oracle Database Career, Database Skills, Database Jobs, Database Study

This article describes the space management enhancements in Oracle database 21c.

◉ SecureFiles Shrink

In previous releases we were only able to free up space from SecureFile lobs by moving them, which could take a considerable amount of time for a large lob segment.

alter table tab1 move lob(lob_column_name) store as (tablespace new_ts);

In Oracle 21c we can defragment lob segments without affecting access. This releases unused space, without the overhead of a full move of the LOB segment.

The shrink can be performed for the lob segments of a specific column, or as part of a cascade operation for a table.

alter table t1 modify lob (colb_column1) (shrink space);

alter table t1 shrink space cascade;

The cascade operation was valid in previous releases, but SecureFile LOB segments were not included in the cascade.

The V$SECUREFILE_SHRINK view contains a row for a shrink operation of a segment. It is updated during the operation, and is overwritten if another shrink operation is requested for the same segment.

If the LOB is shrunk directly, rather than as part of a cascading table shrink, row movement doesn't need to be enabled for the operation to complete, as shown below.

We create a table containing a LOB column, and populate it with 1000 rows,

create table t1 as 

  select level as id,

         to_clob(dbms_random.string('x',32767)) as clob_data

  from   dual

  connect by level <= 1000;

commit;

alter table t1 add constraint t1_pk primary key (id);

We check the lob segment statistics and we can see the number of blocks used to store the LOB.

select ul.table_name,

       ul.column_name,

       ul.segment_name,

       us.blocks

from   user_lobs ul

       join user_segments us on us.segment_name = ul.segment_name;

TABLE_NAME   COLUMN_NAME  SEGMENT_NAME   BLOCKS

------------ ------------ ------------------------------ ----------

T1       CLOB_DATA    SYS_LOB0000074741C00002$$       2088

1 row selected.

SQL>

We delete the majority of the rows from the table.

delete from t1 where id < 900;

commit;

When we check the lob segment statistics and we can see the number of blocks used to store the LOB segment hasn't changed.

exec dbms_stats.gather_table_stats(null, 'T1');

select ul.table_name,

       ul.column_name,

       ul.segment_name,

       us.blocks

from   user_lobs ul

       join user_segments us on us.segment_name = ul.segment_name;

TABLE_NAME   COLUMN_NAME  SEGMENT_NAME   BLOCKS

------------ ------------ ------------------------------ ----------

T1      CLOB_DATA    SYS_LOB0000074741C00002$$     2088

SQL>

We shrink the lob segment.

alter table t1 modify lob(clob_data) (shrink space);

When we check the lob segment statistics again we see the number of blocks used to store the LOB segment has reduced.

exec dbms_stats.gather_table_stats(null, 'T1');

select ul.table_name,

       ul.column_name,

       ul.segment_name,

       us.blocks

from   user_lobs ul

       join user_segments us on us.segment_name = ul.segment_name;

TABLE_NAME   COLUMN_NAME  SEGMENT_NAME   BLOCKS

------------ ------------ ------------------------------ ----------

T1       CLOB_DATA    SYS_LOB0000079689C00002$$    1184

SQL>

We can see row movement was not enabled on this table, yet the shrink of the SecureFile LOB segment worked anyway. Remember, the table rows are not being shrunk by this operation. Just the LOB segments.

select row_movement

from   user_tables

where  table_name = 'T1';

ROW_MOVE

--------

DISABLED

SQL>

In Oracle 19c the same shrink operation on a SecureFile LOB would give the following error.

alter table t1 modify lob(clob_data) (shrink space);

Error starting at line : 1 in command -

alter table t1 modify lob(clob_data) (shrink space)

Error report -

ORA-10635: Invalid segment or tablespace type

◉ Automatic Temporary Tablespace Shrink

As the name suggests, the Automatic Temporary Tablespace Shrink feature will shrink the size of the temporary tablespace to free up space. The database can pre-emptively grow the temporary tablespace if more is needed. The documentation refers to this as Automatic Temp Tablespace Sizing. That allows us to let the temporary tablespace expand and contract as needed, without a permanent loss of disk space.

At the time of writing the documentation is limited to the New Feature manual, which just says it exists with no details of usage control or logging.

Thanks to Roger MacNicol for pointing out the relevant statistics in the V$SYSSTAT view.

column name format a40

select con_id,

       name,

       value

from   v$sysstat

where  name like '%TBS%';

    CON_ID        NAME                               VALUE

---------- ---------------------------------------- ----------

         0 TBS Extension: tasks created                0

         0 TBS Extension: tasks executed              0

         0 TBS Extension: files extended               0

         0 TBS Extension: bytes extended              0

         0 TBS Shrink: tasks created                      0

         0 TBS Shrink: tasks executed                    0

SQL>

This feature was first introduced in Oracle 19c Autonomous Database, but from Oracle 21c it is available on-prem for enterprise edition installations.

◉ Automatic Undo Tablespace Shrink

As the name suggests, the Automatic Undo Tablespace Shrink feature will shrink the size of the undo tablespace to free up space. Expired undo segments are dropped, and if possible the data files are shrunk. That allows us to let the undo tablespace expand and contract as needed, without a permanent loss of disk space.

At the time of writing the documentation is limited to the New Feature manual (here), which just says it exists with no details of usage control or logging.

Thanks to Roger MacNicol for pointing out the relevant statistics in the V$SYSSTAT view.

column name format a40

select con_id,

       name,

       value

from   v$sysstat

where  name like '%TBS%';

    CON_ID        NAME                                VALUE

---------- ---------------------------------------- ----------

         0 TBS Extension: tasks created                0

         0 TBS Extension: tasks executed              0

         0 TBS Extension: files extended               0

         0 TBS Extension: bytes extended             0

         0 TBS Shrink: tasks created                      0

         0 TBS Shrink: tasks executed                    0

SQL>

Source: oracle-base.com

Related Posts

0 comments:

Post a Comment