Re: Datafile AUTOEXTEND and system performance

From: Niall Litchfield <niall.litchfield_at_gmail.com>
Date: Wed, 4 Jan 2012 15:15:12 +0000
Message-ID: <CABe10sYRn=h1oEaCSFm9mShdFgNL6WtUkJe9MUqx1Q57vbJyZg_at_mail.gmail.com>

I think there are 2 basic things to consider for "efficiency" and the results will be system and site dependent. Its worth re-iterating though that I think the efficiency or otherwise of autoextend from a performance viewpoint is way down the list of priorities. (it was just interesting at a quiet time).
First is, *overall *how efficient do you want the allocation of space to be. The best result will be achieved if you never autoextend but size exactly correctly to begin with :) In general the fewer file extension operations you do the better, but its a declining win.

Second, how much do you want to delay any individual transaction that triggers a file extension event? here the opposite applies, the more small file extensions you have the shorter each individual event will be.

I have some figures and a sample script for others to test/critique etc at   http://orawin.info/blog/2012/01/04/proof-by-extension/

cheers

Niall

On Sat, Dec 24, 2011 at 12:38 PM, 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
>
>
>

-- 
Niall Litchfield
Oracle DBA
http://www.orawin.info


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

Received on Wed Jan 04 2012 - 09:15:12 CST