FAQ
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

Search Discussions

  • Guillermo Alan Bort at Dec 24, 2011 at 6:17 pm
    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 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
  • Iotzov, Iordan at Dec 28, 2011 at 10:02 pm
    Sreejith,

    About the impact of autoextend:
    It is not that much about DB metrics as it is about the user experience. If your users expect sub-second response time, they might be affected (for a short time) during a DB file auto-extend. In most other cases, the end users should be fine - they might still get delayed a bit by auto-extend, but their overall experience/SLA should be fine.

    As Alan mentioned, you are better off pre-allocating the space in the tablespaces, and use autoextend only for unexpected growth. You can find more info about that in an entry I just posted on my blog -
    http://iiotzov.wordpress.com/2011/12/28/to-autoextend-or-not-to-autoextend


    Iordan Iotzov
    http://iiotzov.wordpress.com


    -----Original Message-----
    From: oracle-l-bounce@freelists.org On Behalf Of Guillermo Alan Bort
    Sent: Saturday, December 24, 2011 1:17 PM
    To: sreejithsna@gmail.com
    Cc: Oracle - L
    Subject: Re: Datafile AUTOEXTEND and system performance

    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 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




    This message and its attachments may contain legally privileged or confidential information. It is intended solely for the named addressee. If you are not the addressee indicated in this message (or responsible for delivery of the message to the addressee), you may not copy or deliver this message or its attachments to anyone. Rather, you should permanently delete this message and its attachments and kindly notify the sender by reply e-mail. Any content of this message and its attachments that does not relate to the official business of News America Incorporated or its subsidiaries must be taken not to have been sent or endorsed by any of them. No warranty is made that the e-mail or attachment(s) are free from computer virus or other defect.
    --
    http://www.freelists.org/webpage/oracle-l
  • Marco Gralike at Dec 29, 2011 at 6:01 pm
    no idea if there is already some metrics around, but a long long long time ago when auto extending was a new feature I asked Oracle to create some metrics, as an enhancement request because I expected a bug (performance related) during the extending phase. The reply took too long, so in the end I created a workaround via a scheduled PLSQL solution to capture it. Have no idea btw anymore what the outcome was. The SR was closed due to "customer has found a workaround". As said it was a long time ago…auto extend was introduced in version…?

    On 28 dec. 2011, at 23:00, Iotzov, Iordan wrote:

    Sreejith,

    About the impact of autoextend:
    It is not that much about DB metrics as it is about the user experience. If your users expect sub-second response time, they might be affected (for a short time) during a DB file auto-extend. In most other cases, the end users should be fine - they might still get delayed a bit by auto-extend, but their overall experience/SLA should be fine.

    As Alan mentioned, you are better off pre-allocating the space in the tablespaces, and use autoextend only for unexpected growth. You can find more info about that in an entry I just posted on my blog -
    http://iiotzov.wordpress.com/2011/12/28/to-autoextend-or-not-to-autoextend


    Iordan Iotzov
    http://iiotzov.wordpress.com


    -----Original Message-----
    From: oracle-l-bounce@freelists.org On Behalf Of Guillermo Alan Bort
    Sent: Saturday, December 24, 2011 1:17 PM
    To: sreejithsna@gmail.com
    Cc: Oracle - L
    Subject: Re: Datafile AUTOEXTEND and system performance

    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 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




    This message and its attachments may contain legally privileged or confidential information. It is intended solely for the named addressee. If you are not the addressee indicated in this message (or responsible for delivery of the message to the addressee), you may not copy or deliver this message or its attachments to anyone. Rather, you should permanently delete this message and its attachments and kindly notify the sender by reply e-mail. Any content of this message and its attachments that does not relate to the official business of News America Incorporated or its subsidiaries must be taken not to have been sent or endorsed by any of them. No warranty is made that the e-mail or attachment(s) are free from computer virus or other defect.
    --
    http://www.freelists.org/webpage/oracle-l



    --
    http://www.freelists.org/webpage/oracle-l
  • Rjamya at Jan 3, 2012 at 4:08 pm
    Per doc id 115178.1, it was Oracle 7.2 that autoextend was introduced.
    Raj
    On Thu, Dec 29, 2011 at 1:00 PM, Marco Gralike wrote:
    As said it was a long time ago�auto extend was introduced in version�?
    --
    http://www.freelists.org/webpage/oracle-l
  • Niall Litchfield at Jan 4, 2012 at 3:16 pm
    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 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

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouporacle-l @
categoriesoracle
postedDec 24, '11 at 12:39p
activeJan 4, '12 at 3:16p
posts6
users6
websiteoracle.com

People

Translate

site design / logo © 2022 Grokbase