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.
- About Manual SecureFiles Shrink
- About Automatic SecureFiles Shrink
- Automatic SecureFiles Shrink Features
Automatic SecureFiles Shrink has the following features: - SecureFiles Shrink and Undo Retention
This section discusses how the Auto Shrink feature treats different flavors of undo retention. - Targets and Limits
- Selection Criteria for LOB Segments to Shrink
Here are the criteria that automatic SecureFiles shrink uses for selecting LOB segments to shrink. - Automatic SecureFiles Shrink Task
The Automatic SecureFiles Shrink task performs a series of steps to complete the shrink of LOB segments. - Checking Progress
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.
shrink_clause
on SecureFile LOB
segments for release 21c and onward. There are two ways to invoke the
shrink_clause
:
-
This command targets a specific LOB column and all its partitions.
ALTER TABLE <table_name> MODIFY LOB <lob_column> SHRINK SPACE
-
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:
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.
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 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 |
Undo Retention Min (N Seconds) |
A long-running query does not fail with Automatic SecureFiles Shrink evitably breaks the long-running queries. |
A DML query shorter than "N Seconds" does not fail with
If you want a different behavior, you need to increase the "N seconds". |
Undo Retention Auto (Use
|
A long-running query does not fail with Automatic SecureFiles Shrink evitably breaks the long-running queries. |
A DML query shorter than "UNDO_RETENTION Seconds" does
not fail with If your want a different behavior, you need to increase the "N seconds". |
Undo Retention Max |
A long-running query does not fail with The Automatic SecureFiles Shrink operation skips such LOB segments entirely. |
When the LOB segment can grow, a query must not fail
with |
Parent topic: Automatic SecureFiles Shrink
16.5 Targets and Limits
Automatic SecureFiles Shrink follows a set of targets and limits to operate optimally.
- Automatic SecureFiles Shrink Targets
An Automatic SecureFiles Shrink target determines the amount of space that needs to be freed in one automatic shrink run. - 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.
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.
- Pre-Allocation Threshold
A pre-allocation threshold value is used to compute the targeted minimum free space for a LOB segment. - Automatic SecureFiles Shrink Trickle Threshold
The trickle threshold controls the maximum amount of space per segment to be freed by one automatic shrink iteration. - Shrink Target Examples
The following are examples of how shrink targets are calculated for Automatic SecureFiles Shrink.
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 |
Parent topic: Automatic SecureFiles Shrink Targets
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.
Parent topic: Automatic SecureFiles Shrink Targets
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.
Parent topic: Automatic SecureFiles Shrink Targets
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:
- 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.
- 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.
- 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.
- Once the qualified segment is found, the shrink task can start work on the segment.
- 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.
- Automatic SecureFiles Shrink runs the shrink command. The
ALTER TABLE ... SHRINK SPACE
command is executed using the OCI interface. - 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.
- Shrink Task Example
Here is an example of an Automatic SecureFiles Shrink task.
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