Chatta subito con l'assistenza
Chat con il supporto

Space Manager with LiveReorg 9.1 - 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

Specify Options - Reorg Manager and Partitioning Wizard

Select Global Reorg Options

Use the first page of the Reorg Manager to specify target tablespaces and to specify the table switch style (Live Reorg only).

You can specify one target tablespace to be used for all tables, one to be used for all indexes, and one to be used for all LOBs. If necessary, you can override this setting for individual objects in the Custom Object Allocations page of the Reorg Manager.

Tip: You can save Reorg Manager settings as a Reorg Plan to edit and reuse later. See Save Reorg Manager Settings for more information. You can also use saved settings to automate reorganization script execution. See About Scheduling a Recurring Reorganization for more information.

Select table switch mode (LiveReorg only)

  1. If you are creating a LiveReorg script, on the first page of the Reorg Manager (see Launch Reorg Manager for more information) in the LiveReorg Table Switch Mode section, select the switch mode (style). Switch mode determines how the switch from the original table to the reorganized copy table is made.

    • To use the default setting, which is a combination of online and T-Lock switch, take no action.

      With the default setting, the switch mode is determined dynamically. The online switch is used for all tables supported for this switch style. The T-Lock (trigger lock) switch style is used for objects that are supported for LiveReorg, but not supported by the online switch. See What is Not Supported for Live Reorganizations for a list of objects not supported by the online switch.

      For more information about switch styles, see Switch Mode.

    • To use the online switch only, select Exclude tables requiring the T-Lock switch (online switch only).

      Select this mode to use the online switch for all tables supported for this switch style. Unsupported objects are excluded from a reorganization. When the online switch is used, the table being reorganized remains available during the switch.

      See What is Not Supported for Live Reorganizations for a list of objects not supported by the online switch.

Select target tablespaces

  1. On the first page of the Reorg Manager (see Launch Reorg Manager for more information), use the Target Tablespaces section to select whether or not to move objects to another tablespace. You can also use this section to specify a different tablespaces to use during reorganization (Standard Reorg only). For more information about target tablespaces, see About Selecting Target Tablespaces.

    Review the following for additional information:

    Tables

    Leave in Original Tablespace—Select to leave tables in their current tablespace.

    Reorganize In Place—(Standard Reorg only) Select this option to reorganize tables using space in their current tablespace.

    Use Interim Tablespace—(Standard Reorg only) Select this option to leave tables in their current tablespace but reorganize them using free space in another tablespace. Then select the other tablespace. Tables are copied to the current tablespace after they are reorganized.

    Relocate to Tablespace—Select to move tables to a different tablespace. Then select the tablespace from the list.

    Indexes

    Leave in Original Tablespace—Select to leave indexes in their current tablespace.

    Relocate to Tablespace—Select to move indexes to a different tablespace. Then select the tablespace from the list.

    LOBs and LOB Indexes

    Leave in Original Tablespace—Select to leave LOBs and LOB indexes in their current tablespace.

    Reorganize In Place—(Standard Reorg only) Select this option to reorganize LOBs using space in their current tablespace.

    Use Interim Tablespace—(Standard Reorg only) Select this option to leave LOBs in their current tablespace but reorganize them using free space in another tablespace. Then select the other tablespace. LOBs are copied to the current tablespace after they are reorganized.

    Relocate to Tablespace—Select to move LOBs and LOB indexes to a different tablespace. Then select the tablespace from the list.

    In the Relocate to Tablespace field, the list displays names and free space for all tablespaces in a database except offline and read-only tablespaces.

    Tip: Select the Use Interim Tablespace option to defragment a tablespace during a standard reorganization. A tablespace is defragmented when all of its objects are included in a reorganization.

Specify FastCopy or DataPump data movement method

  1. On the first page of the Reorg Manager, either the FastCopy (Oracle 11) or the DataPump (Oracle 12) section is displayed. This section allows you to specify FsCopy (Oracle 11) or DataPump (Oracle 12) as the only data movement method.
  2. FastCopy (Oracle 11)

    Use FsCopy—Use this option to specify whether or not to use FsCopy. Review the following:

    • (Recommended) Clear this check box to instruct Space Manager to automatically select the most efficient data movement method, either SQL or FastCopy, to use for each object to be reorganized.
    • Select this check box to use FsCopy for all tables that are supported for this method. Tables that are not supported are excluded from reorganization.

      Caution: Care should be taken when selecting this method. FsCopy and DataPump are not always the most efficient data movement methods and should be used only for large tables where table copy might cause Oracle errors.

    See About Data Movement Methods for more information about this option.

  3. DataPump (Oracle 12)
    • Use DataPump only for tables with LONG columns—(Recommended) Select this option to allow Space Manager to automatically select the most efficient data movement method, either SQL or DataPump, to use for each object to be reorganized.
    • Use DataPump for all tables—Select this option to use DataPump for all tables that are supported for this method. Tables that are not supported are excluded from reorganization.

      Caution: Care should be taken when selecting this method. FsCopy and DataPump are not always the most efficient data movement methods and should be used only for large tables where table copy might cause Oracle errors.

    • Max File Size—Use this field if you want to specify a maximum file size. When the file reaches the size specified, a new file is created. Leave this field blank to specify unlimited.
    • Parallel Degree—Specify the number of parallel processes to use if you want to request parallel processing.
    • Access Method—Select the method to use to unload data.
    • Compression—Specify which data to compress before writing to the dump file set.

    See About Data Movement Methods for more information about this option.

    If necessary, you can override this setting for individual objects in the Customize Object Allocations page of the Reorg Manager. See Customize Object Allocations.

  4. Click Next to proceed to the next page. For next steps, see Select Scripting Options.

Note: For the exported data, FastCopy and DataPump use the location/locations specified in the WORKDIR and OVERFLOW_DIR_nn parameters. See QSA Server Agent Parameters for more information.

After proceeding to the next page of the wizard

View any messages that display when you proceed to the next window. One message identifies objects that Reorg Manager is excluding. (Their names are displayed in gray.) Another message identifies tables with constraints that can be validated for use in posting live transactions. See Respond to Checks for a Posting Index for more information. Reorg Manager excludes the following:

  • Objects that are not supported for live reorganization.
  • Objects that have components or related objects that are not supported for live reorganizations.
  • Indexes that were selected for reorganization without their tables. Indexes can only be reorganized on a live basis when their tables are reorganized.
  • Objects that are not supported for the online-only switch style. To include these objects, clear the Exclude tables requiring the T-Lock switch (online switch only) option.

  

Select Scripting Options

You can use the Scripting Options page of the Reorg Manager and Partitioning Wizard to increase reorganization speed or to select the switch on approval option.

To select scripting options

  1. After specifying options on the first page of the Reorg Manager, click Next to open the Scripting Options page.
    • Or in the Partitioning Wizard, after reviewing the Partition Names page, click Next.
  2. In the Scripting Options page, specify parallel query, dependency, and switch options. Review the following for additional information:

    Tables

    Parallel Query Option—Use parallel query options to speed up reorganization with Oracle’s parallel processing. This type of parallel processing uses multiple server processes to reorganize each table in a script.

    Insert—This option lets you parallelize the INSERT statements used to insert data into copy tables. When CREATE TABLE AS SELECT is the SQL used, the Insert option parallelizes creation of the copy table and insertion of data.

    Select—This option lets you parallelize the SELECT statements used to select data from original tables.

    The maximum number of parallel processes specified should be no more than the number of CPUs in your system. The maximum number Oracle uses is determined by its PARALLEL_MAX_SERVERS initialization parameter.

    Unrecoverable—Select this option to speed up reorganization using Oracle’s NOLOGGING mode. This increases reorganization speed by turning off writing to redo logs as tables are recreated. Immediately after a NOLOGGING reorganization, you should back up reorganized tables.

    Use Alter Table Move where possible—(Standard Reorg only) Select to use the ALTER TABLE…MOVE statement with the SQL data-movement method during a standard reorganization. It moves table data into a new segment in one move. Constraints and dependencies do not need to be recreated or recompiled.

    • When a table is reorganized with ALTER TABLE…MOVE, its indexes can be recreated or rebuilt with the Rebuild where possible option.
    Indexes Use Parallel Query Option—Use parallel query options to speed up reorganization with Oracle’s parallel processing. See Parallel Processing for Indexes for more information.

    Create n indexes at the same time—Enter a number from two to 20 to use QSA parallel processing. See Parallel Processing for Indexes for more information.

    Unrecoverable—Select this option to speed up reorganization using Oracle’s NOLOGGING mode. This increases reorganization speed by turning off writing to redo logs as indexes are recreated. Immediately after a NOLOGGING reorganization, you should back up reorganized tables and their indexes.

    Rebuild where possible—(Standard Reorg only) Select to specify ALTER INDEX…REBUILD as the SQL to use for non-partitioned indexes. Rebuilding an index is faster than dropping and recreating it.

    • Rebuild ONLINE—(Standard Reorg only) Indexes can be rebuilt offline or online. When an index is rebuilt online, users can update its table throughout the rebuild process. Select this option if you want to allow DML access to associated tables.
    Compile Dependencies

    Select this option to compile dependencies on the tables being reorganized.

    • Compile n objects at the same time—Select this option to speed up the compile process with parallel processing by QSA.

    How much time is required to recompile dependencies depends on the number of dependencies, how busy the database is, and system performance. The time required can range from seconds to hours.

    Drop original table after successful reorganization

    (LiveReog and Partitioning Wizard only) This check box is selected by default so that original tables and indexes are dropped after they are replaced with their reorganized copies. Original objects are also purged from the Recycle Bin.

    Do not select this option if you want to save original tables and indexes. Original objects are preserved in their original tablespaces under modified names.

    See More About Scripting Options (LiveReorg) for more information.

    Switch Options

    (LiveReorg only) See More About Scripting Options (LiveReorg) for more information about switch options.

    Automatically—Select if you want the switch to be made automatically, as soon as a copy table is ready. When multiple tables are included in one reorganization script, the switch is made automatically for each table in turn.

    Upon user approval—Select if you want the switch to made after a user approves the switch. Approval should be given as soon as possible after a table is ready for the switch. Until approval is given, QSA keeps the original table in sync with the copy table.

    When multiple tables are included in a reorganization script, multiple approvals must be given.

    Approval is given from the Scripts/Job Monitor. See Approve the Switch for more information.

    Between date/time—Use this option if you want the switch to be made within a time window. Then specify a start date/time and end date/time for the window. The switch is made automatically for all tables that are ready for the switch during the time window specified.

    This option allows the switch to proceed automatically at a time when activity is light.

    Note: If the window ends before the switch is made for all tables in a script, execution aborts with this message: “QSA-20502: Target completion date/time expired.” This happens after 20 “retry operation” attempts are made.

    Tip: After a live reorganization script has been stored and scheduled, you can select a different switch option, if needed. See Change Approval Method for Online Scripts for more information.

    Analysis

    Collect statistics for the CBO—Select to update Oracle's cost-based optimizer (CBO).

    Do not collect statistics for the CBO—Select if you do not want to update Oracle's cost-based optimizer (CBO).

    Restore original statistics in data dictionary for the CBO— (Reorg Manager only) Select this option to preserve existing statistics in the Oracle data dictionary instead of collecting statistics.

    Note: Statistics are collected using DBMS_STATS. To collect statistics or restore the data dictionary, the DBMS_STATS package must be installed for the database.

    See About Statistics Collection Options for more information about these options in the Reorg Manager.

    Segments being reorganized Provides information about the objects to be reorganized.

    Note: Scripting options for tables are not used for FastCopy (or DBMS_DataPump).

    Important: Parallel and NOLOGGING scripting options do not change the permanent parallel and logging attributes for an index. These options are only used during reorganization.

  3. Click Next to proceed to the next page. For next steps, see Customize Object Allocations.

Parallel Processing for Indexes

There are two types of parallel processing you can use for indexes, and they can be used together. Each works in a different way:

  • Oracle parallel processing uses multiple server processes to reorganize one index at a time. All are applied to the same index. If degree of parallelism for a reorg is four, all four parallel processes are applied to the first index being reorganized. All four are then applied to the next index being reorganized, and so on. To use this option, select Use Parallel Query Option.
  • QSA Server Agent parallel processing uses multiple processes to reorganize multiple indexes at the same time. One QSA process is used per index. If three QSA processes are being used, one process is applied to the first index in a series of indexes, one is applied to the second index in the series, and one is applied to the third index in the series. To use this option, select Create n indexes at the same time.

What happens when the two types of parallel processing are used together? Multiple QSA processes are used to reorganize multiple indexes simultaneously while multiple Oracle parallel query processes are applied to each index. Keep in mind that using the two together increases the number of CPUs needed for a reorganization.

To calculate the number of CPUs needed for parallel processing of indexes, multiply the number of QSA processes specified by the number of parallel server processes that will be used by Oracle (this is twice the number you specify plus one). For example, if you specified 3 QSA processes and 7 Oracle processes will be used by Oracle, 21 CPUs will be needed for reorganizing indexes.

  

Customize Object Allocations

Use the Customize Object Allocations page of the Reorg Manager or Partitioning Wizard to specify options for individual objects.

In the Physical Attributes sections, current values are displayed in gray beside entry fields for new values.

Note: To see the space currently allocated for an object, as well as the post-reorganization estimate, check the Segment Allocation section.

Names are displayed in gray for objects on the Objects to Reorganize list that are not included in a reorganization. Typically, these objects are related to included objects.

Note: The Partitioning Wizard does not display fields for an object if its partitioning is not changed. Instead, it displays a message: “This functionality does not apply to the currently selected object.”

To customize object allocations

  1. After specifying options in the Scripting Options page, click Next to open the Customize Object Allocations page.
  2. In the Customize Object Allocations page, specify storage and space use options for individual objects as needed. You can also override target tablespace settings for an object.

    Select an object in the Objects to Reorganize list. Then customize its settings. Review the following for additional information:

    Object Properties  

    Space Saving tab

    Use these fields to modify physical attributes and compression methods.

    For Tables

    Compress—Select to use compression. Then select a compression method.

    Row Store Compress—(Oracle 12c/19c)

    • Basic—Select to use ROW STORE COMPRESS BASIC to enable basic table compression.
    • Advanced—Select to use ROW STORE COMPRESS ADVANCED to enable Advanced Row Compression.

    Column Store Compress—(Oracle 12c/19c) Enabled only if underlying storage supports Hybrid Columnar Compression (such as on Oracle Exadata).

    • Query—Select to use COLUMN STORE COMPRESS FOR QUERY to enable query compression.
    • Archive—Select to use COLUMN STORE COMPRESS FOR ARCHIVE to enable archive compression.

    For Indexes

    Compress—Select to use compression. Then select a prefix length.

    • Advanced Low—(Oracle 12c/19c) Select to use Advanced Index Compression. Available only if supported in the underlying Oracle database.

     

    Note: Pct Free reserves a percentage of data block space for updates to existing rows. For example, if the PctFree setting is 10, 10% of each of an object’s data block is reserved for UPDATES. The space left (minus block overhead) is the space available for INSERTS of new rows and their overhead. Set a higher value for PctFree if existing rows will be updated heavily and updated values will increase row size. This will help prevent chaining of growing rows to other data blocks.

    Detail tab

    Use these fields to modify logging, parallelism, indexing, and other object properties.

    Indexing—(Oracle 12c/19c)

    • For Tables. Use this field to specify which partitions are indexed by turning INDEXING ON or OFF. The table-level setting can be overwritten by each partition-level setting.
    • For Indexes. Use this field to indicate whether the selected index is PARTIAL or FULL.

    In-Memory

    Use these fields to enable or modify In-Memory Column Store options.

    In-Memory—Select this option to enable In-Memory Column Store for the selected object.

    • MemCompres—Select to use in-memory compression. Then select the level of compression to use.
    • Priority—Select a priority to use for this table when populating tables in the IM column store.
    • Distribute—Specify how the table is distributed among Oracle RAC instances.
    • Duplicate / All—Select to duplicate data on another or on all Oracle RAC instances.

    Column Clause—Select to specify In-Memory Column Store options per column.

    Note: In a Live Reorg, the column clause is only supported if the T-Lock switch is used.

    Attribute Clustering

    Use these fields to apply or modify attribute clustering.

    Attribute Clustering—Select this option to apply attribute clustering to the reorganized table.

    • Specify the type of attribute clustering to apply: Linear Order or Interleaved Order.
    • Select the operations for which clustering is triggered: on Load and/or on Data Movement.

    Columns—Click to specify one or more columns on which to base clustering.

    • Add Group—Click to specify a column group if necessary. Enabled for Interleaved Order clustering.

    Joins—Click to specify one or more joined columns on which to base clustering (join attribute clustering).

    LOB tab

    This tab only displays if you have LOB columns.

    SecureFile—Select this option to store LOBs using the SecureFile storage method and to enable additional SecureFile options from which to select.

    Note: Oracle only stores data in the row if the data is less than 4K. If the data is greater than 4K, then the LOB value is moved into a LOB segment.

    Deprecated tab

    Use this tab to modify physical attributes and storage options applicable to features used in older versions of Oracle database, such as for Dictionary-Managed tablespaces.

    If you change values for Initial, Next, and PctIncrease, this overrides global values.

    Note: Oracle ignores the PCTUSED, FREELISTS, and FREELIST GROUPS parameters for objects in locally managed tablespaces that use automatic segment space management (ASSM).

    Encryption tab

    This tab only displays if you have encrypted objects. It is recommended that you provide the encryption password to ensure that the reorganization is successful.

    Note: If you use the exchange partition or online switch to reorganize a table that uses Transparent Data Encryption (TDE), you must supply the encryption password. If you did not provide a password when you created the table, you cannot reorganize the table with methods.

    Long LONG Columns Indicates whether or not the object includes one or more long LONG columns.
    DataPump

    (Oracle 12c/19c) Use this section to override the global DataPump settings if necessary. You can specify DataPump options for individual objects.

    Use DataPump—Select this option to use the DataPump method for the selected object. You can then modify the Max File Size and Parallel Degree options if necessary.

    The Use DataPump check box is not editable for tables with LONG columns, regardless of the global setting.

    See Select Global Reorganization Options for more information.

    Tablespace Use this section to override the global tablespace specification if necessary. See Select Global Reorganization Options for more information.
    Sorted Reorg Perform sorted reorg—Select this option if you want to sort a table’s data by a certain index. Then select the index to sort by from the Index to Use field.
    Add Index

    (Reorg Manager) To add an index, select a table and click Add Index. Once you add an index, you can modify or drop it from the Customize Object Allocations page.

    Note: If you want to create a partitioned index, it must be locally partitioned. You cannot create a globally partitioned index.

    To modify or drop an index, select the index and click Modify Index or Drop Index.

    Segment Allocation

    Use this section to view current allocation, as well as post-reorganization allocation estimates.

  3. Click Next to proceed to the next page.

  

Related Documents

The document was helpful.

Seleziona valutazione

I easily found the information I needed.

Seleziona valutazione