| Advice Needed-Free space in tablespace for performance [message #182504] |
Sat, 15 July 2006 16:51  |
dba-k
Messages: 3
Registered: July 2006
|
Junior Member |
|
|
Could anybody provide some thoughts about the free space inside an Oracle database. Does the
amount of free space inside the tablespace containing all the tables inside an OLTP Production
database affect its performance.
Should there be a threshold for free space on the disk where the datafiles are located. As the SYSTEM
and UNDO tablespaces are on AUTOEXTEND mode, is it possible to find how much space they would swell
on at the worst peak.
And lastly, is it better to increase the datafile size or add another datafile to increase tablespace size.
The database in question is an OLTP Production database on Oracle 9205 & Solaris 5.8
Please do let me know if there is any other info that I missed to provide here.
Thanks in advance...
|
|
|
|
|
|
| Re: Advice Needed-Free space in tablespace for performance [message #182588 is a reply to message #182525] |
Mon, 17 July 2006 03:20  |
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
| Quote: |
Having the tablespaces in autoextend mode is not a good idea. At some point, you will be running out of disk space. System tablespace is not supposed to grow "out-of-control".
|
I must politely take issue with this statement.
If you have set the maximum size that the tablespace can grow to to be within the capacity of your disk system, then on balance I feel that auto extend is worth it.
The downside is that you ill take longer to spot a problem causing a tablespace to grow uncontrolably, but these are rare in my experience.
The upside is that your system will not suddenly grind to a halt and start displaying errors to every user atempting to insert a record because you were on holiday and the tablespace needed extending unexpectedly.
On balance, I feel the benefits outweigh the disadvantages.
|
|
|
|
| Re: Advice Needed-Free space in tablespace for performance [message #182620 is a reply to message #182588] |
Mon, 17 July 2006 06:49 |
 |
Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille
|
Senior Member
Account Moderator |
|
|
You are right. But the root issue is still there.
If i am on a holiday, if the disk runs out-of-space or if the datafile reaches the the maximum size with autoextentd on, i still cannot do anything. Worse, i do not have a local disk space to add/extend the datafile. I Should call my Unix/System Support guys ( Specifically in my case, we are using SAN. For every new slice i need, i have to ask them ahead)
In a production environment, i prefer to monitor the tablespace usage closely, instead of having surprises.
And again, yes. It is rare case.
Only in a controlled conditions (like data loading, export/import where actual size to be used is unknown) it is OK to have autoextend on, so that data loading process may not halt.
Regards
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
