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
NOTE: The Unrecoverable/Nologging option should not be used if there is a
Possible Reorganizing Strategies
- Cron jobs at fixed times — the most common strategy.
- Inside batches, after massive changes, to keep objects current — a fairly common
- Inside batches, before massive changes, to improve the batch performance — used
- 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
Some Procedures Related to Table Reorganization
- Coalesce free extents in tablespaces, before and after each reorg; here is a script:
- 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
- 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
• 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
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
- 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
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
- 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:
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.
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
However, I would not recommend running these scripts against the whole database in one
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
Before you can begin, you should have some system privileges (see the beginning of the
• 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.
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
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
TRUNCATE TABLE tabsys_ts;
INSERT INTO tabsys_ts VALUES ('SYSTEM', 'USERS');
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
- 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.
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 —
Other Related Articles
... to read more DBA articles, visit http://dba.fyicenter.com/article/