Oracle tablespace tuning tips
Oracle Database Tips by Donald Burleson
There are some general tip for ensuring optimal tablespace
allocation and tuning of tablespaces:
- Always use "autoextend on": This
prevents a tablespace from ?hanging? in a condition where an
object cannot extend. When combined with ?maxextents
unlimited? for the tables/indexes, the space management monitoring
shifts from the tablespace to the disk mount point.
- Always use ASM for the tablespace data files:
Oracle ASM's use of RAID 0+1 (striping and
mirroring) ensures that you never see any chronic disk hot spots.
Many professional have questions about Oracle
data file and tablespace management, and I've tried to start this
FAQ with common Oracle data file questions.
>> Does the amount of free space inside the tablespace
containing all the tables inside an OLTP Production database affect
Not if your SQL uses indexes. However, if
have free space within a table's or indexes extents and you see your SQL doing
full-scans, then yes, it might affect performance. I wrote "Oracle
Tuning: The Definitive Reference" with details on all of this
and scripts that you can run to see if you have a space-related
>> Should there be a threshold for free space on the disk where the
datafiles are located?
No. If you restrict their growth, the database will lock-up
Oracle, as the database freezes when a table runs out of extents
for a tablespace to extend. Always use "maxextents unlimited"
and "autoextend on".
>> If my tablespaces
are on AUTOEXTEND mode, is it possible to find how much space they
would swell on at the worst peak?
Mike Ault has
Oracle predictive modeling spreadsheets for
predicting tablespace growth.
>> How can I tell if my UNDO tablespace
is large enough?
If you run out of UNDO you will get the
message "ORA-30036: unable to extend segment by 8192 in undo
tablespace 'UNDOTBS'" and the update will abort. The size
of your UNDO is governed by the total space required to store
the before images for your largest DML transaction. The
size of your UNDO is governed by the total space required to
store the before images for your largest DML transaction.
Some DBA's use the "autoextend" option on
their UNDO tablespace, but you run the risk of running out of
disk space if you have a super-large update running, or a
runaway update. Like everything it Oracle, the choice us using
qutoextend for UNDO depends on your system requirements. If your
SLA requires you to minimize aborts, it might be worth the risk
of filling-up a disk.
>> Is it better to increase the Oracle
datafile size or add another datafile to increase the Oracle
Some DBA's choose to add datafiles instead
of using autoextend for the tablespace. The choice of
adding datafiles depends on your RAID level and how many disk spindles you have.
Mike Ault has an eBook dedicate to Oracle space management, and his
Disk I/O Tuning" is superb.
Also see my collected notes on Oracle data file
and tablespace management:
Oracle Training from Don Burleson
The best on site
training classes" are just a phone call away! You can get personalized Oracle training by Donald Burleson, right at your shop!