Space Manager with LiveReorg 9.1 - User Guide

Stages of a Live Reorganization

This section describes the stages of a live reorganization. To monitor these stages as they are performed, use the Live Reorg Detail pane of the Scripts/Job Monitor. See Monitor Live Reorganizations for more information.

Stage 1 — Create copy table

In the first stage of a live reorganization, a structural copy of the original table is created and all existing data is copied from the original table to the copy table:

• If an entire table is being reorganized, the copy table is a structural copy of the original table. All of the original table’s data is copied.
• If an individual partition or subpartition is being reorganized, the copy table is a structural copy of the parent-partitioned table, but only data for the original partition or subpartition is copied.

Data is reorganized as it is inserted into the copy table. The copy table uses the storage attributes specified in Reorg Manager or the Partitioning Wizard.

As the copy table is created and existing data are copied, live DML activity can continue against the original table. QSA collects information on live transactions using collection triggers. One trigger is used for most transactions. A second is used for direct UPDATES to LOBs.

The information collected by triggers is stored in a collection table. The table is automatically created in the tablespace being used for LiveReorg objects. This is specified with QSA’s LW_TABLESPACE parameter and is identified in documentation as the “LiveReorg tablespace”. See LiveReorg Objects and Their Tablespace for more information.

In the third stage of a live reorganization, QSA posts (copies) live transactions to the reorganized copy table based on information in the collection table.

Stage 2 — Create Posting Index

Once the reorganized copy table is in place, the first index is recreated. This index serves as the posting index. The posting index is used when live DML transactions are posted to the reorganized copy table. It can be used for one or two purposes, depending on whether it is a selective or nonselective index:

• Selective posting indexes are used to ensure that rows are uniquely identified so that live transactions are posted to the correct rows. A selective index is one where every index entry points to a single location in table data. The PRIMARY KEY and unique indexes are selective indexes. A nonselective index is one where each index entry points to multiple locations in table data.
• All posting indexes are used to speed up the posting process by increasing the efficiency of searches on the copy table.

What type of index is used as the posting index depends on what is available for a table. Space Manager tries to use the most selective index. How the posting index is chosen is covered under “Posting Indexes”.

As the posting index is created on the reorganized copy table, QSA continues collecting live DML transactions.

Stage 3 — Post Live Transactions

Once the posting index and its constraints are in place on the reorganized copy table, QSA begins posting live transactions to the copy table. Posting continues throughout Stage 3.

Additional tasks for this stage consist of recreating all remaining indexes. They can also include recreating constraints, privileges, and comments. Which of these tasks are performed depends on object type and switch style.

How Posting Progresses

1. First, QSA posts the live DML transactions collected during Stages 1 and 2. This allows the copy table to “catch up” with changes made to the original table in these stages.
2. Next, the agent continually collects and posts the live transactions made against the original table as Stage 3 proceeds.
3. Finally, after all tasks for Stage 3 have been performed, the agent posts any last-minute transactions that remain in the collection table. Once the collection table is empty and there are no remaining transactions, the original table and reorganized copy table are in sync and ready for the switch. Up until the switch is made, the agent continues collecting and posting live transactions, keeping the original and copy tables in sync.

How Transactions Are Posted

QSA posts live transactions using a PL/SQL stored procedure and Oracle Call Interface (OCI) calls. The OCI calls are used for data in LONG columns. The procedure and calls read transactions in chronological order, oldest transactions first. See The Posting Procedure and OCI Calls for more information.

Stage 4 — Make the Switch

In the switch stage of a live reorganization, the original table and its indexes are replaced with the reorganized copy table and its indexes. When the online switch is used, the switch is the only task performed during the switch stage. When the T-Lock switch is used, additional tasks are always performed. These depend on whether a table or partition is being reorganized. See Estimate the Time Needed for a Live Reorganization for more information.

Additional Tasks for a T-Lock Switch for Tables

When a table is reorganized with the T-Lock switch, additional tasks for the switch stage include:

• Recreating constraints, triggers and synonyms.
• Recompiling dependencies when the While the table is protected by T-Lock option is selected. (Dependencies are recompiled after the switch stage when this option is not selected. This can significantly shorten the switch stage for tables with hundreds of dependencies.)

Additional Tasks for a T-Lock Switch for Partitions

When a partition or subpartition is reorganized with the T-Lock switch, additional tasks for the switch stage include:

• Recreating or rebuilding global indexes on the parent-partitioned table.
• Recreating FOREIGN KEYs that reference the parent-partitioned table.
• Recompiling materialized views.
• Recompiling dependencies when indexes are recreated (instead of being rebuilt) and the While the table is protected by T-Lock option is selected. (Dependencies are compiled after the switch when this option is not selected.)

• Dropping a table’s temporary ROWID column, which causes its ROWID index to be dropped. This task is performed when a ROWID index is used as the posting index.

  Note When a (sub)partition is reorganized live, triggers, synonyms, and most dependencies are left in place on the parent table. As a result, they do not need to be recreated or recompiled. However, dependencies must be recompiled when indexes are recreated instead of being rebuilt.

How the Switch Begins

How the switch begins depends on the switch option selected for a live reorganization script:

• Automatic switch option—When this option is selected, the switch stage begins as soon as the copy table is ready for the switch.
• User-approval switch option—When this option is selected, the switch stage begins when approval is given.
• Time-window switch option—When this option is selected, the switch stage begins when the time window begins.

If QSA must wait for user approval or a time window, it continues collecting and posting live transactions, keeping the original table and copy table in sync. Once approval is given or a time window begins, the agent posts any live transactions that remain in the collection table and then makes the switch.

How the Switch Is Made

How the switch is made depends on the switch style being used:

• Online switch—The online switch is made by exchanging the original table and copy table. The copy table takes the place of the original. The original takes the place of the copy. Triggers, synonyms, and dependencies, which were left in place on the original, now apply to the copy.
• T-Lock switch—For a table, the T-Lock switch is made by renaming both the original and reorganized copy tables. The reorganized copy is given the name of the original table and takes its place as a result. For a partition or subpartition, the T-Lock switch is made by exchanging the original partition or subpartition with its copy table. The copy table takes the place of the original partition or subpartition in the parent-partitioned table. It thus becomes a partition or subpartition. The original partition or subpartition takes the place of the copy table. (If the original is kept, it is kept as a table.)

Table Availability During the Switch

Table availability during the switch depends on the switch style being used:

• Online switch—When the online switch style is used, the table being reorganized remains available throughout the switch stage.

• T-Lock switch—When the T-Lock switch style is used, original and copy tables are locked with trigger locks throughout the switch stage. Although write access is prevented, read-only access is available for most of this stage. Normally, the duration of the stage is less than a minute.

  Note Just before making the switch, QSA acquires an exclusive lock on the original table. This ensures that there are no open transactions against the table. The lock does not affect the online switch.

Stage 5 — Finish Reorganization

After the switch stage completes, the final tasks for a live reorganization are performed. These tasks always include dropping the temporary LiveReorg objects created by QSA. They can also include dropping the original table. This is dropped when the Drop original table option is selected.

Additional tasks may be performed depending on switch style and on how scripting options are set. Reorganization of the next table in a script does not begin until all tasks have been performed for the table ahead of it. If the original table is being dropped, this is the last task for a live reorganization.

Additional Tasks After an Online Switch

After an online switch for a table with a LOB column, additional tasks include dropping the table’s temporary materialized view log and AFTER INSERT trigger. These are dropped along with other LiveReorg objects. They are created by QSA for use in collecting direct UPDATEs to the LOB column. (When the T-Lock switch is used, the log and trigger are dropped right before the switch.)

Additional Tasks After a T-Lock Switch

After a T-Lock switch, additional tasks can include recompiling dependencies. Dependencies are recompiled after the switch stage when the While the table is protected by T-Lock option is not selected. (They are recompiled during the switch stage when this option is selected.)

Additional Tasks After Either Switch

After either type of switch, additional tasks can include validating data against constraints. Post-switch validation occurs when the Revalidate originally validated constraints option is selected and the constraints were ENABLE VALIDATE prior to reorganization.

Post-switch validation also occurs for PRIMARY KEYs that serve as the posting index. However, it does not occur when an entire table is reorganized with a T-Lock switch. In this case, data is validated against the PRIMARY KEY before the switch.

Note Reorganization of the next table in a script does not begin until validation for the last is complete. DML activity can continue against a table as constraints are validated.

Estimate the Time Needed for a Live Reorganization

This section provides information that can help you estimate the time required for a live reorganization. As a live reorganization is performed, you can monitor its progress from the Live Reorg Detail pane of the Scripts/Job Monitor. See Monitor Live Reorganizations for more information.

Time from Start of Reorganization to the Switch

The time required from the start of a live reorganization to the start of the switch depends on the size of the original table, the number and size of its indexes, and the level of activity against the table. It also depends on whether statistics are being collected. Recreating a large table and its indexes could require a minimum of several hours.

Time for the Switch

The time required for the switch is very brief. Normally, it takes less than a minute to replace an original table and its indexes with a reorganized copy table and its indexes. When the T-Lock switch is used, time is needed for additional tasks. These tasks are covered under “Stage 4—Make the switch”. The following list identifies factors that determine the amount of time required for additional tasks:

• Handling constraints and dependencies—The time required to recreate constraints and recompile dependencies is determined by how many must be processed. Time requirements can range from a matter of seconds to a matter of hours.
• Recreating global indexes—The time required to recreate global indexes is determined by their number and size.
• Dropping a ROWID index—If a ROWID index is used as the posting index, its size determines the time required to perform the tasks involved in dropping that index.

Time to Finish a Live Reorganization

After the switch stage completes, time is needed to drop the original table, if it is not being kept. Time is also needed to drop the LiveReorg objects created by QSA. Depending on how scripting options are set, more time may be needed for additional tasks. These tasks are covered under “Stage 5—Finish reorganization”.

The following list identifies factors that determine the amount of time required for final tasks:

• Recompiling dependencies—The time required to recompile dependencies is determined by how many must be processed. Time requirements can range from a matter of seconds to a matter of hours.
• Validating constraints—The time required to validate data against constraints depends on table size. Very large tables might need several hours or more. Reorganization of the next table in a script does not begin until validation for the last is complete. DML activity can continue against a table as constraints are validated.
• Dropping tables—Normally, only a matter of seconds is needed to drop an original table and LiveReorg objects. However, it might take minutes or hours if a table contains a large number of extents and is located in a data dictionary managed tablespace.

What to Do When Posting Falls Behind

Under rare circumstances, posting can fall behind or become “stuck” in a repetitive loop so that the switch cannot be made. The first problem can occur due to extremely heavy live activity. The second problem can occur due to system problems such as extremely high CPU usage.

In both cases, the main symptom is that posting sessions are longer than the “catchup” interval set for these sessions. The default is 60 seconds. When posting sessions are longer than the catchup interval, QSA treats this as a sign that there are more live transactions to post. As a result, it does not consider the copy table to be caught up with the original table and does not make the switch.

To determine whether posting has fallen behind or is stuck, check the Scripts/Job Monitor, the monitor’s Live Reorg Detail pane, and the script execution log:

• Scripts/Job Monitor—For both problems, “Posting live modifications” is displayed in the monitor’s Status Message column.
• Live Reorg Detail—For both problems, a value of greater than 60 seconds is displayed in the detail pane’s second Elapsed Time field for Stage 3. When posting is stuck, the value for Post Count continues to increase while the value for Current stays the same.

• Script log—For both problems, a series of session-time entries like the one in the following example are displayed in the script log:

  session time nnn > 60

To resolve either problem, increase the catchup interval for posting sessions. This is set in the CP_OVER_RIDE column of the QUEST_QSA_PDATA table. The value should be set in the row for the current reorganization. Check the REORG_LABEL column to find the name of the table being reorganized. Check the START_TIME column to identify the current reorganization. CP_OVER_RIDE displays 0 for the default value of 60 seconds. The new value should be greater than the last session time displayed in the script log.

Once the catchup interval is changed, it goes into effect for the next posting session. The change is only used for the table currently being reorganized. The default of 60 seconds is used for the next table in a live reorganization script.

Note Changes to the catchup interval are reported in the execution log for a live reorganization script. The entry for a change reads: “Posting time limit over-ride changed to nnn seconds”.

Posting Indexes

Posting Indexes

A posting index is used by QSA as it copies live transactions to a reorganized copy table. The main purpose of the index is to guarantee that rows are uniquely identified. Another purpose is to speed up the posting process by increasing the efficiency of searches on the copy table. Sometimes these functions are split between a posting index and QSA’s “all columns” posting method. This happens when a table does not have a selective index and an agent-created ROWID index is not used as the posting index.

Note: A selective index is one where every index entry points to a single location in table data. The PRIMARY KEY and unique indexes are selective indexes. A nonselective index is one where each index entry points to multiple locations in table data.

Comparison Chart

Related Topics

How Space Manager Chooses a Posting Index

Prompts to Validate Constraints

All Columns Posting Method

ROWID Posting Indexes

Set the Posting Parameter