About Statistics Collection Options
In the Reorg Manager, statistics collection options are specified on the Scripting Options page (Analysis section). These options allow you to automatically collect or restore statistics when a reorganization script is run.
See Collect Statistics for an overview of statistics collection in Space Manager.
The Reorg Manager provides the following options for statistics collection:
- Collect statistics for the CBO (cost-based optimizer)—This option lets you collect post-reorganization statistics.
-
Restore original statistics in data dictionary for the CBO—This option lets you preserve existing statistics in the Oracle data dictionary instead of collecting statistics. It is designed for use when data-dictionary statistics are pre-set for Oracle’s cost-based optimizer.
Note: Restore functionality requires the DBMS_STATS package.
For information on how to specify statistics collection options in the Reorg Manager, see Select Scripting Options.
How the Restore Option Works During Collection
When you restore statistics during statistics collection, data dictionary statistics are copied before objects are reorganized. The statistics are restored to the data dictionary afterward. The backup and restore are performed using procedures from the DBMS_STATS package. Before a reorganization begins, the export_table_stats procedure is used to export existing statistics from the data dictionary to a backup table. This is called QUEST_SPC_REORGANIZATION_STATTAB. After statistics are collected, statistics in the backup table are imported into the data dictionary with import_table_stats. They are then deleted from the backup table with delete_table_stats.
Important: The DBMS_STATS procedures used by the restore option processes statistics needed by Oracle’s cost-based optimizer. They do not process statistics such as number of chained rows, average free space in data blocks, number of empty blocks, and average space in freelist blocks. As a result, these statistics are not preserved by the restore option. If they exist in the data dictionary before collection is performed with this option, they will not exist there afterward.
Review Space Usage
After you specify storage options for individual objects in the Customize Object Allocations page, the Reorg Manager performs a space audit and displays the results in the Space Usage Summary tab. Reorg Manager checks for the following:
- Free space in target tablespaces—Reorg Manager checks target tablespaces to see if they have enough free space for original and copy objects.
- Data block size—Reorg Manager checks to see if tablespaces for partitions and subpartitions from the same object use the same data block size. If these tablespaces use different sizes, a warning is raised. Make note of the objects listed in the warning. Then return to the Customize Object Allocations page and select tablespaces that use the same block sizes for these objects.
-
LiveReorg tablespace—If you are performing a live reorganization, Reorg Manager checks to see if the LiveReorg tablespace has enough space for LiveReorg objects. The LiveReorg tablespace and LiveReorg objects are used by QSA. The LiveReorg tablespace is specified with the agent’s LW_TABLESPACE parameter. Space checks by Reorg Manager 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.
Note: Another space audit is performed when a script is run to make sure sufficient space is still available.
Space Usage Summary Tab
The Space Usage Summary tab displays information for segments and tablespaces involved in a reorganization. This includes original tablespaces, tablespaces being used on an interim basis, tablespaces to which objects are being relocated, and the LiveReorg tablespace.
For each segment, the information displayed includes current and ending space allocation values.
For each tablespace, the information displayed includes starting free space and ending free space.
Space Usage Messages
Space Manager may alert you to space availability with various messages. If a message indicates that a target tablespace or the LiveReorg tablespace does not have enough free space, open Tablespace Properties for the tablespace and add datafiles or increase datafile size. Leave Reorg Manager open while you do this. You can also return to previous windows and select different target tablespaces or adjust storage values for the objects being reorganized.
If Space Manager displays a message after estimating space usage (in the Reorg Manager or other areas of the application), review the message description and suggested guidelines below.
|
Message |
Description |
|---|---|
|
Insufficient Free Space |
When one or more target tablespaces do not have enough free space, the “insufficient free space” message is displayed at the bottom of the Review Space Usage window. Values for target tablespaces without enough free space are displayed in red. A negative value is displayed in the Lowest Usable Freespace column. |
|
Insufficient Contiguous Free Space |
When one or more target tablespaces do not have enough contiguous free space for more objects, a message with a list of these tablespaces and objects displays when you proceed to the Review Space Usage window.
 Click here to see an example.
To provide the contiguous space needed in a target tablespace or the LiveReorg tablespace, use one of the methods under “Space Usage Messages”. If your actions do not result in sufficient contiguous free space, one of the following messages is displayed in the Review Space Usage window:
|
|
Current Allocation Notes |
The Review Space Usage window sometimes displays a note indicating that some objects were sized based on current allocation instead of current usage. Current allocation and current usage are two sizing options for the Minimize Number of Extents global allocation strategy. You can select either one. However, the current usage option requires current statistics in the Space Manager repository. If statistics are not current for an object, current allocation is used instead of current usage.
Click here to see an example.
The note gives one of the following reasons for use of current allocation:
|
|
Underestimates and Overestimates |
Space checks can sometimes underestimate or overestimate the space required by an object. This happens when statistics in the Space Manager repository are not current. The results of incorrect estimates are as follows:
Even when space checks are accurate, “Freespace not available” errors can occur during script execution. This happens when extents are allocated in a different order than anticipated. For example, a space check might anticipate that larger extents will be allocated to larger objects first, but Oracle might allocate these extents to smaller objects first. As a result, they are not available for larger objects. |
Respond to Checks for a Posting Index
The wizard checks for indexes that can be used as posting indexes. A posting index is needed when live transactions are copied to a reorganized copy table. The index guarantees uniqueness of rows in the copy and speeds up the posting process. Each table in a script has its own posting index.
In most cases, Space Manager automatically chooses a posting index. Normally, the most selective index available for a table is selected. However, in some cases, Space Manager prompts you to make choices regarding the posting index. This happens when the only candidate for a table’s posting index has a PRIMARY KEY constraint or UNIQUE KEY constraint in the ENABLE NOVALIDATE state.
The message prompts you to validate constraints so that they can be used for posting. If you do not want to do this, you can exclude a table from a reorganization or select one of the two posting methods that are designed for tables that do not have selective indexes. These are the “all columns” posting method and the ROWID posting index. See Posting Indexes for more information.
Important: If you choose to validate a UNIQUE KEY constraint, posting may be slow if the leading column of the constraint is nullable.
To respond to checks for a posting index
-
From the Unsupported Objects message with two options, select one option:
- Exclude these objects from the reorganization—Select this option to exclude tables whose only candidates for a posting index have PRIMARY KEY or UNIQUE KEY constraints that are ENABLE NOVALIDATE.
- Generate a script to validate these constraints—Select this option to generate a script for validating constraints. The script is displayed in the SQL Editor. Whether you should run the script depends on the following:
- If you want to validate PRIMARY KEY or UNIQUE KEY constraints for use in posting, run the script by pressing F9 from the SQL Editor. Then close the SQL Editor and skip the rest of this procedure.
- If you want to use the “all columns” posting method or ROWID posting index, close the SQL Editor. Then proceed to the next window in Reorg Manager. A second Unsupported Objects message displays with additional options.
-
From the Unsupported Objects message with four options, select one option:
- Exclude these objects from the reorganization—Select this option to exclude tables whose only candidates for a posting index have PRIMARY KEY or UNIQUE KEY constraints that are ENABLE NOVALIDATE.
- Generate a script to validate these constraints— Select this option to generate a script for validating constraints. The script is displayed in the SQL Editor. Run the script by pressing F9. Then close the SQL Editor.
- Use all columns of these tables for posting—Select this option to use “all columns” posting for tables in the message. This method checks all columns in a table to guarantee row uniqueness in its reorganized copy. However, it does not check columns with LONG, LOB, XMLType, object type, REF, and VARRAY datatypes. By default, tables with those datatypes are excluded from a reorganization when this option is selected. It you want to include the tables and are sure that rows can be identified uniquely based on columns with other datatypes, set Space Manager’s ALLOW_LONG_WO_UNIQUE parameter to YES. See All Columns Posting Method for more information.
- Use a temporary rowid column on the new tables—Select this option to use ROWID indexes for tables in the message. These indexes check the ROWIDs of all rows in a table to guarantee row uniqueness in its reorganized copy. Unlike the “all columns” method, ROWID indexes guarantee row uniqueness for tables with LONG, LOB, XMLType, object type, REF, and VARRAY datatypes. However, they require DDL during script execution and do not support the online switch or FastCopy/DBMS_DataPump. Also, ROWID indexes are only used for partitions or subpartitions when a Space Manager parameter is set. If that parameter is not set, you are prompted to reorganize a partition’s parent table. See ROWID Posting Indexes for more information.