1. SecureFiles and Large Objects Developer's Guide
  2. Automatic SecureFiles Shrink

16 Automatic SecureFiles Shrink

The Oracle Database SecureFiles Shrink feature provides manual and automatic methods to free the unused space in SecureFiles LOB segments and release the space back to the containing tablespace. This chapter explains how you can use the automatic method called Automatic SecureFiles Shrink with Oracle Database.

16.1 About Manual SecureFiles Shrink

To shrink a SecureFiles LOB segment manually, you can use a DDL statement to execute the shrink operation on the selected segments. The ALTER TABLE ... MOVE or the ALTER TABLE ... SHRINK SPACE statement can be used to shrink SecureFiles LOB segments. You can also use tools, such as Segment Advisor or a PL/SQL procedure, such as DBMS_SPACE.SPACE_USAGE to return information about SecureFiles LOB space usage before deciding on the LOB segments to shrink.

The following points are important when opting for the manual shrink method:

  • The manual SecureFiles shrink operation uses an online DDL with part of the operations being offline, where offline means concurrent DML are blocked until the shrink activity on the critical section ends. The DML statements do not fail, but are blocked.

  • The manual SecureFiles shrink operation disregards any flavor of undo retention and treats it as if the retention is equal to none. The user cannot expect the LOB retention feature to provide the usual guarantees after invoking the shrink operation. The user may see the ORA-1555 snapshot too old error message in queries. Run the shrink operation with caution if this is a concern.

You can use the shrink_clause on SecureFile LOB segments for release 21c and onward. There are two ways to invoke the shrink_clause:

  1. This command targets a specific LOB column and all its partitions.

    ALTER TABLE <table_name> MODIFY LOB <lob_column> SHRINK SPACE

  2. This command cascades the shrink operation for all the LOB columns and its partitions for the given table .

    ALTER TABLE <table_name> SHRINK SPACE CASCADE

See Also:

Parent topic: Automatic SecureFiles Shrink

16.2 About Automatic SecureFiles Shrink

In the context of a cloud service on Oracle Database, LOBs can potentially be the largest consumer of space. It may not be feasible for administrators to spend their time checking each LOB segment to shrink. With Automatic SecureFiles Shrink for Oracle Database, you can use a framework that enables automatic selection of SecureFiles LOBs to shrink based on a set of criteria and that runs SecureFiles Shrink in the background.

When using Automatic SecureFiles Shrink with Oracle Database, space held up by SecureFiles LOB segments is gradually freed up without affecting the current and future user workloads. All Data Manipulation Language (DML) statements and Data Definition Language (DDL) statements work when Automatic SecureFiles Shrink is in progress with little to no effect on their performance. However, Automatic SecureFiles Shrink does not have any effect on the BasicFiles LOBs and in-lined LOBs.

By default, Automatic SecureFiles Shrink is turned on for cloud.

See Also:

Reclaiming Unused Space

Parent topic: Automatic SecureFiles Shrink

16.3 Automatic SecureFiles Shrink Features

Automatic SecureFiles Shrink has the following features:

Uses the Autotask Infrastructure

Automatic SecureFiles Shrink is built on top of Autotask infrastructure, which provides background execution and resource management. Autotask executes the shrink task automatically at a system-determined interval and within a bounded runtime.

See Also:

Managing Automated Database Maintenance Tasks

Integrates with Pre-Allocation

Automatic SecureFiles Shrink integrates with pre-allocation seamlessly without affecting performance. Automatic SecureFiles Shrink avoids the LOB segments that are recently pre-allocated. Segment pre-allocation is performed in the background for segments that have high demand for free space.

Works with DDL and DML

Automatic SecureFiles Shrink targets only idle segments and skips active LOB segments. User driven DDL and DML statements do not fail and face minimal performance impact when Automatic SecureFiles Shrink works in the background. If Automatic SecureFiles Shrink for a segment comes across locked rows, it skips the locked rows because locked rows are indicative of DML activity or waiting on locked rows may cause deadlocks with user transactions. Automatic SecureFiles Shrink always acquires row locks in the NOWAIT mode to avoid deadlock with user transactions.

Targets Idle LOB Segments

To avoid unnecessary block accesses, Automatic SecureFiles Shrink filters LOB segments based on information available in System Global Area (SGA). Automatic SecureFiles Shrink selects only idle LOB segments and skips active LOB segments to minimize performance impact on active LOB segments.

Covers All LOB Segments

A shrink task covers all LOB segments in a PDB over several intervals and this includes user-created LOB segments and the LOB segments that are created using features, such as JSON and DBFS.

Performs Shrinks in Iterations

Automatic SecureFiles Shrink does not free all the free space in the selected LOB segments at once. Instead, the shrink task frees a modest amount of space at every shrink call (iteration). The trickle threshold limit defines the amount of space to shrink in every iteration. Over time, the amount of free space in idle LOB segments approaches the minimum that is specified for pre-allocation.

Executes in the Background

All steps involved in Automatic SecureFiles Shrink, including the selection of LOB segments to shrink, run in the background. After Automatic SecureFiles Shrink is enabled, it comes into effect with the start of a database instance. No directive regarding how Automatic SecureFiles Shrink should operate is required.

Honors Undo Retention

Automatic SecureFiles Shrink respects the undo retention period. It does not allow a query to fail within the undo retention period because an affected LOB segment has been freed, relocated, or reused as a part of an Automatic SecureFiles Shrink task. Unexpired blocks are freed only after the undo retention time.

Parent topic: Automatic SecureFiles Shrink

16.4 SecureFiles Shrink and Undo Retention

This section discusses how the Auto Shrink feature treats different flavors of undo retention.

As part of the SecureFile Shrink feature, used blocks in an extent are relocated, and the extents are freed to the tablespace. After an extent is freed to the tablespace, it may be reused and overwritten. Freeing and reusing an extent during shrink can change the expected behavior of undo retention.

Table 16-1 SecureFile Shrink and Undo Retention

Undo Retention Flavor No DML Query Concurrent DML Queries

Undo Retention None

A long-running query does not fail with ORA-1551 Snapshots too old because the underlying data has not changed from the user's perspective.

Automatic SecureFiles Shrink evitably breaks the long-running queries.

If the DML query touches the LOB being queried, a query may fail at any time with ORA-1551 Snapshots too old.

Undo Retention Min (N Seconds)

A long-running query does not fail with ORA-1551 Snapshots too old because the underlying data has not changed from the user's perspective.

Automatic SecureFiles Shrink evitably breaks the long-running queries.

A DML query shorter than "N Seconds" does not fail with ORA-1551 Snapshots too old even though the DML modifies the LOB. Queries that last longer than "N Seconds" may fail.

If you want a different behavior, you need to increase the "N seconds".

Undo Retention Auto (Use UNDO_RETENTION)

A long-running query does not fail with ORA-1551 Snapshots too old because the underlying data has not changed from the user's perspective.

Automatic SecureFiles Shrink evitably breaks the long-running queries.

A DML query shorter than "UNDO_RETENTION Seconds" does not fail with ORA-1551 Snapshots too old even though the DML modifies the LOB. Queries that last longer than "UNDO_RETENTION Seconds" may fail.

If your want a different behavior, you need to increase the "N seconds".

Undo Retention Max

A long-running query does not fail with ORA-1551 Snapshots too old because the underlying data has not changed from the user's perspective.

The Automatic SecureFiles Shrink operation skips such LOB segments entirely.

When the LOB segment can grow, a query must not fail with ORA-1551 Snapshots too old even though the DML modifies the LOB. When the LOB segment is under space pressure, a query may fail with ORA-1551 Snapshots too old, if the DML modifies the LOB. There is no "N Second" period during which the query must succeed. This means in the worst case scenario, Undo Retention Max is less strict than Undo Retention Min or Undo Retention Auto.

Parent topic: Automatic SecureFiles Shrink

16.5 Targets and Limits

Automatic SecureFiles Shrink follows a set of targets and limits to operate optimally.

Parent topic: Automatic SecureFiles Shrink

16.5.1 Automatic SecureFiles Shrink Targets

An Automatic SecureFiles Shrink target determines the amount of space that needs to be freed in one automatic shrink run.

In manual shrink, the shrink command continues to free space until there is no free space left. Automatic SecureFiles Shrink sets a shrink target for every shrink run. The target is based on:

  • Pre-allocation threshold that determines the minimum limit below which an Automatic SecureFiles Shrink task cannot free space for a segment.

  • Trickle threshold that specifies the maximum limit that Automatic SecureFiles Shrink can free space for a LOB segment in one iteration.

Parent topic: Targets and Limits

16.5.1.1 Pre-Allocation Threshold

A pre-allocation threshold value is used to compute the targeted minimum free space for a LOB segment.

Pre-allocation threshold values are represented in percentages of free space out of the LOB segment size. For example, for a 2 TB LOB segment, the minimum amount of free space is 20 GB (2 TB * 1%). If the actual free space is less than the pre-allocation threshold, pre-allocation triggers in and adds extents to the segment. By default, unexpired space is not considered as free space.

Note:

Automatic SecureFiles Shrink does not free space that is less than the pre-allocation minimum threshold for a LOB segment.

Table 16-2 shows the default pre-allocation threshold values for different segment sizes.

Note:

You can adjust the thresholds using optimizations in the future Oracle Database releases.

Table 16-2 Pre-Allocation Threshold

Segment Size Actual Threshold Minimum Free Space

<= 1 GB

10%

100 MB

<= 10 GB

5%

500 MB

<= 100 GB

2%

2 GB

> 100 GB

1%

1 GB
16.5.1.2 Automatic SecureFiles Shrink Trickle Threshold

The trickle threshold controls the maximum amount of space per segment to be freed by one automatic shrink iteration.

The shrink run on a LOB segment frees up space incrementally. Therefore, the trickle threshold is an incremental shrink amount, with the shrink task freeing limited space in iterations without concurrent DML and DDL statements facing any noticeable performance impact due to higher latency.

Note:

Automatic SecureFiles Shrink does not free space that is less than the pre-allocation minimum threshold for a LOB segment.

16.5.1.3 Shrink Target Examples

The following are examples of how shrink targets are calculated for Automatic SecureFiles Shrink.

The shrink trickle threshold for the following examples is assumed to be 128 MB, although the actual shrink trickle threshold is an incremental shrink amount, where LOBs are reorganized incrementally.

Example 1: Segment Size = 1000 MB, Used Space = 600 MB, Unexpired Space = 0 MB

  • Intermediate value based on pre-allocation threshold: 600 MB / (1 - 10%) = 667 MB.

  • Intermediate value based on shrink trickle threshold (MB): 1000 MB – 128 MB = 872 MB.

  • Final Shrink Target = 1000 MB - Max(667 MB, 872 MB) = 128 MB.

Example 2: Segment Size = 1000 MB, Used Space = 600 MB, Unexpired Space = 300 MB.

  • Intermediate value based on pre-allocation threshold: (600 MB + 300 MB) / (1 - 10%) = 1000 MB.

  • Intermediate value based on shrink trickle threshold (MB): 1000 MB – 128 MB = 872 MB.

  • Final Shrink Target = 1000 MB - Max(1000 MB, 872 MB) = 0 MB.

  • Automatic SecureFiles Shrink skips this LOB segment.

16.5.2 LOB Segment Idle Time Limit

The LOB segment idle time limit is the minimum time limit a LOB segment can be idle before a shrink can start.

The LOB segment idle time limit specifies the minimum amount of time that a LOB segment must be idle to qualify for Automatic SecureFiles Shrink. An idle LOB segment is defined as one that has neither user-driven DML statements nor pre-allocation activity in the past N hours. LOB Segment Idle Time Limit defines the N hours. The default value for LOB segment idle time limit is 1440 minutes, which is 24 hours.

Parent topic: Targets and Limits

16.6 Selection Criteria for LOB Segments to Shrink

Here are the criteria that automatic SecureFiles shrink uses for selecting LOB segments to shrink.

The Automatic SecureFiles Shrink task excludes the following LOB segments when choosing the LOB segments to shrink:

  • The LOB segment is not an idle segment as per LOB Segment Idle Time Limit.

  • The LOB segment does not contain extra free space greater than the pre-allocation threshold.

  • The LOB segment has RETENTION MAX, which means the segment keeps as many unexpired blocks as possible.

  • The LOB segment is currently being shrunk.

  • The LOB segment does not have enough expired free space. For this purpose, Automatic SecureFiles Shrink treats unexpired space as used space.

  • The LOB segment has failed a previous shrink task. Previous shrink calls have failed to free space from the LOB segment. Automatic SecureFiles Shrink identifies the LOB segments that it failed to shrink previously and avoids such segments.

Parent topic: Automatic SecureFiles Shrink

16.7 Automatic SecureFiles Shrink Task

The Automatic SecureFiles Shrink task performs a series of steps to complete the shrink of LOB segments.

When enabled, a shrink task is performed as one instance of the background action performed on AutoTask. The task runs every 30 minutes and performs the following steps:

  1. A shrink task has 30 minutes at the start of the task. As the task progresses, it tracks both the time spent so far and the average duration of a shrink call. The latter is used to predict how long the next shrink call would take. If the time left is not enough for another call, the shrink task exits. If a shrink call goes over the 30-minute mark, it is terminated.
  2. Automatic SecureFiles Shrink fetches the next batch of LOB segments from internal catalog tables (which is ordered by objd). The last objd in the previous shrink task is used as the starting point for the next shrink task.
  3. Automatic SecureFiles Shrink applies the criteria filters from the Selection Criteria for SecureFiles LOB Segment to remove the segments that do not qualify for the shrink task.

    See Also:

    Selection Criteria for LOB Segments to Shrink

  4. Once the qualified segment is found, the shrink task can start work on the segment.
  5. Before starting the shrink, the shrink target is computed. The shrink target is based on the Pre-Allocation Threshold and the Automatic SecureFiles Shrink Trickle Threshold.
  6. Automatic SecureFiles Shrink runs the shrink command. The ALTER TABLE ... SHRINK SPACE command is executed using the OCI interface.
  7. Automatic SecureFiles Shrink updates the SGA structure for the "Timestamp for the next shrink" field. This field indicates the earliest time when Automatic SecureFiles Shrink can select this LOB segment again. If space was freed successfully, the timestamp uses the current time. Otherwise, the shrink is assigned a time in the future. If shrink is not successful, a penalty time is assessed to avoid Automatic SecureFiles Shrink from selecting the same segment in future shrink tasks.

See Also:

Parent topic: Automatic SecureFiles Shrink

16.7.1 Shrink Task Example

Here is an example of an Automatic SecureFiles Shrink task.

First Iteration at 0:00

  • Fetch a LOB segment from the internal catalog table and determine if it is a candidate (fulfills the selection criteria).

  • Run the SHRINK command. The shrink iteration finishes after 10 minutes. Update the SGA structure.

  • Determine that there is enough time to shrink another LOB segment (Assuming that there is enough time for the second shrink).

  • The second shrink finishes after 15 minutes. Automatic SecureFiles Shrink determines that there is no time left, so it exits.

Second Iteration at 0:30

  • The first shrink finishes after 20 minutes. Automatic SecureFiles Shrink determines that there is no time left for another shrink.

Third Iteration at 1:00

  • Keep fetching the next LOB segment from the internal catalog table until it finds a candidate. Run the SHRINK command and update the SGA structure.

  • Repeat the first step in the third iteration until there is no time left.

Parent topic: Automatic SecureFiles Shrink Task

16.8 Checking Progress

The most straightforward way of checking the result of Automatic SecureFiles Shrink is to compute the combined size of the LOB segments in the database. Alternatively, you can observe space saving at a micro level using the V$SECUREFILE_SHRINK table, which reports one row for each LOB segment that is shrunk.

See Also:

V$SECUREFILE_SHRINK to see the results of the previous shrink calls.

Parent topic: Automatic SecureFiles Shrink