This important Oracle DB question has been around for a long time without a definitive answer. This post not only gives an objective overview of the pros and cons of using autoextend (on) ,but also provides some guidance on how to handle common issues related to the autoextend option.
Here are some definitions I am going to use:
- “Autoextend ” is the primary disk space management mechanism when DBA does not do any monitoring on tablespace level, but rather forecasts and monitors the file system(s) or ASM disk group(s) that hold the database.
- “Autoextend ” is a secondary disk space management mechanism when DBA monitors and forecasts tablespace usage and makes sure that there is free space in each tablespace to accommodate growth for some time. The DBA uses “autoextend on” only as a way to handle unexpected data growth.
Pros of autoextend on:
- Ability to accommodate unexpected data growth.
That is the major benefit of that feature. Some believe that rigorous forecasting and monitoring are enough for disk space management. I firmly believe that a DBA should monitor and forecast tablespace usage. I even presented a paper that explains, among many other things, how to forecast tablespace usage using OEM repository tables. Nevertheless, I am convinced that no monitoring or forecasting would be able handle an unexpected surge in activity (hence unexpected :) ).
Resumable space allocation (RESUMABLE_TIMEOUT ) is another way to handle unexpected data growth.
- Easiness to manage
Autoextend as a primary disk space management mechanism can simplify somehow disk monitoring and forecasting of databases with large number of tablespaces but small number of ASM diskgroups /file systems.
In general, however, what autoextend does is just shift the burden of monitoring and forecasting from DB tablespace monitoring and forecasting (DBA) to file system/ASM monitoring and forecasting (SA/DBA). That’s not that big of a benefit.
Cons of autoextend on:
/Wording slightly modified on 1/22/2013 – Thanks to J. Garry for the useful suggestion/
- As a result of an application or user error, a single tablespace can fill up the whole filesystem/ASM group, bringing all applications using that filesystem/ASM group to a halt.
This is a valid concern that can be addressed in most cases.
First, tablespaces that have higher chance of experiencing uncontrolled growth, such as TEMP, UNDO, “staging”, etc, should not use autoextend on.
Second, DBA should limit (utilizing max file size parameter) how much a single tablespace can grow. A single tablesopace should not be allowed to use all available disk space. For instance, if there is 50G free in an ASM group, DBA should make sure that no single tablespace can grow more than 40G (leaving 10G safety buffer).
If a single table growth can affect more than one tablespace (DATA and INDEX, for instance), then the combined tablesopace growth should be less than the free disk space. If we have 50G free in an ASM group, DBA should allow DATA and INDEX tablespace to grow by only 20G each, so both can grow no more than 40G.
This is a workable solution only when autoextend is a secondary disk management mechanism. If autoextend is a primary disk management mechanism then the free space will change rather frequently (after each autoextend), so staying on top of those limitations can be very hard.
- The actual process of extending DB files, allowed by autoextend on, causes performance issues.
This could be a valid concern, particularly for OLTP system with end-users who expect response in sub-second time. Using autoextend as a secondary mechanism greatly reduces the chances of this happening. In that case, autoextend growth, with performance issues and all, it is much better than the alternative – outright failure.
Autoextend is an excellent DB option that can provide us with protection against unexpected data growth. Used properly, autoextend has minimal downside. In overwhelming majority of cases, autoextend should be used as a secondary disk space management mechanism.