FAQ
Has anyone on the list seen a discussion of

performance cost of running in archivelog mode.
overhead of using partitions
time calculation to perform statistics
time savings of using statistics

I always see these things discussed in terms of "more" but never in terms of "how much".

Thanks for any information that you can point me to.

Claudia Zeiler

Search Discussions

  • Ken Naim at Nov 16, 2008 at 6:40 am
    I haven't seen a thread on items you listed nor measured them in a
    scientific manor myself. With that being said, in my experience the decision
    to use each one of those features is usually a no brainer, either you must
    use them or you don't need them; Rarely is there grey ground when it comes
    to using archive logging, partitioning and stats gathering.

    As far as I am concerned performance with archive log mode is the
    baseline and not the other way around since I cannot live without hot
    backups, point in time recovery, block recovery etc.

    Partitioning has overhead associated with it, but when used properly it
    improves performance so dramatically that the overhead is not even noticed
    (when used improperly performance suffers to such a degree the overhead
    doesn't matter either). I have seen up to 5 orders of magnitude reduction in
    resource utilization and up to 3 orders of magnitude reduction in elapsed
    time when partitioning is needed and implemented.

    Gathering stats is such an implementation specific item, it can run from
    no time to seconds to hours, or even days. If most of your data is
    relatively static or not growing very quickly you could possibly go without
    stats collection for months or years, if growth, change, and skew is very
    high than weekly, daily etc. stats gathering for some objects maybe
    necessary or anywhere in between.

    "Good" stats can take queries that will never complete and bring them
    down to milliseconds for a near infinite improvement with the converse being
    true as well.

    Ken

    -----Original Message-----
    From: oracle-l-bounce_at_freelists.org
    On Behalf Of Claudia Zeiler
    Sent: Saturday, November 15, 2008 4:27 PM
    To: oracle-l_at_freelists.org
    Subject: performance impact of archivelog

    Has anyone on the list seen a discussion of

    performance cost of running in archivelog mode.
    overhead of using partitions
    time calculation to perform statistics
    time savings of using statistics

    I always see these things discussed in terms of "more" but never in terms
    of "how much".

    Thanks for any information that you can point me to.

    Claudia Zeiler

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

    --
    http://www.freelists.org/webpage/oracle-l
  • Roberts, David (GSD - UK) at Nov 17, 2008 at 11:09 am
    Performance cost of running in archivelog mode.

    We take our database out of archive log mode for a yearly batch process.
    Very roughly the dry run (which is run on a different but similarly
    specked machine in archive log mode) takes about 20% longer.

    Obviously as the difference is going to be dependent on how much change
    is going on, your mileage may vary.

    David Roberts
    www.logica.com


    Logica UK Limited
    Registered in England and Wales (registered number 947968)
    Registered office: Stephenson House, 75 Hampstead Road, London NW1 2PL,
    United Kingdom

    -----Original Message-----
    From: oracle-l-bounce_at_freelists.org
    On Behalf Of Claudia Zeiler
    Sent: 15 November 2008 21:27
    To: oracle-l_at_freelists.org
    Subject: performance impact of archivelog

    Has anyone on the list seen a discussion of

    performance cost of running in archivelog mode.
    overhead of using partitions
    time calculation to perform statistics
    time savings of using statistics

    I always see these things discussed in terms of "more" but never in
    terms of "how much".

    Thanks for any information that you can point me to.

    Claudia Zeiler

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

    This e-mail and any attachment is for authorised use by the intended recipient(s) only. It may contain proprietary material, confidential information and/or be subject to legal privilege. It should not be copied, disclosed to, retained or used by, any other party. If you are not an intended recipient then please promptly delete this e-mail and any attachment and all copies and inform the sender. Thank you.

    --
    http://www.freelists.org/webpage/oracle-l
  • Renato M at Nov 17, 2008 at 3:18 pm
    Hi, list.

    I'm with the same doubt...
    We have a Data Warehouse database in "no archivelog" mode with 180GB/day of
    redo generation.

    because of a new backup solution/ strategie, we need put it in "archivelog
    ON".

    Claudia,
    Have you any other information about it ?

    Thanks a lot!

    Renato Gomes
    Eds, an HP Company.

    On Mon, Nov 17, 2008 at 9:09 AM, Roberts, David (GSD - UK) <
    david.h.roberts_at_logica.com> wrote:
    1. Performance cost of running in archivelog mode.

    We take our database out of archive log mode for a yearly batch process.
    Very roughly the dry run (which is run on a different but similarly
    specked machine in archive log mode) takes about 20% longer.

    Obviously as the difference is going to be dependent on how much change
    is going on, your mileage may vary.


    David Roberts
    www.logica.com

    Logica UK Limited
    Registered in England and Wales (registered number 947968)
    Registered office: Stephenson House, 75 Hampstead Road, London NW1 2PL,
    United Kingdom

    -----Original Message-----
    From: oracle-l-bounce_at_freelists.org
    On Behalf Of Claudia Zeiler
    Sent: 15 November 2008 21:27
    To: oracle-l_at_freelists.org
    Subject: performance impact of archivelog

    Has anyone on the list seen a discussion of

    1. performance cost of running in archivelog mode.
    2. overhead of using partitions
    3. time calculation to perform statistics
    4. time savings of using statistics

    I always see these things discussed in terms of "more" but never in
    terms of "how much".

    Thanks for any information that you can point me to.


    Claudia Zeiler

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




    This e-mail and any attachment is for authorised use by the intended
    recipient(s) only. It may contain proprietary material, confidential
    information and/or be subject to legal privilege. It should not be copied,
    disclosed to, retained or used by, any other party. If you are not an
    intended recipient then please promptly delete this e-mail and any
    attachment and all copies and inform the sender. Thank you.


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

    --
    http://www.freelists.org/webpage/oracle-l
  • Claudia Zeiler at Nov 17, 2008 at 3:21 pm
    Renato,
    So far I have no more info.

    In my thinking, 180G of redo log generation is already the strain on the system, and 180g of archivelog generation is just a background cleanup process that shouldn't affect the database. But I don't know facts at all. Maybe my assumption will cause someone to tell us that I am totally wrong, and "here's why.". That would be a start in trying to figure this out. I suspect that the answer involves a table with a name that starts like 'k$xz...'.

    Please let me know if you learn anything. I have the same issue as you.
    Thanks,
    Claudia Zeiler

    From: Renato M [renatomelogomes_at_gmail.com]
    Sent: Monday, November 17, 2008 7:18 AM
    To: david.h.roberts_at_logica.com
    Cc: Claudia Zeiler; oracle-l_at_freelists.org
    Subject: Re: performance impact of archivelog

    Hi, list.

    I'm with the same doubt...
    We have a Data Warehouse database in "no archivelog" mode with 180GB/day of redo generation.

    because of a new backup solution/ strategie, we need put it in "archivelog ON".

    Claudia,
    Have you any other information about it ?

    Thanks a lot!

    Renato Gomes
    Eds, an HP Company.

    On Mon, Nov 17, 2008 at 9:09 AM, Roberts, David (GSD - UK) > wrote:
    1. Performance cost of running in archivelog mode.

    We take our database out of archive log mode for a yearly batch process.
    Very roughly the dry run (which is run on a different but similarly
    specked machine in archive log mode) takes about 20% longer.

    Obviously as the difference is going to be dependent on how much change
    is going on, your mileage may vary.

    David Roberts
    www.logica.com<http://www.logica.com>

    Logica UK Limited
    Registered in England and Wales (registered number 947968)
    Registered office: Stephenson House, 75 Hampstead Road, London NW1 2PL,
    United Kingdom

    -----Original Message-----
    From: oracle-l-bounce_at_freelists.org
    [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Claudia Zeiler
    Sent: 15 November 2008 21:27
    To: oracle-l_at_freelists.org
    Subject: performance impact of archivelog

    Has anyone on the list seen a discussion of

    performance cost of running in archivelog mode.
    overhead of using partitions
    time calculation to perform statistics
    time savings of using statistics

    I always see these things discussed in terms of "more" but never in
    terms of "how much".

    Thanks for any information that you can point me to.

    Claudia Zeiler

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

    This e-mail and any attachment is for authorised use by the intended recipient(s) only. It may contain proprietary material, confidential information and/or be subject to legal privilege. It should not be copied, disclosed to, retained or used by, any other party. If you are not an intended recipient then please promptly delete this e-mail and any attachment and all copies and inform the sender. Thank you.

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

    --
    http://www.freelists.org/webpage/oracle-l
  • Bobak, Mark at Nov 17, 2008 at 3:40 pm
    It seems to me, the decision as to whether or not archivelog mode is required is a business decision. Factors that drive that decision are:
    1.) Can downtime be tolerated for taking backups? No=must use archivelog mode
    2.) Do you want to be able to do point in time recoveries and incomplete recoveries? Yes=must use archivelog mode.

    And there are probably other questions to ask, but those are two big ones. Now, assuming the business decision has been made that archivelog mode is required, the question becomes, "How do I create as small an impact on my running production database as possible, when I implement archivelog mode?"

    This answer is purely technical, as opposed to the previous questions, which answered business requirement questions. Here, the first thing to look at is, do you have enough I/O capacity to support the extra reads/writes to support logging of archives? Next, what about CPU capacity (probably not a large requirement, but, if you're already on the edge in terms of CPU and/or memory capacity, it could be a problem.)

    Next, how often do you see a log switch? How large are your log files currently? I like to target a log switch every 20 minutes or so, at peak load. So, size your archive logs appropriately.

    In a reasonably healthy production system, where you're not already maxing out I/O, CPU, or memory, I would not expect that enabling archivelog mode would cause any performance degradation or problems. If your system is already living on the edge, in terms of capacity, you should probably proceed w/ caution.

    Ultimately, in my view, all production databases should be in archivelog mode. End of story. If your system lacks the capacity to support archivelog mode, it's time to buy a bigger system. Archivelog mode is NOT an option for a production system.

    HTH,

    -Mark

    --
    Mark J. Bobak
    Senior Database Administrator, System & Product Technologies
    ProQuest
    789 E. Eisenhower, Parkway, P.O. Box 1346
    Ann Arbor MI 48106-1346
    +1.734.997.4059 or +1.800.521.0600 x 4059
    mark.bobak_at_proquest.com
    www.proquest.com
    www.csa.com

    ProQuest...Start here.

    -----Original Message-----
    From: oracle-l-bounce_at_freelists.org On Behalf Of Claudia Zeiler
    Sent: Monday, November 17, 2008 10:21 AM
    To: Renato M; david.h.roberts_at_logica.com
    Cc: oracle-l_at_freelists.org
    Subject: RE: performance impact of archivelog

    Renato,
    So far I have no more info.

    In my thinking, 180G of redo log generation is already the strain on the system, and 180g of archivelog generation is just a background cleanup process that shouldn't affect the database. But I don't know facts at all. Maybe my assumption will cause someone to tell us that I am totally wrong, and "here's why.". That would be a start in trying to figure this out. I suspect that the answer involves a table with a name that starts like 'k$xz...'.

    Please let me know if you learn anything. I have the same issue as you.
    Thanks,
    Claudia Zeiler

    From: Renato M [renatomelogomes_at_gmail.com]
    Sent: Monday, November 17, 2008 7:18 AM
    To: david.h.roberts_at_logica.com
    Cc: Claudia Zeiler; oracle-l_at_freelists.org
    Subject: Re: performance impact of archivelog

    Hi, list.

    I'm with the same doubt...
    We have a Data Warehouse database in "no archivelog" mode with 180GB/day of redo generation.

    because of a new backup solution/ strategie, we need put it in "archivelog ON".

    Claudia,
    Have you any other information about it ?

    Thanks a lot!

    Renato Gomes
    Eds, an HP Company.

    On Mon, Nov 17, 2008 at 9:09 AM, Roberts, David (GSD - UK) > wrote:
    1. Performance cost of running in archivelog mode.

    We take our database out of archive log mode for a yearly batch process.
    Very roughly the dry run (which is run on a different but similarly
    specked machine in archive log mode) takes about 20% longer.

    Obviously as the difference is going to be dependent on how much change
    is going on, your mileage may vary.

    David Roberts
    www.logica.com<http://www.logica.com>

    Logica UK Limited
    Registered in England and Wales (registered number 947968)
    Registered office: Stephenson House, 75 Hampstead Road, London NW1 2PL,
    United Kingdom

    -----Original Message-----
    From: oracle-l-bounce_at_freelists.org
    [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Claudia Zeiler
    Sent: 15 November 2008 21:27
    To: oracle-l_at_freelists.org
    Subject: performance impact of archivelog

    Has anyone on the list seen a discussion of

    performance cost of running in archivelog mode.
    overhead of using partitions
    time calculation to perform statistics
    time savings of using statistics

    I always see these things discussed in terms of "more" but never in
    terms of "how much".

    Thanks for any information that you can point me to.

    Claudia Zeiler

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

    This e-mail and any attachment is for authorised use by the intended recipient(s) only. It may contain proprietary material, confidential information and/or be subject to legal privilege. It should not be copied, disclosed to, retained or used by, any other party. If you are not an intended recipient then please promptly delete this e-mail and any attachment and all copies and inform the sender. Thank you.

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

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

    --
    http://www.freelists.org/webpage/oracle-l
  • Andrew Kerber at Nov 17, 2008 at 3:28 pm
    Keep in mind that the 'expensive' operation with regard to redo is the file
    open/close and the physical switch to the next log. So, the key to minimize
    performance impact is to set the size of the redo log large enough that you
    are not continually switching logs. Oracle recommends a log switch every 15
    minutes to half hour. So, you can use very large archive logs to handle
    high change time periods, and the archive_lag_target setting to make sure
    you are switching often enough during low activity time periods.
    On Mon, Nov 17, 2008 at 9:18 AM, Renato M wrote:

    Hi, list.

    I'm with the same doubt...
    We have a Data Warehouse database in "no archivelog" mode with 180GB/day of
    redo generation.

    because of a new backup solution/ strategie, we need put it in "archivelog
    ON".

    Claudia,
    Have you any other information about it ?

    Thanks a lot!

    Renato Gomes
    Eds, an HP Company.



    On Mon, Nov 17, 2008 at 9:09 AM, Roberts, David (GSD - UK) <
    david.h.roberts_at_logica.com> wrote:
    1. Performance cost of running in archivelog mode.

    We take our database out of archive log mode for a yearly batch process.
    Very roughly the dry run (which is run on a different but similarly
    specked machine in archive log mode) takes about 20% longer.

    Obviously as the difference is going to be dependent on how much change
    is going on, your mileage may vary.


    David Roberts
    www.logica.com

    Logica UK Limited
    Registered in England and Wales (registered number 947968)
    Registered office: Stephenson House, 75 Hampstead Road, London NW1 2PL,
    United Kingdom

    -----Original Message-----
    From: oracle-l-bounce_at_freelists.org
    On Behalf Of Claudia Zeiler
    Sent: 15 November 2008 21:27
    To: oracle-l_at_freelists.org
    Subject: performance impact of archivelog

    Has anyone on the list seen a discussion of

    1. performance cost of running in archivelog mode.
    2. overhead of using partitions
    3. time calculation to perform statistics
    4. time savings of using statistics

    I always see these things discussed in terms of "more" but never in
    terms of "how much".

    Thanks for any information that you can point me to.


    Claudia Zeiler

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




    This e-mail and any attachment is for authorised use by the intended
    recipient(s) only. It may contain proprietary material, confidential
    information and/or be subject to legal privilege. It should not be copied,
    disclosed to, retained or used by, any other party. If you are not an
    intended recipient then please promptly delete this e-mail and any
    attachment and all copies and inform the sender. Thank you.


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

    --
    Andrew W. Kerber

    'If at first you dont succeed, dont take up skydiving.'

    --
    http://www.freelists.org/webpage/oracle-l
  • David Ballester at Nov 17, 2008 at 3:54 pm

    El lun, 17-11-2008 a las 13:18 -0200, Renato M escribió:
    Hi, list.

    I'm with the same doubt...
    We have a Data Warehouse database in "no archivelog" mode with
    180GB/day of redo generation.

    because of a new backup solution/ strategie, we need put it in
    "archivelog ON".

    Claudia,
    Have you any other information about it ?

    Thanks a lot!

    Renato Gomes
    Eds, an HP Company.
    180GB / day?

    Are you logging all your datawarehouse feeding?

    IMMO you need to verify if your loads are doing with direct path or
    not.

    The data load should be done with direct path ( sqlldr direct=y,
    Insert /* +APPEND */ ... )

    Make extend use of partitioning and exchange_partition ( local indexes
    if any, etc...)

    Forget the archiving, use datapump or export direct to make
    "backups" ( note the quotes )

    May be you must point your backup/restore strategies on the points where
    you extract data from ( have a way to regenerate data ( previous exports
    + regeneration on the data source, for example... ) to load in the
    datawarehouse if things goes bad(tm) )

    my 2 cents

    D.
  • Greg Rahn at Nov 17, 2008 at 10:44 pm

    On Mon, Nov 17, 2008 at 7:54 AM, David Ballester wrote:
    IMMO you need to verify if your loads are doing with direct path or
    not.

    The data load should be done with direct path ( sqlldr direct=y,
    Insert /* +APPEND */ ... )
    I second this point. Data is most efficiently and most speedily
    inserted via direct path (for warehouses). This does effect
    recoverability, but what is faster? Reloading via direct path again,
    or rolling forward from a backup? In almost all cases a restore +
    reload will be faster than a restore + roll forward recover. Of
    course, each has their own set of business criteria to satisfy.

    And I personally would never run a production database in noarchive
    log mode. Never.
  • David Ballester at Nov 18, 2008 at 9:57 am

    El lun, 17-11-2008 a las 14:44 -0800, Greg Rahn escribió:

    And I personally would never run a production database in noarchive
    log mode. Never.
    Hi Greg:

    I can understand your opinion about the archive log mode on production
    databases, but in very special situations - for example a datawarehouse
    with 20 TB of data aprox. renewing a lot of data each hour and 24X7, is
    very difficult to maintain a backup in the Oracle standard mode ( hot
    backup with rman with archive log mode on ). No window to backup all
    data, the nologging inserts making a lot of unrecoverable points... we
    are talking about tablespaces of 360GB, who can backup it at reasonable
    speed? - I think that in very special cases - other example that comes
    to my mind, a instance used as application cache or very volatile data -
    the database could be in noarchivelog mode but after a carefully study,
    of course. I'm with you, but I say 'For the 99,9% of production
    databases I would never run it in noarchive log mode' :)

    Best regards

    D.
  • Michael Fontana at Nov 18, 2008 at 3:25 pm
    David,

    This is a very interesting thread. In my last shop, I was of this very
    opinion - Why backup a DW that is incredibly large with innumerable
    unrecoverable points, and where many objects are completely refreshed in
    relatively short periods of time?

    I was outvoted by the same rationale you see here. Some points for the
    archivelogging side of things:

    Certain recovery situations might be more transparent;
    Recovery from a disaster might be more straightforward.

    On the other side:

    Can you afford to have an DW down while it's rebuilt?

    With a great deal of nonrecoverable points, wouldn't much it have to be
    rebuilt logically even in archivelog mode?

    Are these business or technical issues?

    While I think the answer SHOULD be techical (if it's a traditional DW
    model), I've seen businesses violate the model and use the DW for
    nonconventional queries (such as online order history, etc).

    Interesting topic for list discussion.

    -----Original Message-----
    From: oracle-l-bounce_at_freelists.org
    On Behalf Of David Ballester
    Sent: Tuesday, November 18, 2008 3:58 AM
    To: Greg Rahn
    Cc: renatomelogomes_at_gmail.com; david.h.roberts_at_logica.com;
    czeiler_at_ecwise.com; oracle-l_at_freelists.org
    Subject: Re: performance impact of archivelog

    El lun, 17-11-2008 a las 14:44 -0800, Greg Rahn escribió:
    And I personally would never run a production database in noarchive
    log mode. Never.
    Hi Greg:

    I can understand your opinion about the archive log mode on production
    databases, but in very special situations - for example a datawarehouse
    with 20 TB of data aprox. renewing a lot of data each hour and 24X7, is
    very difficult to maintain a backup in the Oracle standard mode ( hot
    backup with rman with archive log mode on ). No window to backup all
    data, the nologging inserts making a lot of unrecoverable points... we
    are talking about tablespaces of 360GB, who can backup it at reasonable
    speed? - I think that in very special cases - other example that comes
    to my mind, a instance used as application cache or very volatile data -
    the database could be in noarchivelog mode but after a carefully study,
    of course. I'm with you, but I say 'For the 99,9% of production
    databases I would never run it in noarchive log mode' :)

    Best regards

    D.
  • Tim Gorman at Nov 18, 2008 at 3:27 pm
    <!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">

    There shouldn't be any mystery about choosing "to be ARCHIVELOG, or not
    to be ARCHIVELOG".
    If the database can be rebuilt and reloaded within a time period and to
    a data state acceptable to its user community, then NOARCHIVELOG mode
    is OK.  However, this isn't some kind of "free pass" for DBAs.ÂÂ
    Ethically and practically, they have to *prove* themselves able to
    rebuild and reload to the desired point-in-time within the specified
    amount of time, and that just ain't easy -- it takes more preparation,
    practice, and work to rebuild/reload than it takes to restore/recover.ÂÂ
    Rather the contrary -- the procedures for rebuild/reload have to be
    designed and built from scratch, tested, and maintained over time; the
    procedures for restore/recover are standard and largely automated
    already.
    Besides the level of effort attending rebuild/reload, the important
    point to note is that the decision to rebuild/reload implies that the
    database in question is not the "source of record" for the data it
    contains.  This is why it is usually only an option for data warehouse
    databases, and almost never an option for operational environments.
    So, if a database contains data that can't be sourced anywhere, or
    can't be derived from data available anywhere else, then it is a
    "source of record".  This is not a static designation -- a kind of
    "scope creep" can occur within data warehouses previously considered
    rebuildable and reloadable.  Progressive changes to data processing
    within a DW might cause new data to appear, suddenly making the data
    warehouse database a "source of record" in its own right.

    Back in the 1990s, I worked on a couple data warehouses that were
    either too large for the available storage infrastructure to backup, or
    too busy for any tape subsystem to keep up with.  In particular, I
    recall one data warehouse on a 4-node OPS cluster based on HP-UX which
    generated anywhere from 2-3 Tb of archived redo log files per day, from
    a database that was about 6 Tb of datafiles.  The workload of 2-3 Tb of
    redo logs throughout the day, plus datafile backups, plus the other
    systems using the not-inconsiderable backup infrastructure, was
    overwhelming.  So, that monster ran in NOARCHIVELOG mode because there
    wasn't any choice, giving all of its DBAs premature gray hair and
    baldness (even the women).  I know that it too contained data that was
    considered "source of record", because I remember them having to pull a
    rabbit out of their .... ummm ... hat ... when a "power-user" left the
    company and had his account subsequently (and routinely) removed.ÂÂ
    Suddenly, the rest of the company valued the data in the deleted
    account at some obscene dollar figure worth more than the IT personnel
    and data centers put together.  I suspect that the DBAs on that DW have
    recognized themselves and the events in question, and I'll soon be
    getting back in touch with them -- at least I hope so!

    Anyway, if a database is a "source of record" for any data, then it is
    almost certainly a requirement that it be recovered to the
    point-in-time of any failure, and that requires ARCHIVELOG mode, which
    is really a lot less effort to manage (than rebuild/reload) anyway.
    Hope this helps...
    Tim Gorman
    consultant - Evergreen Database Technologies, Inc.
    P.O. Box 630791, Highlands Ranch CO 80163-0791
    website = http://www.EvDBT.com/
    email = Tim@EvDBT.com
    mobile = +1-303-885-4526
    fax = +1-303-484-3608
    Yahoo IM = tim_evdbt

    David Ballester wrote:

    El lun, 17-11-2008 a las 14:44 -0800, Greg Rahn escribió:

    And I personally would never run a production database in noarchive
    log mode. Never.

    Hi Greg:

    I can understand your opinion about the archive log mode on production
    databases, but in very special situations - for example a datawarehouse
    with 20 TB of data aprox. renewing a lot of data each hour and 24X7, is
    very difficult to maintain a backup in the Oracle standard mode ( hot
    backup with rman with archive log mode on ). No window to backup all
    data, the nologging inserts making a lot of unrecoverable points... we
    are talking about tablespaces of 360GB, who can backup it at reasonable
    speed? - I think that in very special cases - other example that comes
    to my mind, a instance used as application cache or very volatile data -
    the database could be in noarchivelog mode but after a carefully study,
    of course. I'm with you, but I say 'For the 99,9% of production
    databases I would never run it in noarchive log mode' :)

    Best regards

    D.
  • Mark W. Farnham at Nov 18, 2008 at 5:43 pm
    1)

    Have you considered using transportable tablespaces to plug in the large
    data changes so that block deltas on the precious whole can be archived and
    therefore useful with all manner of block repair? As the size of your
    database grows the likelihood of all variety of seemingly low probability
    block corruptions increases. Depending on the texture of your ETL this
    process *may* allow for horizontal scaling, secure recovery at every
    appropriate level, and quite possibly the widest window for analysis queries
    that have less contention for UNDO.

    2)

    I have seen a rock solid case for noarchiving; Clay Jackson of USWNV (a
    member of the 1990's Oracle VLDB) had data that was persistent on a switch
    for something like two days. With archiving it was touch and go whether he
    could keep up. With archiving off, he could load it something like 3 or 4
    times a day. So he used a data capture database that was most certainly part
    of the production flow, but which was also only a way-station. If we needed
    to reload it due to a failure getting it through the "pipe" to more
    permanent and secure storage, he did just that. I guess the quibble might be
    whether that was a "production database" whether or not the contents that
    flowed through it was essential.

    3) Never is a high standard. But I tend to agree with Greg and therefore I'd
    have to characterize the things and situations where I consider noarchive
    the best solution to either not be databases or not be production...

    Regards,

    mwf

    -----Original Message-----
    From: oracle-l-bounce_at_freelists.org
    On Behalf Of David Ballester
    Sent: Tuesday, November 18, 2008 4:58 AM
    To: Greg Rahn
    Cc: renatomelogomes_at_gmail.com; david.h.roberts_at_logica.com;
    czeiler_at_ecwise.com; oracle-l_at_freelists.org
    Subject: Re: performance impact of archivelog

    El lun, 17-11-2008 a las 14:44 -0800, Greg Rahn escribió:
    And I personally would never run a production database in noarchive
    log mode. Never.
    Hi Greg:

    I can understand your opinion about the archive log mode on production
    databases, but in very special situations - for example a datawarehouse
    with 20 TB of data aprox. renewing a lot of data each hour and 24X7, is
    very difficult to maintain a backup in the Oracle standard mode ( hot
    backup with rman with archive log mode on ). No window to backup all
    data, the nologging inserts making a lot of unrecoverable points... we
    are talking about tablespaces of 360GB, who can backup it at reasonable
    speed? - I think that in very special cases - other example that comes
    to my mind, a instance used as application cache or very volatile data -
    the database could be in noarchivelog mode but after a carefully study,
    of course. I'm with you, but I say 'For the 99,9% of production
    databases I would never run it in noarchive log mode' :)

    Best regards

    D.
  • Flado at Nov 18, 2008 at 2:05 pm
    Here's a discussion on archivelog vs. noarchivelog mode:
    http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:421219463156

    It is quite long and somewhat noisy - but I just read most of it so let me
    summarize:

    A big (negative) performance impact of switching to archivelog mode is
    likely to come from the sudden importance of the NOLOGGING attribute.
    If you:
    a) do lots of direct-path inserts,
    AND

    b) your segments are LOGGING
    Then switching to archivelog mode will cause a huge increase in redo
    generation: basically, your direct-path inserts will now be fully logged
    (where they previously weren't).

    So if you do not have this setup, and you have taken measures to prevent the
    obvious problems (waits for log buffer space, log file switch completion
    (archiving needed), and so on) you should not notice the transition.
    I always see these things discussed in terms of "more" but never in terms
    of "how much".
    What do you expect to see? The performance impact of archivelog varies,
    depending on too many things, from 0 (in most cases - see above) to infinity
    (when your archive log destination fills up). That's why the only true
    statement one can make about it - aside from "it depends(tm)" - is "possibly
    more".

    Cheers!

    Flado

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouporacle-l @
categoriesoracle
postedNov 15, '08 at 9:26p
activeNov 18, '08 at 5:43p
posts14
users12
websiteoracle.com

People

Translate

site design / logo © 2022 Grokbase