Chat now with support
Chat mit Support

Space Manager with LiveReorg 9.0 - User Guide

Smart Start for Space Manager Collect Statistics Profile DML Activity Manage Storage Reorganize Objects with Reorg Manager Partition Tables with the Partitioning Wizard Run and Monitor Scripts Run Reports Options Troubleshooting Administration Functions Appendix About Us

LiveReorg Objects and Their Tablespace

QSA creates several objects for temporary use during a live reorganization. These include the collection table used for storing live transactions and the table used for storing posting statistics. One set of objects is created for each table in a live reorganization script. A table’s object ID is appended to the names of most LiveReorg objects.

Temporary LiveReorg objects are used in addition to QSA’s permanent objects. The names of temporary objects begin with QUEST_QSA_LW%. The names of permanent objects begin with QUEST_QSA_% or QUEST_EXEC_%.

All temporary LiveReorg objects are created in the LiveReorg tablespace. This is specified with QSA’s LW_TABLESPACE parameter. (If a ROWID index is used as the posting index, it is also created in this tablespace.) QSA’s permanent objects are stored in another tablespace. This is specified during first-time installation of the agent.

LiveReorg objects are created at the beginning of a live reorganization. Most are dropped from the LiveReorg tablespace after the switch from original table to reorganized copy table. If the T-Lock switch is used for a table with a LOB, the materialized view log and trigger created for that table are dropped right before the switch.

Important In most cases, you should not drop or change objects used by QSA. If a live reorganization script fails, leave LiveReorg objects in place while you resolve the problem that caused the failure. The tables will be used when the script is restarted.

 

Related Topics

A Look at LiveReorg Objects

A Look at the LiveReorg Tablespace

Space for LiveReorg Objects

A Look at LiveReorg Objects

This section provides a list of the temporary LiveReorg objects that are created by QSA. It also identifies two permanent tables that are used by the agent during live reorganizations:

  • QUEST_QSA_LWDATA<obj#>—This table is used for storing information on live transactions. The last part of its name is the object ID of the table being reorganized. (This is a temporary LiveReorg object.)
  • QUEST_QSA_LWLONG<obj#>—This table is used for storing information on posted live transactions that require changes to a LONG column. The last part of its name is the object ID of the table being reorganized. The LWLONG table is only created for tables with a LONG column. (This is a temporary LiveReorg object.)
  • QUEST_QSA_LWIX<obj#>—This index is used for the collection table, QUEST_QSA_LWDATA. It is created on the transaction number column, Q_TN. The last part of the index name is the object ID of the table being reorganized. (This is a temporary LiveReorg object.)
  • QUEST_QSA_LWSES<obj#>—This table is used for storing statistics for each posting session that occurs during a live reorganization. The last part of the table name is the object ID of the table being reorganized. (This is a temporary LiveReorg object.)
  • QUEST_QSA_LWRT<obj#>—This AFTER trigger is used to collect most information on most live transactions. The last part of the trigger name is the object ID of the table being reorganized.
  • QUEST_QSA_LWRMT<obj#>—This AFTER INSERT trigger is used to collect information on direct UPDATEs to LOB columns. It is used in conjunction with the materialized view log, QUEST_QSA_LWRMT<obj#>. The last part of the trigger name is the object ID of the table being reorganized.
  • MLOG$_TABLENAME—This materialized view log is used in conjunction with the QUEST_QSA_LWRMT<obj#> trigger to detect and collect direct UPDATES to LOB columns. The last part of the log name is the name of the table being reorganized.
  • QUEST_QSA_MVPKG—This package is used for inter-process messaging during live reorganizations of tables with LOBs.
  • QUEST_QSA_PDATA—This table contains persistent information for each live reorganization performed in a database. If a table undergoes multiple live reorganizations, QUEST_QSA_PDATA contains a record for each one. A record is created as soon as reorganization begins. It is preserved whether or not reorganization completes. Information in this table includes key statistics such as posting rates for INSERTs and DELETEs. (This is a permanent QSA object.)
  • QUEST_QSA_ERR—This table is used for recording errors that occur during execution of collection triggers. (This is a permanent QSA object.)

    Note The “LW” displayed in the names of LiveReorg objects stands for “lightweight”. The collection and posting processes associated with these objects are lightweight. All are designed to have low overhead and minimal impact.

 

Related Topics

A Look at the LiveReorg Tablespace

Space for LiveReorg Objects

A Look at the LiveReorg Tablespace

The tablespace used for LiveReorg objects is specified with QSA’s LW_TABLESPACE parameter. The LiveReorg tablespace must be specified when you install the agent. It can be changed at any time from the QSA Installer (through the Space Manager client) or from the QUEST_EXEC_PARAMETER table. See QSA Server Agent Parameters for more information.

If you try to exit from the QSA Installer without specifying a LiveReorg tablespace, a “Required parameter is not set” message displays. If you delete the parameter or its value from the QUEST_EXEC_PARAMETER table, live reorganizations fail with this error: “QSA-20391: ORA-NNNNN during internal SQL”.

It is recommended that you create or reserve a tablespace for exclusive use by LiveReorg objects. Dedicating a tablespace to LiveReorg objects ensures that the space they need will not be used by other objects. If you create a LiveReorg tablespace before installing the agent, that tablespace will be available for selection during the installation process.

Note LiveReorg tablespace must have a standard name, one that does not require double quotes.

To learn how to modify the LW_TABLESPACE parameter, see Specify Parameters for the QSA Server Agent.

 

Related Topics

A Look at LiveReorg Objects

Space for LiveReorg Objects

Space for LiveReorg Objects

The tablespace used for LiveReorg objects must have sufficient space for creating these objects. It must also have sufficient space for the objects to grow during a live reorganization.

At a minimum, the LiveReorg tablespace should have a datafile that is at least 50 megabytes in size. The datafile should contain at least 20 megabytes of contiguous free space.

How much additional space is needed as a live reorganization proceeds depends on row length for the collection table, total size of the columns in the posting index, and number of live transactions made against the original table during creation of the copy table. If additional space is needed by LiveReorg objects, it is allocated in five-megabyte extents. 

Tip To see how much free space is available in the LiveReorg tablespace, select it in Space Manager’s Explorer. Then check Freespace fields at the top of the Explorer.

Space for Materialized View Logs

If the table being reorganized contains a LOB column, the LiveReorg tablespace needs additional space for the materialized view log that is created for the table. The log doubles the amount of free space required.

The materialized view log is used in conjunction with an AFTER INSERT trigger to collect direct updates to the LOB. As live transactions are made against the original table, rows for LOB updates are committed and then inserted into the log.

To keep the log from running out of space as rows are inserted, QSA periodically deletes all rows from the log. It does this using a process that runs every five seconds. If a row cannot be inserted in the log, Oracle returns the following error to your application: “ORA-12096: Error in materialized view log on “string”.”string”. See What to Do If a Materialized View Log Error Occurs for more information.

Space Checks for the LiveReorg Tablespace

Prior to script generation, Space Manager checks to see whether the LiveReorg tablespace has enough space for creating LiveReorg objects. If a table has a LOB column, Space Manager also checks to see whether the tablespace has enough space for creating a materialized view log on the table. A space check of the LiveReorg tablespace is made along with space checks of target tablespaces. See Review Space Usage for more information.

If the LiveReorg tablespace does not have sufficient space for creating LiveReorg objects, warnings are displayed in Reorg Manager’s Review Space Usage window or the Partitioning Wizard’s Space Usage Summary tab. In addition, a message may be raised. To provide the free space needed, you can use Tablespace Properties to add datafiles or increase datafile size. You can leave Reorg Manager or the Partitioning Wizard open while you do this.

If you do not provide the space needed for creating LiveReorg objects, a live reorganization will fail with this error: “QSA-20394: Could not create temporary QSA object in <LW_TABLESPACE>”. If the LiveReorg tablespace does not have space for a materialized view log, your application will receive the ORA-12096 error. However, reorganization will not fail.

Note Space checks consider the space needed for creating LiveReorg objects. They do not consider additional space that might be needed for object growth. If a live reorganization fails because a LiveReorg object cannot grow, a collection trigger error is displayed in the script log.

Quota Checks for the LiveReorg Tablespace

During script generation, Space Manager checks to see whether the owners of tables with LOBs have quota for the LiveReorg tablespace. This is needed for creating the materialized view log used in collecting direct UPDATEs to LOBs.

If an owner does not have quota, Space Manager raises a warning right after generating a script. You can avoid the warning and automatically grant quota by leaving the Script Quota changes scripting option selected (it is selected by default). You can ensure that a script will not fail due to insufficient quota by selecting the Grant unlimited quota scripting option. Quota that did not exist prior to reorganization is revoked afterward.

Calculate Space for LiveReorg Objects

To estimate how much space will be needed to create and grow LiveReorg objects during a live reorganization, use the following steps:

  1. Calculate “total live activity” to be made against the original table. The formula to use is: Total INSERTs + DELETEs + (UPDATES x 2).
  2. Calculate “collection table size” for QUEST_QSA_LWDATA. The formula to use is: Row length of 55 bytes + total size of all columns in the posting index.
  3. Calculate “collection table space usage”. The formula to use is: “Total live activity” x “collection table size”.
  4. If the table to be reorganized contains a LONG column, calculate “LONG collection table size” for QUEST_QSA_LWLONG. The formula to use is: Total INSERTs + UPDATEs x row length of 45 bytes.
  5. Calculate “total space usage”. The formula to use is: “Collection table space usage” + “LONG collection table size” rounded up to the nearest 5 MB + minimum requirement of 5 MB. (Additional space is allocated in five-megabyte extents.)
  6. If the table being reorganized contains a LOB column, double the calculation made in Step 5.

Storage Values for LiveReorg Objects and Their Tablespace

Some LiveReorg objects are sized with storage values specified by QSA. These objects are the collection table and the table that stores changes for LONG columns.

Other LiveReorg objects are sized with default storage values for the LiveReorg tablespace. These objects are the statistics table, the index on the collection table, and the materialized view log that is created for tables with LOB columns.

Default storage values for the LiveReorg tablespace should be set as follows:

  • Storage for locally managed tablespace with UNIFORM allocation type:
    INITIAL 1 MB
    NEXT 1 MB
    MAXEXTENTS UNLIMITED
    PCTINCREASE 0
  • Storage for dictionary managed tablespace:
    INITIAL 1 MB
    NEXT 1 MB
    MINIMUM EXTENT size 1 MB
    MAXEXTENTS UNLIMITED
    PCTINCREASE 0

 

Related Topics

A Look at LiveReorg Objects

A Look at the LiveReorg Tablespace

Verwandte Dokumente

The document was helpful.

Bewertung auswählen

I easily found the information I needed.

Bewertung auswählen