Re: Datafile AUTOEXTEND and system performance

From: Guillermo Alan Bort <cicciuxdba_at_gmail.com>
Date: Sat, 24 Dec 2011 15:16:45 -0300
Message-ID: <CAJ2dSGSWPj7w=1Du_HO-CvuFz2sJ45OG9OA79BnvMzAiDnYaiw_at_mail.gmail.com>

There would be a noticeable difference if you happened to load all the data at once, in that case autoextend would kick in too frequently and cause some overhead. In a regular environment where autoextend kicks in only a couple times a day it should not present a noticeable performance impact. So in this case autoextend may not present a performance issue. Generally speaking if you know beforehand how much space you are going to need, it's better to allocate that space. Autoextend, as I see it, is supposed to be there for peak growth (or lazy DBAs).

However, I've often found that when working on ASM it's far more convenient form an administration point of view to create the tablespaces using BIGFILE and setting it to autoextend. Then you only need to monitor and worry about ASM space. Bigfile was a bit problematic on regular filesystems, but on ASM it's a really good option.

hth
Alan.-

On Sat, Dec 24, 2011 at 9:38 AM, Sreejith S Nair <sreejithsna_at_gmail.com>wrote:

>
> Hello friends,
>
> I have been asked this question by one of our fellow team member. The
> question goes as follows.
>
> Oracle database 2 nose RAC 11.2.0.2 on Solaris 10 wih ASM
>
> Case 1. You size the database which is expected to grow 50GB in one month
> for say 6 months by creating 10 data files with maxbytes(32GB) without
> AUTOEXTEND so that they have no need to AUTOEXTEND.
>
> Case 2. You add two datafiles with an initial size of say 100M with
> AUTOEXTEND on , on next 512M. You keep on monitoring the ASM disk space
> and add storage when the disk gets full.
>
> The question was which one is efficient. Forget the file management
> overheads and all. The question is just based on system performance or
> 'cost' for AUTOEXTEND ing the datafiles. From my understanding it doesn't
> really makes much difference unless your system is very very busy , though
> I do not know any metrics or how to explain how busy the system is for
> this to make a difference
>
> Please add your valuable comments on this.
>
>
> Cheers,
> Sreejith
>
> --
> Sent from my iPhone--
> http://www.freelists.org/webpage/oracle-l
>
>
>

--
http://www.freelists.org/webpage/oracle-l

Received on Sat Dec 24 2011 - 12:16:45 CST