Re: tablespace management

rupinder braich wrote:

> Hello
> I have been using Oracle 8i with tablespace having autoextend set
> to ON and currently the tablespace is used 97% (checked through DBA studio).
> I want to know when Oracle will autoextend the tablespace. The database is
> responding very slowly and could it be one of the reason that tablespace is
> low?
>
> I also want to know when the oracle is going to extend the size of the
> tablespace(is it when it reaches 100% full) or should it be little bit
> earlier? I have also checked the disk space and there is sufficient disk
> space available to extend the tablespace.
>
>
> Thanking you in advance.
>
> Rupinder
>

The actual process of increasing a datafile's size, whether you do it "by hand" or allow it to autoextend, will cause a performance (I/O) hit for the relatively short time it is occurring, and if you have autoextend on, the transaction that triggered the extension will have to wait until it completes. But simply having a tablespace (one or more datafiles) at 97% high water mark should not by itself cause any performance problems.

Why not just extend the tablespace yourself now, to eliminate the suspense over when it will happen? Some would recommend that you never use autoextend, instead be fully aware of your database's growth and usage patterns and size all the tablespaces appropriately, so that no transaction has to unexpectedly incur this overhead.

If you're trying to figure out a general performance problem, autoextension probably isn't the first place to start. There is an entire book "Oracle 8i Designing and Tuning for Performance" at http://tahiti.oracle.com, start there. And consider upgrading to version 9i as soon as you can.

--Mark Bole Received on Sun Aug 08 2004 - 14:29:26 CDT