Storage Management

Some of the information presented in this article has been hotly debated for many years. Please read this article with an open mind. If you feel so inclinded, test this information for yourself, and let me know if you get results that disagree with this information and under what circumstances the results disagree.

Background:
Prior to Oracle7.3, an Oracle database had limitations on the number of extents that could be allocated per physical segment. For instance, a table or index could allocate a maximum of 121 extents if the database block size was 2K, 249 extents if the database block size was 4K, etc. As of release 7.3 and all later releases, this limitation has been removed. Tables, indexes, rollback segments, and other segments requiring physical storage can now have "unlimited" extents. In this case, "unlimited" actually means a 32-bit integer worth of extents, or 2.1 billion. Also, datafile sizes are no longer fixed. Datafiles can be manually resized to larger and, in some cases, smaller sizes, and can be set to auto-extend up to a maximum value as necessary. So, there are still limitations (not truly "unlimited"), but the limitations are so large that in practical terms they close enough to "unlimited" to provide the intended utility.

With the storage management limitations prior to release 7.3, it was necessary to custom size extents for each table, index, or other segment such that the probability of running out of extents was tremendously reduced. The only way to correct a table that had reached it's maximum extent limit was to "reorganize" the table. This involved making a copy of the table and data either by exporting it or by issuing a CREATE TABLE AS SELECT statement, dropping the original table and re-creating it with more appropriate extent sizes, then placing the data back into the table by importing or issuing a INSERT INTO SELECT * FROM statement. In addition, it was necessary to re-create the necessary indexes, constraints, grants, and triggers on the table, as well as re-creating any foreign key constraints that referenced primary or unique key constraints on the table in question. Obviously, this whole process requires downtime and is highly mistake-prone.

One of Oracle Corporation's major motivations for introducing the improved storage management capabilities was to improve availability. The reorganize procedure is a somewhat risky procedure and requires an interruption
in availability. The most likely time for a table to reach max extents was during a period of heavy activity, which normally happens to also be the period of time when availability is most crucial. It was necessary for DBA's to plan ahead extremely well and to micro-manage space in an Oracle database. Thanks to these improved storage management features, these extreme measures in storage management and downtime for reorganization are essentially non-value added activities, and can, in most reasonable circumstances, be discontinued.

Storage Management Facts about Extents
The early needs to micro-manage storage and reorganize tables has led to a number of fallacies, with most of them involving performance concerns resulting from table fragmentation and the number of extents. Below are the facts intended to invalidate some of the fallacies.

1) Table access through the use of an index is immune to table fragmentation. When table rows are accessed by rowid retrieved from an index, the Oracle instance knows the exact location of the row in terms of file, block within the file, and row within the block. So, using access by index, it takes exactly the same amount of time to read 1 million rows from 1000 extents as it does to read 1 million rows from a single extent.

2) Using full table scans, it takes exactly the same amount of time to read 1 million rows from 1000 extents as it does to read 1 million rows from a single extent. This is true regardless of the total number of extents. When the Oracle instance is performing full-table scans, it is sensitive to the number of rows per block, the multi-block read settings as an evenly divisible factor of the extent size, the percentage of empty space per block, and the percentage of empty blocks in an extent. There are, of course, exceptions to every rule, but, in general, it is not sensitive to the total number of extents.

3) Statements that add or remove 1 million rows from 1000 extents will take longer than statements that add or remove 1 million rows from one extent. Examples would include INSERTs, TRUNCATEs, or DROP TABLE statements. The difference here is that recursive SQL is required to allocate or deallocate the correct number of extents. 1000 recursive SQL statements and extent allocations/deallocations will obviously take longer than 1 recursive SQL statement and extent allocation/deallocation.

4) Oracle re-uses empty space in table segments where possible. Tables will almost always reach a certain level fragmentation, and remain at that level of fragmentation throughout their remaining existence.

5) Oracle does not re-use empty space in an index segment unless the entire block has been emptied. For this reason, index fragmentation tends to increase over time for certain indexes that receive the right mix of transactions.

In general, HAVING a large number of extents does NOT degrade performance; ALLOCATING and DEALLOCATING a large number of extents DOES degrade performance.

A sensible approach to storage management
Since individually sizing table and index extents is no longer truly required, what is a good approach to storage management? The answer, although it does have exceptions, is fairly simple: pick a reasonable extent size, and use it throughout the database. For instance, if your database block size is 8K, then choose 80K, 160K, or 800K as a standard extent size (based on how large you expect typical segments and the database itself to be). Alternatively, you could choose three extent sizes for small, medium, and large segments, making sure that the extent sizes are multiples of one another (for instance, 80K, 800K, and 8M). A third choice would be to choose one extent size as a standard and a multiple of this standard size to use for exceptions (say, 80K for all segments except extremely large ones, where 1600K will be used). The point here is that by choosing a small number of standard extent sizes (in particular, choosing one extent size), you tremendously improve the database's ability to re-use deallocated space in tablespaces. This keeps tablespace-level fragmentation to a minimum. Under typical circumstances, tablespaces, like the tables they contain, will reach a certain level of fragmentation and remain at that level when using a standardized extent size. In situations where a customized, calculated extent size is used for each table and index, a higher level of tablespace fragmentation will result because any deallocated extents cannot be easily reused. Tablespace fragmentation may have effects on performance in some cases, but the biggest effect is wasted storage that cannot be reused without reorganizing the tablespace.

Think about the following analogy: Is it easier to build a house with 10,000 bricks that are all the same size, or is it easier to build a house with 10,000 different brick sizes? The answer should be obvious.

Suggestions for table and tablespace reorgs

Avoid them! Choose a standard extent size and stick with it. Tablespace fragmentation will be minimized, thus removing the benefits of doing a tablespace reorg. Tables won't typically need to be reorganized to since they don't have a practical limit on extents. Reorgs should be the exception rather than the rule. Only reorg a table or tablespace where it is necessary to fix a problem.

In cases, where a very large table would potentially have an extremely large number of extents, consider using the partitioning features available in Oracle8.0 and Oracle8.1. These features allow DROP's, TRUNCATE's, and other statements that are sensitive to the number of extents to be done on a partition of the table rather than the entire table. This feature helps relieve most of the concerns around tables with large numbers of extents. See the Oracle documentation for your specific version for details.

For those who still find that there IS a detectable performance increase after reorganizing a table or tablespace, consider the following suggestion: rather than performing a reorg on a monthly basis that provides a 10% or even 20% improvement that degrades to 0% over the month, spend that time performing EXPLAIN PLAN on SQL statements from the V$SQL table, and add indexes to tune table access. You'll find that the benefits from the reorgs disappear when table access is done via an index AND that the overall performance of the database instance has increased several fold! Indexes can allow individual or groups of SQL statements to execute tens or even hundreds of times faster, which unquestionably shows up at the instance level. What's more, this work doesn't have to be redone every single month to remain effective!

Suggestions for Index reorgs

Indexes are a bit different from tables in terms of the reuse of storage. In many cases, the particular mix of transactions performed on an index may result in the index having a significant degree of fragmentation in situations where the space cannot be easily reused. In these cases, the index leaf rows may be empty, but that particular spot has either not been placed back on the index's freelist, or another row with the same key value has not been inserted into the underlying table. It is not at all unusual to see 10-20% of the indexes in an Oracle instance with a fragmentation percentage greater than 50% if the indexes have never been reorganized. Index reorgs can unquestionably improve instance performance by a typical 5-20%, depending on other conditions.

There's good news in this area. As of release 7.3 and above, indexes can be rebuilt rather than being dropped and re-created. This feature offers tremendous advantages in that it rebuilds the index using the existing index (much faster than dropping and re-creating), levels it's tree structure, and eliminates empty leaf row space that is not being re-used (as well as allowing it to be rebuilt onto a different tablespace if necessary). Also, indexes enforcing primary keys and unique keys can be rebuilt without needing to drop these constraints, which additionally means that foreign key constraints that point to these primary or unique keys do not have to be dropped and re-created as well. Again, this is an effort by Oracle Corporation to improve availability.

How do you know if an index needs to be rebuilt? Here is the algorithm suggested by Oracle Corporation:

1) ANALYZE INDEX owner.index_name VALIDATE STRUCTURE. This will place a single row into the INDEX_STATS view for that particular session.

2) SELECT del_lf_rows/lf_rows FROM index_stats. Oracle recommends that if this ratio is 15-20% or greater (meaning that 15-20% of the index leaf rows are empty space), the index should be rebuilt.

3) ALTER INDEX owner.index_name REBUILD TABLESPACE tablespace_name. Note that if the tablespace name is not specified, the index will be rebuilt on the connected user's default tablespace. Storage parameters can be specified as well if desired.

Unfortunately, Oracle has thrown us a slight curve. Only one row exists in the INDEX_STATS view (for the last index organized) at any one time. This feature makes scripting the whole process a bit involved, and with small indexes, the ANALYZE and SELECT can take longer than the actual ALTER INDEX...REBUILD statement. With this in mind, there are essentially three different approaches to implementing a weekly or monthly index rebuild job:

1) Rebuild every user index. Simply create a script that generates the necessary ALTER INDEX...REBUILD statements for every non-SYS non-SYSTEM index in the database by SELECTing the literals and necessary tablespace names from the DBA_INDEXES table. In other words, rebuild every user index without evaluating which indexes actually need to be rebuilt. In situations where a large percentage of indexes typically need to be rebuilt or the majority of the indexes are reasonably small, this approach will provide the shortest job run-time. This approach could easily be broken up into multiple jobs that rebuild indexes for specific schemas at different times of the month, for instance. (See the script available on the index rebuild page in the downloads section of this site.)

2) Evaluate every user index and rebuild only the ones exceeding a specific fragmentation percentage. In other words, perform the evaluation routine and the resulting rebuild weekly or monthly. In cases where there are a large the number of indexes in the instance, and the number of indexes that are actually rebuilt is a reasonably small percentage, this job will typically complete much faster (2-5 times) than approach #1. (See the NT executable and C source code available on the index rebuild page in the downloads section of this site.)

3) Evaluate every user index once every several months, but perform the rebuild on that specific group more frequently. In other words ANALYZE the indexes and check the level of fragementation every 3 months, but run the rebuild job for indexes that typically need rebuild weekly or monthly. For instances with a very large number of indexes that are each very large in size, this may be the only practical choice. For any other case, this approach will be the fastest to complete. (Again, see the NT executable and C source code available on the index rebuild page in the downloads section of this site.)

Storage Management Bugs
Since we all know that software doesn't always work as intended, here are the show-stopping bugs related to storage management for Oracle on NT of which the author is aware. (There may be other bugs. Periodically check with Oracle Support or MetaLink. This is your responsibility as a DBA!) Please contact Oracle Support or browse MetaLink for more details.

1) Bug# ??????: Prior to 7.3.3.6.0, control files cannot be re-created if any datafiles have been resized (including auto-extension). This bug applies to releases on all platforms (if I'm not mistaken). This fix carries forward into 7.3.4.3.0, which is the oldest currently supported release.

2) Bug# 711563: In Oracle8 prior to 8.0.4.3.5, datafile resizing or auto-extension to a size greater than 4 Gb results in a datafile corruption error. Under certain conditions, the database may be unrecoverable and data may be permanently lost. This bug is specific to Oracle on NT only.

3) Bug# ??????: In Oracle7, setting the SYS.BOOTSTRAP$ table to unlimited extents causes the database to fail on open during the next startup. This is fixed in Oracle8 by disallowing changes to the MAX EXTENTS of this data dictionary table.

Final Note

NEVER modify storage characteristics, attempt to reorg, or attempt to rebuild any tables, indexes, or other segments that are owned by SYS or SYSTEM, or are otherwise part of the Oracle data dictionary.