Re: Datafile AUTOEXTEND and system performance
- From: Niall Litchfield <niall.litchfield@xxxxxxxxx>
- To: sreejithsna@xxxxxxxxx
- Date: Wed, 4 Jan 2012 15:15:12 +0000
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@xxxxxxxxx>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--
> //www.freelists.org/webpage/oracle-l
>
>
>
--
Niall Litchfield
Oracle DBA
http://www.orawin.info
--
//www.freelists.org/webpage/oracle-l
Other related posts:
