DBA > Articles

Automated Table/Index Reorganization In Oracle8i

By: Mike Hordila
To read more DBA articles, visit http://dba.fyicenter.com/article/

(View this artlcle, downloadable scripts, and links, online at http://www.dbazine.com/hordila1.html) Automation can free the DBA of boring, time-consuming tasks and allows him to focus on more challenging activities.
Databases normally have a number of very volatile tables and indexes, and I felt that strong, automated reorganizations would be beneficial. The result is a comprehensive solution — a complete PL/SQL package that can perform periodic table and associated index reorganization automatically, is self-tuning, portable, and (almost) platform and version independent. I started this project since similar commercial products are a lot more complex and normally are extremely expensive.
This PL/SQL package is a complementing solution to the one presented in my article, “Setting Up an Automated Index-Rebuilding System” (Oracle Publishing Online - September 2001). It can be run as a periodic complement to the auto-reindexing package (PKG_NDXSYS), or instead of it. This solution has been tested on Unix (HP 10.7 and 11, Sun Solaris 7 and 8, AIX 4.3, Linux 2.x) and Windows servers (NT4, 2000), on Oracle versions 8.1.5, 8.1.6, 8.1.7. It should work just fine with Oracle9i, but not with versions earlier than 8.1.5. It requires some knowledge of UNIX shell scripts, SQLPlus scripts, and PL/SQL. However, the full scripts are provided and minimal knowledge would be enough to install the package and get started.


When Reorganizing, How Many Extents to Use?
The current view is that objects do not need to be compressed into a single larger extent to have good performance. Although once the recommendation was to have singleextent objects, today Oracle recommends not to have more than 1024 extents per object and that an object with reasonably and equally sized extents leads to the best performance. A very interesting method is described in the Oracle white paper 711, “How to Stop Defragmenting and Start Living.” Since there is no formula that I know of, and after some experimentation, I propose the use of an algorithm for table and index rebuilds, done manually or automatically (algorithm described in the comments within the package). The extent size upper limit can be increased for extremely large objects. Refer to the Oracle manuals for the official view on space management: Oracle8i Tuning Manual (“Tuning I/O, “Avoiding Dynamic Space Management,” “Evaluating Multiple Extents”), and Oracle8i Backup And Recovery Manual (“Developing A Backup And Recovery Strategy,” “Developing A Backup Strategy,” “Perform Backups After Unrecoverable/Unlogged Operations”). NOTE: The Unrecoverable/Nologging option should not be used if there is a standby database.


Possible Reorganizing Strategies

  • Cron jobs at fixed times — the most common strategy.
  • Inside batches, after massive changes, to keep objects current — a fairly common strategy.
  • Inside batches, before massive changes, to improve the batch performance — used less frequently.
  • Just before backups, to back up an optimized database, or just after backups, to back up the database faster.
  • Dedicated systems, with collection tables, control procedures, and so on — used in more complex environments. Assumptions and Experimental Figures
  • On average, roughly, we could rebuild in one hour 5,000,000 rows or 5GB.
  • We had a time window of between one and three hours, between 21:00 and 24:00.
  • We could not do all objects in one day (session).
  • Time acceptable without reorg is within a certain limit.
  • Some days cannot be used for reorg, as they are being used for cold backup, and so on.

Some Procedures Related to Table Reorganization

  • Coalesce free extents in tablespaces, before and after each reorg; here is a script: ts_coalesce.sql
  • Build a fragmented tablespace as a test environment; use a script such as: ts_fragment.sql (first create a user “cbosys2” and a tablespace for it)
  • The only visible objects in a GUI tablespace tool like Tablespace Manager (Map) are the ones that actually take up physical space: tables and indexes (regular, primary keys, unique constraints, and so on). If some objects are not visible, it means they are just references or definitions (foreign keys, not nulls, checks, and so on).
  • Determine the fragmentation level in a database — here are some fragmentation assessment criteria:
    • high numbers of extents (acceptable < 1024 extents for very large objects — look out for extents per object > 5)
    • high percentages of chained rows per table (acceptable < 3 percent — look out for percentages > 0.1 percent); analyze the tables first
    • high percentages of free space inside blocks (look out for FREESPACE/BLOCK > 2*PCTFREE) • high percentages of free space above high watermark (look out for EMPTY BLOCKS ABOVE HWM > 50 percent)
  • here are a few scripts to help with these tests: objects_for_reorg.sql and obj_next_ext_fail.sql

Important Issues Regarding Table/Index Moving/Rebuilding

  • You cannot perform a table reorg without an index reorg, even if you do not restructure or relocate the index, because the index becomes UNUSABLE after the table reorg, as the ROWID references become invalid. That is why, if you have a table reorg scheduled, you may skip a scheduled index reorg (for the affected indexes).
  • The new index is built either from the data in the index, or from the data in the table, whichever source is smaller. This is called “fast rebuild” and is available since Oracle 7.3.4. If you suspect the index is already corrupted, you will have to drop the index and re-create it with fresh data from the table.
  • Oracle will place a lock on the table for the duration of the table/index move/rebuild. The lock affects INSERT, UPDATE, DELETE statements, but allows SELECT statements. The DML will have to wait until the move/rebuild is done. However, Oracle8i (8.1.x) can allow any DML statement if the DDL runs the ONLINE option.

ALTER TABLE/INDEX table_name/index_name MOVE/REBUILD ONLINE;

  • In this situation (locking), the indexes may not be available to users for some periods of time and performance may be affected. Conversely, the move/rebuild will fail if somebody else has put a lock on the table and Oracle cannot acquire exclusive
  • While ANALYZE COMPUTE does not lock the object, the ANALYZE VALIDATE STRUCTURE locks the object the same as the ALTER TABLE/INDEX MOVE/REBUILD.

The Behavior of the “Alter Table/Index Move/Rebuild” Commands 

ALTER TABLE/INDEX table_name/index_name MOVE/REBUILD TABLESPACE
tablespace_name
STORAGE (PCTINCREASE 0 INITIAL 512M NEXT 256M);

will cause the database to try to locate an extent of 512M in the selected tablespace, to allow rebuild and compression of the existing object. If the object is larger than 512M, the rebuild process will try to acquire a next extent of 256M and continue the rebuild. If there is no extent of 512M, on most versions and platforms, the rebuild process will revert to the tablespace default for INITIAL, and start the rebuild (check or experiment with your version to determine how this feature works). Normally, this does not fail. However, the free space for the next extent (256M) has to be available and found, or the rebuild will fail.

If you are unsure, the clause STORAGE (INITIAL 0K NEXT 0K) will often revert to tablespace defaults and almost always work successfully, if the total free space is enough, but you can end up having a large number of extents (even hundreds or thousands).


Limitations of the “ALTER TABLE MOVE” Command

  • Supported only in Oracle8.1.5 and higher.
  • Does not support directly some objects and some data types:
    • Clustered tables, IOTs, overflow table of an IOT, hash and composite partitions (range partitions are supported), tables with columns containing LONG and LONGRAW types, tables with columns containing user-defined types, indexes on such columns, function-based indexes, domain indexes. Partitioned tables containing a LOB column can be reorg’ed on a per partition basis only; partitioned indexes may not be rebuilt as a whole. For some object types and data types, there are special commands that can be used as workarounds.
  • Most of these limitations apply also to Create Table As Select (CTAS ) methods.
  • Some of them apply also to index rebuilds.
  • You can still use the SQL*Plus COPY command or the EXPORT/IMPORT utilities.

Manual Object Reorganization

Roughly, for us, the execution time was 100 minutes per 1GB of really used space (data). Regarding resources, reorganizing can require up to 300MB of memory and up to 30 percent

  • reorg.sql - script to reorg all tables in the database
  • reindex.sql - script to rebuild invalidated indexes - called by reorg.sql
  • ts_coalesce.sql - script to coalesce tablespaces - called by reorg.sql CPU. It requires a lot less on smaller systems.


    The following method will keep the rest of the database online and available to users. For each table, there are two steps:

    Step 1
    The ALTER TABLE MOVE command will lock the table for changes, but will allow queries. While the table is moved, the new table will actually be a TEMPORARY segment in the destination tablespace, named something like, “52.42” for the duration of the reorg. The old table will continue to be there and is dropped (and the new table renamed to the old name) only when the new table build is finished successfully. The TEMP tablespace is normally not used. However, RBS and redo logs can take a serious hit.
    If there is not enough space, the procedure will fail and the old table will remain in place. This procedure can be run by the schema owner or by the SYSTEM user. Relocating tables to other tablespaces can be done manually, by editing the generated reorg.lst script. If there is enough spare space, one can create one or two flip-flop tablespaces, dedicated to moving around reorganized objects, so that the objects are always rebuilt in only a few larger extents when moved to the other tablespace.

    Step 2
    The table move will change the ROWIDs of the table rows, and as such the indexes, which are based on ROWIDs, will become invalid (UNUSABLE). Therefore, step two must be executed immediately after step one: rebuild the invalid indexes on the current table. At the same time, the advantage of using the “table move” procedure is that all constraints are preserved, and index definitions are also saved, so that reindexing is possible using the fast index REBUILD method, rather than the slower index DROP and CREATE method.
    The ALTER INDEX REBUILD command will restore the index to a valid state. While the index is rebuilt, the new index will actually be a TEMPORARY segment in the destination tablespace, named something like, “15.64” for the duration of the rebuild. The old index will continue to be there and is dropped (and the new index renamed to the old name) only when the new index is finished successfully.
    There is also another type of TEMPORARY segment during the rebuild: the segments for storing the partial sort data, because for larger indexes the SORT_AREA_SIZE is normally too small. These segments are located in the TEMP tablespace and they become visible as soon as the SORT_AREA is filled and spills over to disk. When the whole index is contained in these segments, their growth will stop and the segments that will hold the final index will start to grow in the destination index tablespace. For small indexes, there are no segments in the TEMP tablespace, as the sorting happens in memory (in the SORT_AREA, outside the SGA). Anyway, especially for large objects, RBS and redo logs can take a serious hit. You should also watch for space in the ARCHIVE LOGS directory.
    If there is not enough space, the procedure will fail and the old index will remain in place. This procedure can be run by the schema owner or by the SYSTEM user. Relocating indexes to other tablespaces can be done manually by editing the generated reindex.lst script. If there is enough spare space, one can create one or two flip-flop tablespaces, dedicated to moving around reorganized indexes, so that the indexes are always rebuilt in only a few larger extents when moved to the other tablespace.
    This method is by far the preferred manual method for table/index relocation and reorganization/defragmentation.
    However, I would not recommend running these scripts against the whole database in one session.
    If you need more sessions to go through all the objects in the database, you can use a similar technique to the one illustrated for building session-based scripts for the ANALYZE command, discussed in my article, “Automated Cost Based Optimizer” (Oracle Publishing Online — September 2000), in the section, “Manual Analysis of the DB1 Database.”


    Automated Object Reorganization

    Our strategy will be a combination of cron jobs and a PL/SQL package (PKG_TABSYS).
    Reorganizing tables/indexes normally can be done online, without dropping objects, and has a very positive impact on the general performance of the database. I have been running the package for the last year with no serious problems. The execution times seem to decrease steadily after a few runs, as the package has some self-tuning capability. The average move/rebuild times on Oracle8i have come down from 90 minutes to 45 minutes. In theory, at least, the more it runs, the less fragmented the objects become, and the faster the systems will be. Some degree of tablespace level fragmentation is to be expected.
    Remember that tablespace fragmentation does not affect performance, but only the growth capacity of the objects (especially very large ones). You should keep an eye on the free space.


    Prerequisites
    Before you can begin, you should have some system privileges (see the beginning of the INSTALL_TABSYS.SQL script).
    • Set UTL_FILE_DIR = * (or at least c:\temp, or /tmp, etc.) in INIT.ORA, to allow log files to be created.
    • Set JOB_QUEUE_PROCESSES = 2 (or higher) in INIT.ORA, to allow DBMS_JOB scheduling to work.

    Associated Tables
    A set of three tables (TABSYS_LIST, TABSYS_SORT, and TABSYS_HIST) hold identifying, processing, and historical information. The data collected in the history table can also be used for queries later on to find information useful for growth monitoring and capacity planning. A fourth table (TABSYS_TS) holds the information about corresponding pairs: source table tablespaces and target table tablespaces. You may want to give careful consideration to this as it will cause table relocation. Check for available space in the tablespaces.


    Search for the following section in the INSTALL_TABSYS.SQL script and adapt it to your particular environment, before installing the package. The package reads this table and checks object location every time it runs. 


    -------------------------------------------------------------------------------
prompt POPULATING TABLE tabsys_ts WITH YOUR VALUES
prompt
TRUNCATE TABLE tabsys_ts;
COMMIT;
INSERT INTO tabsys_ts VALUES ('SYSTEM', 'USERS');
COMMIT;
-------------------------------------------------------------------------------

    This will relocate any table found in any of the tablespaces in the left-hand column (e.g., SYSTEM) to the corresponding tablespace in the right-hand column (USERS, for objects not owned by SYS or SYSTEM, in this case). If you do not populate the table or just insert the same values left and right, then the object will not be relocated. You can update this table manually any time in the future.

    Overview of the Package

    Basically, the Automated Table/Index Rebuild package (PKG_TABSYS) runs the ALTER TABLE MOVE command followed immediately by the 'ALTER INDEX REBUILD' command, and will also:

    • clean up residual temporary segments
    • coalesce free space in tablespaces
    • analyze the structural integrity of the objects
    • generate valuable statistics usable by the CBO
    • de-allocate unused space from object blocks
    • shrink object segments
    • realign the high watermark to low levels
    • reorganize fragmented objects into fewer extents
    • restructure (optimize) tablespace storage options
    • restructure (optimize) table storage options
    • compact table blocks into fewer blocks
    • reattempt to run with modified parameters in case of failure
    • generate alerts if it detects failure to grow or reorg
    • detect some generic unavailability conditions
    • process both tables and indexes
    • reorganize/defragment, actually, the entire database

    The code (circa 2500 lines) performs a lot of error checking and decision making in support of the commands. Since you cannot reorg everything in one session, objects are sorted and organized in manageable sessions, which are then run one a day, until the cycle is finished and a new cycle begins. Each table reorg will cause the associated indexes to become invalid (UNUSABLE) and as such an index rebuild MUST be performed after the table reorg.
    Initially, we build a few tables (see the previous Associated Tables section), then we populate them with data from the DATA DICTIONARY and calculate them by running the package with information about the processable objects (tables and indexes), sorted by size (bytes) in descending order. The system examines the objects one by one and marks them with 0 if no reorg needed, with 99 if reorg required, with 999 if last reorg failed, and with 9999 if the last reorg was successful.
    Based on a series of rules, the system then decides which object is assigned to which session. It starts with the first session, “empty,” and examines the first object against the rules. If there is a need for reorg, the object is assigned to the current session; if there is no match, it is left for the next session. The process continues until all objects are assigned, and there are a number of sessions.
    We then start to run the sessions, one at a time (probably daily). The results of the run are written back into our TABSYS tables, to be used the next time we build sessions. When all sessions are done, we examine the logs in the /tmp or c:\temp directories for failed runs, and attempt to run them again. Upon completion, an email message is sent to the DBA, and the process is ready to start again.
    When run manually in a SQLPlus session, display procedures ensure that debugging and detailed logging (hundreds of lines of messages) can be done as easily as possible. Currently, these modules are commented out to avoid crashing the package because of overloading the server output buffer — uncomment them selectively for databases with very large numbers of objects.
    Although it will not account for all situations, the package does log a wide variety of errors. The DBA should treat errors manually as the automated system will only try to re-run a session in case of failure. Some errors, like “failed because of resource busy,” simply mean that a lock could not be obtained, since some other process was using the object. This error can be ignored, as the transaction will probably succeed on the next run. A number of conditions and options (e.g., parallel, analyze, nologging, and so on) are also available to be enabled or disabled in the package body. Objects dropped after the list was created will also cause benign errors. Also, hitting tables with data types not supported for MOVE will simply generate an error message and skip to the next object. If the package is run automatically with 'DBMS_JOB', we get only the SUMMARY OUTPUT, which can include captured error messages. Most error messages will also be logged in the TABSYS tables themselves.


    Setup
    The package is installed into the default Oracle schema MHSYS, which I use to host my automation packages. It can be installed, as is, for UNIX and NT-based servers. It is a pretty comprehensive piece of software, is compatible with Oracle8.1.5 or higher, on both UNIX and NT, and includes routines to detect the current OS, Oracle version, and SID.
    The code is amply commented. Run the INSTALL_TABSYS.SQL script as user SYSTEM from SQLPlus, but before installing, you should read the top of the package body, in case you need to make some modifications. This section can also be used for tuning later by changing the values of a very large number of constants. Make sure the script does not drop the existing schema MHSYS if it is already installed. The defaults will cover most situations and, most likely, nothing will need to be changed. It has been run against objects with sizes of up to 3500 MB. Sessions can vary between 10 — 300 minutes. Have the logs emailed to you, or, at least, examine them manually.
    You can use scripts to schedule or run the package similar to the ones described in my article, “Setting Up an Automated Index-Rebuilding System” (Oracle Publishing Online — September 2001).

Other Related Articles

... to read more DBA articles, visit http://dba.fyicenter.com/article/