 |
|
Oracle data file management FAQ
Oracle Tips by Burleson Consulting |
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
its performance?
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
performance problem.
>> 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
or waiting
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
tablespace size?
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
book "Oracle
Disk I/O Tuning" is superb.
Also see my collected notes on Oracle data file
and tablespace management:
 |
If you like Oracle tuning, you may enjoy my new book "Oracle
Tuning: The Definitive Reference", over 900 pages
of BC's favorite tuning tips & scripts.
You can buy it direct from the publisher for 30%-off and get
instant access to the code depot of Oracle tuning scripts. |
|
