23
Introduction to the Reorg Wizard

The Reorg Wizard can help you maintain a well performing database by correcting space usage problems. Over time, database performance can be affected by problems such as row chaining and/or migration and index stagnation. The Reorg Wizard can eliminate space problems by reorganizing database space usage. The Reorg Wizard also allows you to change an object's storage settings and location.

The Reorg Wizard gives you three reorganization options:

• Reorganize specific schema objects
• Reorganize an entire tablespace
• Repair migrated rows

The wizard performs reorganizations within the Oracle database and does not use external data handling operations. Reorganizations can be performed within the target tablespace or by temporarily using space in another tablespace.

The Reorg Wizard evaluates the reorganization operations and generates an Impact Report and reorganization script, which can be reviewed prior to execution. Reorganizations can be performed immediately or can be scheduled at a later time, perhaps during off-peak hours. In either case, the reorganization will be run by an Enterprise Manager job using the Oracle Agent.

Certain database reorganization operations can be time-consuming and will impact the availability of objects being reorganized. Use database object reorganization selectively. For example, tablespace reorganizations can often be avoided by reorganizing only selected tables and indexes.

When to use the Reorg Wizard

Over time, your database's space usage can become fragmented and inefficient. The Reorg Wizard can be used for efficient space management.

Specifically, you may want to use the Reorg Wizard when one or more of the following situations occur:

• Excessive row migration--If an UPDATE statement increases the amount of data in a row so that the row no longer fits in its data blocks, the Oracle Server tries to find another block with enough free space to hold the entire row. If such a block is available, the Oracle Server migrates the data for the entire row to the new data. A forwarding address is left at the original row's block. This is called migrating a row; the rowid of a migrated row does not change.

  The effect of row fragmentation on performance is that each time a migrated row is needed for a query, an additional I/O call is required for the row piece that is not stored in the original block.

  It is possible to minimize unnecessary row fragmentation for a table by correctly setting the table's PCTFREE value. Being too pessimistic on PCTFREE can waste some space but will more or less guarantee no row migration while being too optimistic can result in row migration and reduced performance. Instead of trying to save every byte in a block, Oracle recommends erring on the side of setting PCTFREE high rather than low. In most cases this will be a difference of only 5%. For tables that do not have varying width columns and/or do not get updated, this parameter can be safely ignored or set to a low value.

  Reorganizing the table or table partition will fix migrated rows. In some cases, however, you may want to focus on correcting problematic rows only. The Reorg Wizard allows you to reorganize an entire tablespace or choose selected rows to repair. In either case, when reorganizing an object which suffers from excessive row migration, you may wish to increase the current PCTFREE setting in order to reduce the potential for rows which would have to be migrated in the future.

• Disorganized indexes--As indexed values are inserted or deleted, the amount of unusable space within the index increases. Because the index contains a mixture of data and empty areas, scans of the index are less efficient. Rebuilding the index will eliminate unused index space.
• Inefficient extent sizes-- In order to avoid unnecessary I/O for objects that are fully scanned, a segment's extent size should be a multiple of the multi-block I/O size which is defined as DB_FILE_MULTIBLOCK_READ_COUNT * DB_BLOCK_SIZE. If unnecessary I/O is being performed, consider rebuilding the object specifying an INITIAL and NEXT value that is a multiple of the multi-block I/O size.
• Overextended object--The MAXEXTENTS storage parameter specifies the maximum number of extents that can be allocated to the segment. Once a segment has filled the maximum number of extents, any row insertion will fail. If an object is approaching its maximum extents setting, consider increasing the value of the segment's MAXEXTENTS storage parameter, or rebuilding the segment with a larger extent size. Ensure that the extents within the segment are the same size by specifying storage parameters where the INITIAL and NEXT values are the same and PCTINCREASE=0.

Any of these factors may impact the overall performance of the database.

Accessing the Reorg Wizard

You can start the Reorg Wizard in context of an object that you want to reorganize or in standalone mode. You can start the Reorg wizard in context to an object by selecting an entire tablespace or by selecting a specific database object. You can also start the Reorg Wizard in standalone mode from the Oracle Enterprise Manager console Tools=>Tuning Pack menu or from the Oracle Enterprise Manager console's Tuning Pack toolbar.

Reorganizing an Entire Tablespace

• From the Oracle Enterprise Manager console:
  1. Open the target database by double-clicking on the database object in the console navigator tree.
  2. Open the Tablespaces container and select the target tablespace.
  3. Right-click on the tablespace and select the Reorganize... menu item.

Reorganizing a Specific Schema Object

• From the Oracle Enterprise Manager console:
  1. Open the target database by double-clicking on the database object in the console navigator tree.
  2. Open the Schema Objects container and select one of the following schema objects: table, cluster, index, table partition, or index partition.
  3. Right-click on the the selected object and select the Reorganize... menu item.

Reorganization Process

The Reorg Wizard leads you through the process of reorganizing objects within a database. As part of the process you will be able to select the objects targeted for reorganization, optionally change the storage setting and location of these selected objects, and then perform the reorganization immediately or schedule it to be performed at later time.

If you need assistance while using the wizard, select the Help button from any of the pages to detailed information.

The Reorg Wizard guides you through the following steps:

• Selecting a Reorganization Option

  The reorganization option allows you to chose the type of reorganization to perform. There are three reorganization options: reorganize specific schema objects, reorganize an entire tablespace, or repair migrated rows. (Note that this step will only appear when you launch the Reorg Wizard without first selecting an object to reorganize.)

• Selecting Objects

  A list of available objects is displayed. The wizard allows you to select the objects you wish to reorganize. (Note that this step will only appear when you launch the Reorg Wizard without first selecting an object to reorganize.)

• Setting object attributes

  When reorganizing schema objects, it is possible to change the object location and sizing information for the individual selected objects not only to correct existing space usage problems, but also to prevent future problems.

• Choosing the appropriate reorganization method

  One way to enhance availability and manageability is to allow users full access to the database during a data reorganization operation. Depending on the server version, Oracle supports online reorganizations for various types of objects. For example, starting with Oracle 8i, B*-tree indexes can be created and rebuilt online. Oracle 9i improves on this with new features that support online creation and rebuilding of even more types of indexes as well as support for online reorganization for some types of tables.

  Even though online reorganizations provide higher availability, they are slower. If you have a maintenance window that allows for a scheduled "after hours" reorganization, then a faster, offline reorganization would be better.

  The Reorg Wizard allows you to indicate whether the generated script should favor availability or speed. If availability is chosen, the generated script will take full advantage of online capabilities when supported by the server. If speed is chosen, the generated script will always use an offline reorganization approach.

  Note: To take advantage of the Oracle 9i online table reorganization, you must have the following privileges: SELECT ANY TABLE, ALTER ANY TABLE, LOCK ANY TABLE, DROP ANY TABLE, CREATE ANY TABLE and the ability to execute the dbms_redefinition package either by explicitly granting EXECUTE privilege for this package or by granting the EXECUTE_CATALOG_ROLE. For more information on granting user privileges, see the Oracle Enterprise Manager Administrator's Guide.

  
  
  
• Identifying sufficient temporary free space

  Generally reorganizations are performed by moving data to temporary objects that the wizard creates. When reorganizing specific schema objects, you can choose to have these temporary objects created in their current tablespace or you can use an alternate scratch tablespace. Using the current tablespace is a faster method because the objects are only moved once. However, you may want to create and use a scratch tablespace to avoid the space impact of reorganizing within the current tablespace.

  When reorganizing an entire tablespace, the use of a scratch tablespace is required.

  In addition to the current tablespace, the wizard displays a list of permanent, online, non-SYSTEM tablespaces for defining where temporary space should be acquired.

• Viewing the impact report and job summary

  Using the selected objects and any changes to the individual objects, the wizard generates an impact report and reorganization script. The wizard allows you to review the Impact Report and a Job Summary prior to the execution of the reorganization.

  The Impact Report provides a list of errors or other problems discovered for the reorganization job; for example, the report would include resource warnings should there be insufficient space for the reorganization. This allows you to make necessary changes to help ensure the reorganization job will run successfully.

  The Job Summary contains a summary of the database commands that will be used to perform the reorganization.

• Scheduling the reorganization job

  The wizard allows you to specify when you want to run the reorganization job. Reorganizations can be performed immediately or can be scheduled at a later time perhaps during off-peak hours.

  After submitting a reorganization job, you can check its status by using the Enterprise Manager Console - Jobs window. Jobs will be listed in the Active or History tab views depending on their status. Double-click on the job listing to obtain information about the job properties and operations.

  Note: If you are using a Microsoft NT workstation, be sure that your NT user account has the right to 'Log on as a batch job.' Use the Microsoft NT User Manager to ensure this right is enabled.

  
  
  

Should a job fail; for example, due to insufficient free space in a tablespace to carry out the reorganization, there are two ways of dealing with the failure:

1. Correct the error at the destination database (for example, by increasing the size of a tablespace) and proceed with the reorganization. From the Enterprise Manager Console create a new job. To do this, right-click on the failed job and select the Create Like... menu item. Then from the Create Job dialog, click Submit. The job will be resubmitted.
2. Undo the changes from the reorganization job. From the Enterprise Manager Console create a new job. To do this, right-click on the failed job and select the Create Like... menu item. From the Create Job dialog select the Parameters tab. Change the parameters from 'agent false proceed_and_cleanup' to 'agent false revert' and submit the modified job.