Using Automatic Maintenance for an Index

5.8.1 About Automatic Maintenance

Indexes with automatic maintenance are synchronized in the background without any user intervention.

Overview

Index maintenance is the process of updating index data structures (in-memory and on-disk) as a result of performing DML operations.

Automatic maintenance is the default method for synchronizing Oracle Text CONTEXT and search indexes (Oracle Text, JSON, and XML search indexes) that are created in Oracle Database 23ai and later releases.

Both the automatic maintenance and synchronization (SYNC) methods involve processing pending updates, inserts, and deletes to the base table. However, the automatic maintenance and SYNC specifications are orthogonal. Automatic maintenance uses an asynchronous maintenance framework to perform SYNC operations in the background, and provides the following capabilities:

Eliminates time-based or manual SYNC operations:

In an automatic maintenance mode, IRnn background processes automatically perform index maintenance operations in an optimal manner. This feature internally determines an optimal synchronization interval (based on the DML arrival) and automatically schedules background SYNC operations, as required. You cannot override the automatically determined intervals.

For detailed information about this background mechanism process, see Asynchronous Maintenance Framework.
Reduces the frequency of background jobs:

Background processes maintain indexes rather than the database scheduler. The background mechanism breaks each CTX_DDL.SYNC_INDEX operation into separate events (sync stages) and launches each event only when needed.
Provides the default maintenance configuration:

These indexes do not require you to configure a SYNC type or set any synchronization interval. By default, indexes are configured with a combination of automatic maintenance and SYNC (MANUAL). No other SYNC settings are compatible with these indexes.

Note that the SYNC (MANUAL) behavior is different in this mode. Unlike the regular SYNC (MANUAL) type (where you must manually synchronize an index), here CTX_DDL.SYNC_INDEX is automatically called in the background at optimal intervals.

Why and When to Use Automatic Maintenance?

Oracle recommends that you use automatic maintenance in cases where sync requirements for indexes are not clear or you want to synchronize a large number of indexes in an optimal manner.

In addition to reducing the administrative tasks of managing your indexes, the benefit of using this framework is that it automatically determines when a background SYNC operation needs to be performed, by tracking the DML queue. It also provides more control over the frequency of different background jobs running at any given time, instead of creating independent jobs for each index or index partition per pluggable database (PDB). As a result, automatic maintenance helps in reducing the workload on database resources, eliminates scheduling conflicts, and enhances query performance.

With SYNC (EVERY), which also enables automatic background synchronization, you must manually specify sync interval using interval-string. Although SYNC (EVERY) allows you to explicitly control the synchronization interval, automatic maintenance provides an efficient usage of database resources especially when supporting multiple PDBs. In addition, SYNC (EVERY) may result in excessive launching of background sync jobs, based on the user's estimate of how frequently new index data may arrive.

What is Manual Maintenance?

Manual maintenance is a non-automatic maintenance mode that provides the pre-release 23ai synchronization behavior.

In a manual maintenance mode, you can specify SYNC types, such as SYNC MANUAL, SYNC EVERY interval-string, or SYNC ON COMMIT. The MAINTENANCE MANUAL index parameter sets your indexes to manual maintenance.

After upgrading to a new release, existing indexes continue to use the previously specified method of synchronization. For example, after upgrading to Oracle Database 23ai, existing indexes are set to manual maintenance with the previously specified SYNC settings. If you did not specify any SYNC setting before the upgrade, then the index uses the default SYNC type. That is, SYNC MANUAL for Oracle Text CONTEXT indexes and SYNC ON COMMIT for JSON and XML search indexes. If required, you can manually enable automatic maintenance for such indexes.

How to Configure the MAINTENANCE Parameter?

The MAINTENANCE parameter controls the maintenance type (mode) for your index. You can set the MAINTENANCE parameter globally and not per partition. This means that the maintenance type specified for an index applies to all index partitions.

The supported maintenance types are:

MAINTENANCE AUTO (default): Sets your indexes to automatic maintenance.
By default, you do not need to configure automatic maintenance while creating an index. This example creates a JSON search index that has the default behavior (no PARAMETERS clause):
```
CREATE SEARCH INDEX po_search_idx ON j_purchaseorder (po_document) 
    FOR JSON;
```
This example creates a JSON search index by explicitly specifying MAINTENANCE AUTO using the PARAMETERS clause:
```
CREATE SEARCH INDEX po_search_idx ON j_purchaseorder (po_document)
    FOR JSON PARAMETERS ('MAINTENANCE AUTO');
```
MAINTENANCE MANUAL: Sets your indexes to manual maintenance.
This example disables automatic maintenance on a new JSON search index by specifying MAINTENANCE MANUAL using the PARAMETERS clause:
```
CREATE SEARCH INDEX po_search_idx ON j_purchaseorder (po_document)
    FOR JSON PARAMETERS('MAINTENANCE MANUAL');
```

For detailed information about configuring these parameters, see Enabling and Disabling Automatic Maintenance.

You can switch between the automatic and manual maintenance modes using ALTER INDEX. This command alters only the synchronization options, and thus you do not need to rebuild the index. When set to manual maintenance, if you do not explicitly specify any SYNC type, then the index uses the default SYNC type.

5.8.2 Requirements and Restrictions for Automatic Maintenance

Review these requirements and restrictions (such as database compatibility, supported parameter combinations, and supported indexes) when using the automatic maintenance mode.

Database compatibility for the asynchronous maintenance framework is Oracle Database 21.0.0.0 and later.
The combination of automatic maintenance with the following parameters is not supported:
- FAST_QUERY
- ASYNCHRONOUS_UPDATE
- TRANSACTIONAL
- SYNC (ON COMMIT) and SYNC (EVERY)
Running any of the preceding combinations results in an error, prompting you to use a compatible mode for your index.
Shadow indexes do not support automatic maintenance.
Sharded search indexes do not support automatic maintenance.

If you explicitly use the MAINTENANCE AUTO keyword to specify automatic maintenance (using CREATE SEARCH INDEX) or switch to MAINTENANCE AUTO (using ALTER INDEX), then an ORA-2049 or ORA-1591 error occurs.

If you do not use the MAINTENANCE AUTO keyword to create or alter sharded search indexes, then a MAINTENANCE MANUAL index is created with the following SYNC and OPTIMIZE preferences:
- SYNC:
  
  The specified SYNC type is used.
  
  If the SYNC type is not specified, then the default synchronization method is used. The default synchronization method for Oracle Text search indexes is set to SYNC (EVERY "freq=minutely; interval=3") (indexes are synchronized every 3 minutes). The default synchronization method for JSON search indexes and XML search indexes is set to SYNC (On Commit).
- OPTIMIZE:
  
  The specified OPTIMIZE type is used.
  
  If the OPTIMIZE type is not specified, then the default optimize method is used. The default optimize method is set to OPTIMIZE (MANUAL) for all search indexes.

5.8.3 Asynchronous Maintenance Framework

In an automatic maintenance mode, IRnn background processes perform index maintenance operations, which provides a better scalability of background jobs and enhances query performance.

List of Maintenance Events

Each SYNC operation consists of separate events (stages) that can concurrently run in the background.

Event Description

Event	Description
`SYNC-Mapping` (Sync-M)	Reads the $B catalog table to find the next DocID, and allocates DocID to each ROWID. Next, it reads the contents of the $C commit journal, sorts it on ROWID, and decides which DocIDs must be removed or added. Next, it performs deletes and inserts on the $K mapping table. Then, it adds the removed DocIDs to the $N garbage collection table. Finally, it deletes all the rows read from $C. During a failure, these events are retried and also broadcasted to other Oracle Real Application Clusters (Oracle RAC) nodes.
`SYNC-Mapping Timeout` (Sync-MT)	Specifies the Sync-M timeout events, generated on a commit. These are processed only by timeout actions and not by interrupt actions. During a timeout, the Sync-MT events are converted into the Sync-M events.
`SYNC-Ranges` (Sync-R)	Assigns DocID ranges generated by Sync-M as `READY` for postings stage. It reads all available `NEW` ranges to determine the minimum of the first DocID and the maximum on the last DocID. Next, it deletes all `NEW` ranges and inserts a number of equally-sized `READY` ranges. The size of the range is determined based on the average size of each document for the index or index partition. During a failure, these events are retried and also broadcasted to other RAC nodes.
`SYNC-Scheduler` (Sync-S)	Schedules Sync-P based on the number of `READY` ranges generated by Sync-R. Depending on the value, it schedules either a serial or concurrent Sync-P event.
`SYNC-Postings` (Sync-P)	Generates postings lists that contain new index data. Sync-P starts by getting a `READY` range. During scheduling, it decides the number of workers for running the event. On Oracle RAC systems, concurrent events are also broadcasted to other nodes for running as the `SYNC-Postings Concurrent` (Sync-PC) events. During a failure, the Sync-S events are scheduled with increased iteration. On Oracle RAC systems, the failed Sync-PC and `SYNC-Postings Serial` (Sync-PS) events are broadcasted as Sync-PS events.
`SYNC-Postings Serial` (Sync-PS)	Runs Sync-P serially. The postings are generated in SGA batches.
`SYNC-Postings Concurrent` (Sync-PC)	Runs Sync-P concurrently. Can schedule multiple ranges to run concurrently and independently without contention.
`SYNC-Writer` (Sync-W)	Writes SGA batches of postings lists to disk. Sync-W events can run concurrently with itself. However, these events cannot run with `SYNC-Cleanup batches` (Sync-C). Sync-W events are never broadcasted to other RAC nodes because they process SGA batches, which are local to the node that generated them.
`SYNC-Cleanup batches` (Sync-C)	Cleans up `WRITE` ranges in $B that do not have any SGA batches associated with them due to a failure. These events are retried in the subsequent run of Sync-P.
`SYNC-Inspect` (Sync-I)	Inspects index and index partitions (checks the $C and $B tables) to find if any events are missing. During a failure, the Sync-I events are not retried and are also not broadcasted to other RAC nodes.
`MONITOR`	Schedules the Sync-I events for each index or index partition and the EClean events for each pluggable database (PDB). The Scheduler schedules the actual Monitor event, which is further processed by the Monitor worker.
`EVENT Stats` (EStat)	Terminal event that is processed by Writer workers (Sync-W events). It writes event stats for all completed events in a PDB.
`EVENT Stats Clean up` (EClean)	Cleans up persisted event stats (older than the PDB-specific threshold) from the dictionary.
`OPTIMIZE-Scheduler Timeout` (Opti-ST)	Prevents dealing with large postings lists gaps that are crated after the order completion of the Sync-W events.
`OPTIMIZE-Scheduler` (Opti-S)	Determines the maximum DocID for which there are no gaps in postings lists due to the out-of-order processing of the `WRITE` ranges by the Sync-W events. The Opti-S event aggregates $G token counts that are stored in $B by the Sync-W events, and it schedules the Opti-M event when the aggregate count reaches a user-specified threshold.
`OPTIMIZE-Merge` (Opti-M)	Terminal event that does not perform the actual `MERGE` operation. Instead, it schedules the `DBMS_SCHEDULER` Optimize Merge operation.

SYNC-Mapping

(Sync-M)

Reads the $B catalog table to find the next DocID, and allocates DocID to each ROWID. Next, it reads the contents of the $C commit journal, sorts it on ROWID, and decides which DocIDs must be removed or added. Next, it performs deletes and inserts on the $K mapping table. Then, it adds the removed DocIDs to the $N garbage collection table. Finally, it deletes all the rows read from $C.

During a failure, these events are retried and also broadcasted to other Oracle Real Application Clusters (Oracle RAC) nodes.

SYNC-Mapping Timeout

(Sync-MT)

Specifies the Sync-M timeout events, generated on a commit. These are processed only by timeout actions and not by interrupt actions.

During a timeout, the Sync-MT events are converted into the Sync-M events.

SYNC-Ranges

(Sync-R)

Assigns DocID ranges generated by Sync-M as READY for postings stage. It reads all available NEW ranges to determine the minimum of the first DocID and the maximum on the last DocID. Next, it deletes all NEW ranges and inserts a number of equally-sized READY ranges. The size of the range is determined based on the average size of each document for the index or index partition.

During a failure, these events are retried and also broadcasted to other RAC nodes.

SYNC-Scheduler

(Sync-S)

Schedules Sync-P based on the number of READY ranges generated by Sync-R. Depending on the value, it schedules either a serial or concurrent Sync-P event.

SYNC-Postings

(Sync-P)

Generates postings lists that contain new index data. Sync-P starts by getting a READY range. During scheduling, it decides the number of workers for running the event.

On Oracle RAC systems, concurrent events are also broadcasted to other nodes for running as the SYNC-Postings Concurrent (Sync-PC) events.

During a failure, the Sync-S events are scheduled with increased iteration. On Oracle RAC systems, the failed Sync-PC and SYNC-Postings Serial (Sync-PS) events are broadcasted as Sync-PS events.

SYNC-Postings Serial

(Sync-PS)

Runs Sync-P serially. The postings are generated in SGA batches.

SYNC-Postings Concurrent

(Sync-PC)

Runs Sync-P concurrently. Can schedule multiple ranges to run concurrently and independently without contention.

SYNC-Writer

(Sync-W)

Writes SGA batches of postings lists to disk. Sync-W events can run concurrently with itself. However, these events cannot run with SYNC-Cleanup batches (Sync-C).

Sync-W events are never broadcasted to other RAC nodes because they process SGA batches, which are local to the node that generated them.

SYNC-Cleanup batches

(Sync-C)

Cleans up WRITE ranges in $B that do not have any SGA batches associated with them due to a failure. These events are retried in the subsequent run of Sync-P.

SYNC-Inspect

(Sync-I)

Inspects index and index partitions (checks the $C and $B tables) to find if any events are missing.

During a failure, the Sync-I events are not retried and are also not broadcasted to other RAC nodes.

MONITOR

Schedules the Sync-I events for each index or index partition and the EClean events for each pluggable database (PDB). The Scheduler schedules the actual Monitor event, which is further processed by the Monitor worker.

EVENT Stats

(EStat)

Terminal event that is processed by Writer workers (Sync-W events). It writes event stats for all completed events in a PDB.

EVENT Stats Clean up

(EClean)

Cleans up persisted event stats (older than the PDB-specific threshold) from the dictionary.

OPTIMIZE-Scheduler Timeout

(Opti-ST)

Prevents dealing with large postings lists gaps that are crated after the order completion of the Sync-W events.

OPTIMIZE-Scheduler

(Opti-S)

Determines the maximum DocID for which there are no gaps in postings lists due to the out-of-order processing of the WRITE ranges by the Sync-W events.

The Opti-S event aggregates $G token counts that are stored in $B by the Sync-W events, and it schedules the Opti-M event when the aggregate count reaches a user-specified threshold.

OPTIMIZE-Merge

(Opti-M)

Terminal event that does not perform the actual MERGE operation. Instead, it schedules the DBMS_SCHEDULER Optimize Merge operation.

Background Mechanism Process

The main Scheduler background process checks for the workload from all indexes at predefined intervals (that is, every 3 seconds by default). It then assigns the workload to a Worker background process, which reads events one at a time and processes them based on the event type. The index is synchronized immediately after the Worker process runs CTX_DDL.SYNC_INDEX. Apart from the Scheduler and Worker processes, the Monitor background process helps in recovering lost events.

A CTX_DDL.SYNC_INDEX call performs the following steps in an order:

Resets all waiting events for an index or index partition:

When an event fails, it adds the event to the Waiting Queue with progressively increasing delay. Even after the issue is corrected, the event continues to wait for the current delay to elapse. To track such delays, it schedules retry events at an incremented level. This means that the Scheduler process first moves a retry event into an Event Queue. From the Event Queue, the Scheduler moves it into the Waiting Queue, then to the Ready Queue, and finally allocates the event to the Worker process.

In addition to incremental levels, each event has a retry iteration. On long retries (that is, when a retry event is not the same as the original event), it increments the iteration instead of the level and initializes the level to the iteration.

CTX_DDL.SYNC_INDEX can force immediate re-execution of the event, which moves all relevant Waiting Queue events to the Event Queue and also resets the level and iteration. If the event fails again, then it restarts from the starting incremental level or iteration.
Performs Sync-M in the foreground:

CTX_DDL.SYNC_INDEX waits for the background maintenance to finish. However, instead of waiting for all events to finish, it calls Sync-M in the foreground and gets the maximum DocID that is allocated. It uses this DocID to ignore all future events.
Schedules other stages of SYNC in the background:

CTX_DDL.SYNC_INDEX posts the Scheduler process so that it can immediately start processing pending events.
Waits for the completion of background processing:

The waiting is controlled by the locking parameter when it is set to CTX_DDL.LOCK_WAIT. For all other values, CTX_DDL.SYNC_INDEX returns after completing Sync-M.

The values of the memory, parallel_degree, maxtime, and direct_path parameters are ignored.

If some background events are delayed or cannot complete, then CTX_DDL.SYNC_INDEX returns ORA-30608 and logs an error message in the catalog views.

Differences in SYNC Behavior Between Automatic Maintenance and Manual Maintenance

Compare the differences in the synchronization behavior between automatic maintenance and manual maintenance, and how different events are processed during a CTX_DDL.SYNC_INDEX operation:

Behavior Automatic Maintenance Manual Maintenance

Behavior	Automatic Maintenance	Manual Maintenance
Background mechanism	In an automatic maintenance mode, background processes maintain indexes. The background mechanism breaks each `SYNC` operation into separate events that are run concurrently with each other, as needed. For example, Sync-S launches Sync-P to pick up new index data only when Sync-R generates `READY` ranges.	In a manual maintenance mode, the `DBMS_SCHEDULER` background jobs maintain indexes. The background mechanism implements all sync events (Sync-M, Sync-R, and Sync-P) together as a single `SYNC` operation.
`SYNC` types	These indexes are preconfigured with a combination of automatic maintenance and `SYNC (MANUAL)`. Unlike the regular `SYNC (MANUAL)` type (where you must manually call `CTX_DDL.SYNC_INDEX`), here `CTX_DDL.SYNC_INDEX` is automatically called in the background at optimal intervals. The other `SYNC` types, such as `SYNC ON COMMIT` and `SYNC EVERY` are not supported with automatic maintenance.	Running `SYNC (MANUAL)`, `SYNC (ON COMMIT)`, or `SYNC (EVERY)` launches a `SYNC` operation in the foreground for each index or index partition.
Catalog views	`CTX_BACKGROUND_EVENTS` `CTX_USER_BACKGROUND_EVENTS` `V$TEXT_WAITING_EVENTS`	`CTX_AUTOSYNC_JOBS` `CTX_AUTOSYNC_STATUS` `CTX_USER_AUTOSYNC_JOBS` `CTX_USER_AUTOSYNC_STATUS`

Background mechanism

In an automatic maintenance mode, background processes maintain indexes. The background mechanism breaks each SYNC operation into separate events that are run concurrently with each other, as needed.

For example, Sync-S launches Sync-P to pick up new index data only when Sync-R generates READY ranges.

In a manual maintenance mode, the DBMS_SCHEDULER background jobs maintain indexes. The background mechanism implements all sync events (Sync-M, Sync-R, and Sync-P) together as a single SYNC operation.

SYNC types

These indexes are preconfigured with a combination of automatic maintenance and SYNC (MANUAL).

Unlike the regular SYNC (MANUAL) type (where you must manually call CTX_DDL.SYNC_INDEX), here CTX_DDL.SYNC_INDEX is automatically called in the background at optimal intervals.

The other SYNC types, such as SYNC ON COMMIT and SYNC EVERY are not supported with automatic maintenance.

Running SYNC (MANUAL), SYNC (ON COMMIT), or SYNC (EVERY) launches a SYNC operation in the foreground for each index or index partition.

Catalog views

CTX_BACKGROUND_EVENTS
CTX_USER_BACKGROUND_EVENTS
V$TEXT_WAITING_EVENTS

CTX_AUTOSYNC_JOBS
CTX_AUTOSYNC_STATUS
CTX_USER_AUTOSYNC_JOBS
CTX_USER_AUTOSYNC_STATUS

Related Topics

Monitoring Maintenance Events and Errors

5.8.4 Enabling and Disabling Automatic Maintenance

Automatic maintenance is enabled by default for new Oracle Text CONTEXT and search indexes. Learn how to explicitly specify automatic maintenance while creating an index, or disable it to override the default behavior and enable SYNC instead.

To explicitly specify automatic maintenance for a new index, use the MAINTENANCE AUTO keyword in the PARAMETERS clause of the CREATE INDEX or CREATE SEARCH INDEX statement.

For an Oracle Text index:

CREATE INDEX CTX_IDX ON CTX_TAB(DOC)
    INDEXTYPE IS CTXSYS.CONTEXT 
    PARAMETERS('MAINTENANCE AUTO');

For an Oracle Text search index:

CREATE SEARCH INDEX CTX_IDX ON CTX_TAB(DOC)
    PARAMETERS('MAINTENANCE AUTO');

For a JSON search index:

CREATE SEARCH INDEX JSON_IDX ON CTX_TAB(JSON_DOC) FOR JSON
    PARAMETERS('MAINTENANCE AUTO');

For an XML search index:

CREATE SEARCH INDEX XML_IDX ON CTX_TAB(XML_DOC) FOR XML
    PARAMETERS('MAINTENANCE AUTO');

To disable automatic maintenance for a new index, use the MAINTENANCE MANUAL keyword in the CREATE INDEX or CREATE SEARCH INDEX clause.

This will set your index to manual maintenance. If you do not specify any SYNC type, then the index will use the default SYNC settings. For example, SYNC MANUAL for Oracle Text CONTEXT indexes and SYNC ON COMMIT for JSON and XML search indexes.
- For an Oracle Text index:
```
CREATE INDEX CTX_IND ON CTX_TAB(DOC) 
INDEXTYPE IS CTXSYS.CONTEXT 
PARAMETERS('MAINTENANCE MANUAL');
```
- For an Oracle Text search index:
```
CREATE SEARCH INDEX CTX_IDX ON CTX_TAB(DOC)
    PARAMETERS('MAINTENANCE MANUAL');
```
- For a JSON search index:
```
CREATE SEARCH INDEX JSON_IDX ON CTX_TAB(JSON_DOC) FOR JSON
    PARAMETERS('MAINTENANCE MANUAL');
```
- For an XML search index:
```
CREATE SEARCH INDEX XML_IDX ON CTX_TAB(XML_DOC) FOR XML
    PARAMETERS('MAINTENANCE MANUAL');
```
To override the default SYNC settings for the index set to manual maintenance, specify the required SYNC type:
- MANUAL: To manually synchronize the index on demand.
  For example:
```
ALTER INDEX CTX_IDX REBUILD
    PARAMETERS(‘REPLACE METADATA MAINTENANCE MANUAL);
```
- ON COMMIT: To synchronize the index immediately after a commit.
  For example:
```
ALTER INDEX CTX_IDX REBUILD 
    PARAMETERS('REPLACE METADATA SYNC(ON COMMIT) MAINTENANCE MANUAL');
```
- EVERY "interval-string": To synchronize the index at a regular interval.
  For example, starting every 20 minutes:
```
ALTER INDEX CTX_IDX REBUILD 
    PARAMETERS('REPLACE METADATA SYNC(EVERY "freq=minutely;interval=20") MAINTENANCE MANUAL');
```

Related Topics

5.8.5 Switching between Automatic and Manual Maintenance

You can use ALTER INDEX to switch between MAINTENANCE AUTO and MAINTENANCE MANUAL, without rebuilding the index. While changing modes, you must specify compatible Maintenance type and SYNC type combinations.

Guidelines for Switching between Modes

MAINTENANCE AUTO is supported with SYNC (MANUAL). By default, all indexes with automatic maintenance are specified with this combination.
A combination of MAINTENANCE AUTO and SYNC ON COMMIT or SYNC (EVERY) is not supported. If you want to specify MAINTENANCE AUTO for indexes that also use SYNC (ON COMMIT) or SYNC (EVERY), then you must first set such indexes to SYNC (MANUAL).

Static dictionary view CTX_USER_INDEXES contains information about existing Oracle Text CONTEXT and search indexes for the current user. For example, this query lists the SYNC and Maintenance types for an Oracle Text index set to MAINTENANCE AUTO:

SQL> SELECT IDX_NAME, IDX_SYNC_TYPE, IDX_MAINTENANCE_TYPE FROM CTX_USER_INDEXES;

IDX_NAME                IDX_SYNC_TYPE                IDX_MAINTENANCE_TYPE
----------------------- ---------------------------- ----------------------
DOCIDX                  MANUAL                       AUTO

Switching Indexes to Automatic Maintenance

This table provides ALTER INDEX examples with various SYNC types, while altering your index from MAINTENANCE MANUAL to MAINTENANCE AUTO:

Setting Maintenance and SYNC Type Example

Setting	Maintenance and SYNC Type	Example
`SYNC (MANUAL)` to `MAINTENANCE AUTO`	`IDX_SYNC_TYPE: MANUAL` `IDX_MAINTENANCE_TYPE: AUTO`	`ALTER INDEX CTX_IDX REBUILD PARAMETERS(‘REPLACE METADATA MAINTENANCE AUTO’);`
`SYNC ON COMMIT` to `MAINTENANCE AUTO`	`IDX_SYNC_TYPE: ON COMMIT` `IDX_MAINTENANCE_TYPE: AUTO`	`ALTER INDEX CTX_IDX REBUILD PARAMETERS(‘REPLACE METADATA SYNC (MANUAL) MAINTENANCE AUTO’);`
`SYNC (EVERY)` to `MAINTENANCE AUTO`	`IDX_SYNC_TYPE: EVERY` `IDX_MAINTENANCE_TYPE: AUTO`	`ALTER INDEX CTX_IDX REBUILD PARAMETERS(‘REPLACE METADATA SYNC (MANUAL) MAINTENANCE AUTO’);`

SYNC (MANUAL)

to

MAINTENANCE AUTO

IDX_SYNC_TYPE: MANUAL

IDX_MAINTENANCE_TYPE: AUTO

ALTER INDEX CTX_IDX REBUILD
    PARAMETERS(‘REPLACE METADATA MAINTENANCE AUTO’);

SYNC ON COMMIT

to

MAINTENANCE AUTO

IDX_SYNC_TYPE: ON COMMIT

IDX_MAINTENANCE_TYPE: AUTO

ALTER INDEX CTX_IDX REBUILD
    PARAMETERS(‘REPLACE METADATA SYNC (MANUAL) MAINTENANCE AUTO’);

SYNC (EVERY)

to

MAINTENANCE AUTO

IDX_SYNC_TYPE: EVERY

IDX_MAINTENANCE_TYPE: AUTO

ALTER INDEX CTX_IDX REBUILD
    PARAMETERS(‘REPLACE METADATA SYNC (MANUAL) MAINTENANCE AUTO’);

Switching Indexes to Manual Maintenance

This table provides ALTER INDEX examples with various SYNC types, while altering your index from MAINTENANCE AUTO to MAINTENANCE MANUAL:

Setting Maintenance and SYNC Type Example

Setting	Maintenance and SYNC Type	Example
`MAINTENANCE AUTO` to `SYNC (MANUAL)`	`IDX_SYNC_TYPE: MANUAL` `IDX_MAINTENANCE_TYPE: MANUAL`	`ALTER INDEX CTX_IDX REBUILD PARAMETERS(‘REPLACE METADATA MAINTENANCE MANUAL);` The default `SYNC` type for a `CONTEXT` index is `MANUAL`.
`MAINTENANCE AUTO` to `SYNC ON COMMIT`	`IDX_SYNC_TYPE: ON COMMIT` `IDX_MAINTENANCE_TYPE: MANUAL`	`ALTER INDEX CTX_IDX REBUILD PARAMETERS('REPLACE METADATA SYNC(ON COMMIT) MAINTENANCE MANUAL');`
`MAINTENANCE AUTO` to `SYNC (EVERY)`	`IDX_SYNC_TYPE: EVERY` `IDX_MAINTENANCE_TYPE: MANUAL`	`ALTER INDEX CTX_IDX REBUILD PARAMETERS(‘REPLACE METADATA MAINTENANCE MANUAL); ALTER INDEX CTX_IDX REBUILD PARAMETERS(‘REPLACE METADATA SYNC(EVERY "freq=minutely;interval=20")’);`

MAINTENANCE AUTO

to

SYNC (MANUAL)

IDX_SYNC_TYPE: MANUAL

IDX_MAINTENANCE_TYPE: MANUAL

ALTER INDEX CTX_IDX REBUILD
    PARAMETERS(‘REPLACE METADATA MAINTENANCE MANUAL);

The default SYNC type for a CONTEXT index is MANUAL.

MAINTENANCE AUTO

to

SYNC ON COMMIT

IDX_SYNC_TYPE: ON COMMIT

IDX_MAINTENANCE_TYPE: MANUAL

ALTER INDEX CTX_IDX REBUILD 
    PARAMETERS('REPLACE METADATA SYNC(ON COMMIT) MAINTENANCE MANUAL');

MAINTENANCE AUTO

to

SYNC (EVERY)

IDX_SYNC_TYPE: EVERY

IDX_MAINTENANCE_TYPE: MANUAL

ALTER INDEX CTX_IDX REBUILD
    PARAMETERS(‘REPLACE METADATA MAINTENANCE MANUAL);
ALTER INDEX CTX_IDX REBUILD 
    PARAMETERS(‘REPLACE METADATA SYNC(EVERY "freq=minutely;interval=20")’);

Related Topics

Oracle Text Reference

5.8.6 Monitoring Maintenance Events and Errors

The SYS and CTXSYS users can query catalog views to monitor the status of all background maintenance events for indexes with automatic maintenance.

In an automatic maintenance mode, indexes are asynchronously maintained without any user intervention. Oracle recommends that you periodically examine the CTX and dynamic performance views to know the status of all background maintenance events that are complete or delayed.

Querying Data and Status about Maintenance Events

Use the following views to monitor events at an index or index partition level:

CTX_BACKGROUND_EVENTS:

This Oracle Text view displays historical information about the execution of events for the SYS or CTXSYS user.
CTX_USER_BACKGROUND_EVENTS:

This Oracle Text view displays historical information about the execution of events for the current user, based on the index owner.
V$TEXT_WAITING_EVENTS:

This dynamic performance view displays historical information about events that are delayed or cannot complete due to errors or contentions.

For example, you can query the index object number and name, index owner number and name, base table (or table partition) object number and name, type of event (such as SYNC-Postings, SYNC-Mapping, SYNC-Ranges, and so on), status of the event (such as successful, running, failed, and so on), status of the retry iterations (such as retry delays, waiting time, and so on), elapsed time since the event started waiting, logged error messages, and so on.

Handling Errors

When the system encounters an indexing error (such as an index failure, event delay, or event retry), it logs the error in a catalog view. The error is not directly reported to the user. You must periodically query views to examine such errors and take corrective actions, as follows:

Some errors are transient and do not reproduce on a retry. Such error types do not require user intervention.
Some failed events may automatically succeed after a retry. If a retry event does not succeed, then try restarting the event from another event.

For example, when SYNC-Postings (Sync-P) fails after a retry, you can restart SYNC-Scheduler (Sync-S) so that the system can schedule a serial or concurrent operation. A successful completion of Sync-S clears the queue for Sync-P, and Sync-P immediately runs at a starting level without overloading the system.
If an event does not succeed even after periodic retries, then contact your database administrator.
To limit the load on the system due to periodic retries, the delays between successive retries may progressively increase.

To track such delays, all retry events are scheduled at an incremented level. This means that the Scheduler process first moves a retry event into an Event Queue. From the Event Queue, the Scheduler process moves it into the Waiting Queue, then to the Ready Queue, and finally allocates the event to the Worker process.

In addition to incremental levels, each event has a retry iteration. On long retries (that is, when a retry event is not the same as the original event), the iteration is incremented instead of the level and the level is initialized to the iteration.
On Oracle Real Application Clusters (Oracle RAC) systems, there may be errors that occur only on some nodes but not on others. In such cases, an event may successfully complete only when the event is sent to the other nodes.

Related Topics