This discussion is archived
1 2 Previous Next 26 Replies Latest reply: Apr 4, 2013 1:48 AM by moslee RSS

Achivelog Mode Performance

moslee Newbie
Currently Being Moderated
Hi to all

My Production DB Specs
---------------------------------
Oracle 11g Enterprise Edition (11.2.0.3)
Windows Server 2012 Standard (64 bit)
HP ProLiant DL360p Gen8
Processors Intel Xeon CPU E5-2620 0 @2GHz
16GB RAM
Total disk space 745GB
Database size 74GB

I would like to enable the Archivelog mode in my db. I understand the importance of having archive logs. However as the nature of every db is different, my concern is:

1] What are those configurations that I need to test first BEFORE implementing archivelog mode? (redo log settings, transactions used per day etc.?)
2] How can I reduce the impact on my running production database performance as much as possible, when I implement archivelog mode?
3] What are the things that I need to check and monitor AFTER implementing archivelog mode? (for example, Archivelog disk spaces?)


Thank you for sharing with me your precious experiences.

Edited by: moslee on Mar 27, 2013 11:53 PM
  • 1. Re: Achivelog Mode Performance
    Hemant K Chitale Oracle ACE
    Currently Being Moderated
    You don't really need to "configure" anything. However, you must monitor

    "redo size" statistic --- you can get this from StatsPack reports. This will indicate the volume of ArchiveLogs that will be generated.

    I/O speed of target archivelog volume/filesystem --- slow I/O means that the Archiver will not be fast enough and may cause LGWR to wait on ARCH, resulting in occassional "freezing" of database transaction



    Hemant K Chitale
  • 2. Re: Achivelog Mode Performance
    RobertGeier Oracle ACE
    Currently Being Moderated
    Before turning on archivelog you need to :-

    - check what volume of log you write each day, and if there are peak periods (batches etc)
    - check for log switch interval and consider if the log size is optimal
    - define what your archivelog backup and delete policy will be (now long to keep on disk, how often to backup, how many backups to take before deleting)
    - write and test your backup / delete scripts (rman ?)
    - check that you have sufficient disk to handle unexpected events
    - check that you have monitoring enabled to detect disk filling up
    - consider using FRA instead of archiving to a directory
    - consider whether to archive locally, or to a remote server or NFS share
    - check for objects or transactions with nologging (select force_logging from v$database)
    - consider if you will duplex archivelogs or not
    - consider how many archiver processes you will need (log_archive_max_processes)
    - consider what you want to happen if archival fails (log_archive_min_succeed_dest)

    The ARCH processes only copy files, so impact on performance can easily be simulated, and will depend on the volume of files being copied, and number of parallel copies.
    Archivelogs are useless unless you have a database backup, so you need to include that in your scripting and testing, along with restore tests.
  • 3. Re: Achivelog Mode Performance
    RobertGeier Oracle ACE
    Currently Being Moderated
    Also check the number of redo log groups are sufficient. You don't want the database to hang waiting for archival.
  • 4. Re: Achivelog Mode Performance
    moslee Newbie
    Currently Being Moderated
    Hi guys

    Many thanks for those advices. I'm amazed that there are so many things to check BEFORE implementing archivelog mode alone, by not checking these things will cause a huge impact on the db.

    By asking these questions, you can tell that I am a newbie here. I will definitely come back to you guys again when I have more questions. Will try to check on my own first. Stay tune and thanks.
  • 5. Re: Achivelog Mode Performance
    moslee Newbie
    Currently Being Moderated
    Hi Robert

    To check what volume of log my DB write each day, can I use these 2 queries below to have a rough idea? My small size db (40GB) has a mix of OLTP and OLAP.

    select value/up_days as tx_per_day
    2 from (select sum(value) as value from v$sysstat
    3 where name in ('user commits','user rollbacks')),
    4 (select sysdate-startup_time as up_days from v$instance);

    select value/up_days as calls_per_day
    2 from (select value from v$sysstat where name='user calls'),
    3 (select sysdate-startup_time as up_days from v$instance);

    Thanks.
  • 6. Re: Achivelog Mode Performance
    Hemant K Chitale Oracle ACE
    Currently Being Moderated
    'user commits' and 'user rollbacks' will not show you the redo volume. One transaction may commit 1 row update, another transaction may commit a 1000 row delete. The redo generation by the latter transaction will be much more than the former.

    You should query v$sysstat 'redo size' (which is expressed in bytes).
    Note : Particularly with 32bit implementations, you would find the statistics wrap around after 4billion (which isn't very large for 'redo size'). So querying across 'n' days would not be useful. You could query every hour and find the incremental value. OR you could use StatsPack which will include redo size as one of the elements it reports.


    Hemant K Chitale
  • 7. Re: Achivelog Mode Performance
    moslee Newbie
    Currently Being Moderated
    Hemant

    Should I be using AWR instead and not Statspack? I'm on 11gR2

    Hi to all, kindly advise me on this thread, a newbie in this understanding of database performance... Many thanks..

    Edited by: moslee on Mar 27, 2013 2:21 AM
  • 8. Re: Achivelog Mode Performance
    Hemant K Chitale Oracle ACE
    Currently Being Moderated
    You can use AWR if you have purchased the Diagnostic Pack licence. Alternatively, StatsPack is free.

    Hemant K Chitale
  • 9. Re: Achivelog Mode Performance
    moslee Newbie
    Currently Being Moderated
    Hi Hemant

    Thanks for your sharing.

    The top main reason why I need to ask questions pertaining to Achivelog Mode Performance is because currently this DB (11gR2 64bit) production is on no archivelog mode. I want to move it to Archivelog mode on. But it seems like this is not an easy move. However the consequences of an instance crash or logical error (loss of data) will be too grave if this DB is in no archivelog mode. My management (senior IT staff and boss) are not taking this matter seriously because they claim that ShadowProtect (posted in another thread) is more than enough. However, my responsibility is to start researching, test and implement it to prepare for the worst.

    Edited by: moslee on Mar 27, 2013 6:34 PM
  • 10. Re: Achivelog Mode Performance
    Hemant K Chitale Oracle ACE
    Currently Being Moderated
    Yes, you should go ARCHIVELOG mode and take online database and archivelog backups using RMAN.

    I presume that you are familiar with Backups (Database, Controlfile, ArchiveLogs) and Restore and Recovery (Full and Point-In-Time) operations using RMAN ?

    Also consider FORCE LOGGING.


    See the High Availability Best Practices guide http://docs.oracle.com/cd/E11882_01/server.112/e10803/toc.htm

    Hemant K Chitale
  • 11. Re: Achivelog Mode Performance
    moslee Newbie
    Currently Being Moderated
    Hi Hemant

    Thanks for your assurance.

    Yes, I'm familiar with Rman backups/recovery. That's is why I am so eager to move to Archivelog mode.

    Shall research on those settings stated by Robert now.. Will come back with more questions..
  • 12. Re: Achivelog Mode Performance
    moslee Newbie
    Currently Being Moderated
    Hi Hemant

    I have obtained my AWR report. Is the Redo size (Per Second) under Load Profile the redo volume?

    I have Redo size: 938,530.5 (per second) and 68,392.2 (per transaction)
  • 13. Re: Achivelog Mode Performance
    Hemant K Chitale Oracle ACE
    Currently Being Moderated
    Yes, you should look at 'Redo size (per second)'. You could also scroll down to the statistics section and see 'redo size' there.


    Hemant K Chitale
  • 14. Re: Achivelog Mode Performance
    moslee Newbie
    Currently Being Moderated
    Hi Hemant

    Thanks for your immediate guidance... Now, I have these figures but how can I understand them? Is my redo volume huge? My AWR report is for 19-Mar-13 23:00:18 to 28-Mar-13 10:00:59.

    Redo size:      938,530.5 (per second)
         68,392.2 (per transaction)

    Redo size:      685,917,149,856 (total)
         938,530.45 (per second)
         68,392.22 (per Trans)

    Redo size for direct writes:      132,104 (total)
                   0.18 (per second)
                   0.01 (per Trans)

    Edited by: moslee on Mar 27, 2013 11:59 PM
1 2 Previous Next

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points