Excess archvie log generation
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 11

Thread: Excess archvie log generation

  1. #1
    Join Date
    Mar 2006
    Location
    Charlotte, NC
    Posts
    865

    Excess archvie log generation

    Hi,

    I have observed a weird behaviour of archiver. It is generating 13 archive logs every minute.

    log_buffer is 4MB
    archive log size is 50MB
    Oracle 10.2.0.4 on Windowz

    Can some one hint me is there any init parameter controls the log switch behaviour? This time i am posting here first before googling ...

    Code:
    name				thread#       seq#  first_time
    ARC02566_0686992715.001                1       2566 12-jun-09 09:33:00
    ARC02567_0686992715.001                1       2567 12-jun-09 09:33:05
    ARC02568_0686992715.001                1       2568 12-jun-09 09:33:08
    ARC02569_0686992715.001                1       2569 12-jun-09 09:33:12
    ARC02570_0686992715.001                1       2570 12-jun-09 09:33:20
    ARC02571_0686992715.001                1       2571 12-jun-09 09:33:27
    ARC02572_0686992715.001                1       2572 12-jun-09 09:33:31
    ARC02573_0686992715.001                1       2573 12-jun-09 09:33:48
    ARC02574_0686992715.001                1       2574 12-jun-09 09:33:50
    ARC02575_0686992715.001                1       2575 12-jun-09 09:33:52
    ARC02576_0686992715.001                1       2576 12-jun-09 09:33:54
    ARC02577_0686992715.001                1       2577 12-jun-09 09:33:56
    ARC02578_0686992715.001                1       2578 12-jun-09 09:33:58
    ARC02579_0686992715.001                1       2579 12-jun-09 09:34:00
    ARC02580_0686992715.001                1       2580 12-jun-09 09:34:02
    ARC02581_0686992715.001                1       2581 12-jun-09 09:34:05
    ARC02582_0686992715.001                1       2582 12-jun-09 09:34:07
    ARC02583_0686992715.001                1       2583 12-jun-09 09:34:09
    ARC02584_0686992715.001                1       2584 12-jun-09 09:34:11
    ARC02585_0686992715.001                1       2585 12-jun-09 09:34:14
    ARC02586_0686992715.001                1       2586 12-jun-09 09:34:16
    ARC02587_0686992715.001                1       2587 12-jun-09 09:34:19
    ARC02588_0686992715.001                1       2588 12-jun-09 09:34:21
    ARC02589_0686992715.001                1       2589 12-jun-09 09:34:24
    ARC02590_0686992715.001                1       2590 12-jun-09 09:34:26
    ARC02591_0686992715.001                1       2591 12-jun-09 09:34:29
    Thanks,
    Vijay Tummala

    Try hard to get what you like OR you will be forced to like what you get.

  2. #2
    Join Date
    Mar 2006
    Location
    Charlotte, NC
    Posts
    865
    I have gone thru the alert log and find frequent "checkpoint not complete" messages. This is because of under sizing the redo log files. I am planning to increase (create new groups with new size and drop the old groups when they are inactive) the redo log size from 50M to 500M.

    Some one please let me know if i am going in wrong direction.

    Thanks,
    Vijay Tummala

    Try hard to get what you like OR you will be forced to like what you get.

  3. #3
    Join Date
    Apr 2008
    Location
    Bangalore
    Posts
    96
    Just a suggestion...

    The ideal size of redo should be something which will result in log switch for 20 minutes.


    if you set fast_mttr_target parameter then you can find the optimal log file size column being populated in v$instance_recovery view.


    Please let me know if you need any clarifications.


    Cheers!

    Mohith

  4. #4
    Join Date
    Mar 2006
    Location
    Charlotte, NC
    Posts
    865
    Hi Mohit,

    Thanks for your suggestion. In my DB fast_start_mttr_target is set to 0 so v$instance_recovery view is not populating the optimal_logsize. In this case how Oracle will deside the MTTR?

    However, I have created new log groups with 200MB and dropped the old log groups. Now, I am working on to reduce the redo generating during SQL Loader run.

    App team is loading data into some 15 tables using SQL Loader using convensional path. Explicitely i put those 15 tables and the corresponding indexes in nologging mode but seems not much useful.

    If I ask the app team to use DIRECT=TRUE and UNRECOVERABLE options will that be helpful to reduce the redo generating on those tables? The data they are loading is transient data and not needed for recovery.

    Please suggest.

    Thanks,
    Vijay Tummala

    Try hard to get what you like OR you will be forced to like what you get.

  5. #5
    Join Date
    Nov 2002
    Location
    Mooresville, NC
    Posts
    349
    Quote Originally Posted by vnktummala View Post
    Hi Mohit,

    Thanks for your suggestion. In my DB fast_start_mttr_target is set to 0 so v$instance_recovery view is not populating the optimal_logsize. In this case how Oracle will deside the MTTR?

    However, I have created new log groups with 200MB and dropped the old log groups. Now, I am working on to reduce the redo generating during SQL Loader run.

    App team is loading data into some 15 tables using SQL Loader using convensional path. Explicitely i put those 15 tables and the corresponding indexes in nologging mode but seems not much useful.

    If I ask the app team to use DIRECT=TRUE and UNRECOVERABLE options will that be helpful to reduce the redo generating on those tables? The data they are loading is transient data and not needed for recovery.

    Please suggest.

    Thanks,
    If sqlldr is the issue why do not you tell them to use direct path load which will by pass redo log?
    http://www.perf-engg.com
    A performance engineering forum

  6. #6
    Join Date
    Mar 2006
    Location
    Charlotte, NC
    Posts
    865
    Hi Malay,

    Thanks for your reply.

    I have that option in mind but my concern is about the constraints. They have PK, Not Null, Check, and RI constraints enabled on the tables. Check constraints and RI constraints are NOT evaluated during direct path load. However, I can over come that using EVALUATE CHECK_CONSTRAINTS REENABLE option in SQL Loader.

    I am doing R&D on constraints and indexes in dev before implementing it in prod.

    Thanks,
    Vijay Tummala

    Try hard to get what you like OR you will be forced to like what you get.

  7. #7
    Join Date
    Nov 2002
    Location
    Mooresville, NC
    Posts
    349
    Quote Originally Posted by vnktummala View Post
    Hi Malay,

    Thanks for your reply.

    I have that option in mind but my concern is about the constraints. They have PK, Not Null, Check, and RI constraints enabled on the tables. Check constraints and RI constraints are NOT evaluated during direct path load. However, I can over come that using EVALUATE CHECK_CONSTRAINTS REENABLE option in SQL Loader.

    I am doing R&D on constraints and indexes in dev before implementing it in prod.

    Thanks,
    Let us know how did it go?
    http://www.perf-engg.com
    A performance engineering forum

  8. #8
    Join Date
    Mar 2006
    Location
    Charlotte, NC
    Posts
    865
    Hi Malay,

    New control file creation script (with direct=true, unrecoverable, and evaluate check_constraints reenable options) migrated to production. The job runs at 2am Fiji time. I will keep you post the result.

    Thanks,
    Vijay Tummala

    Try hard to get what you like OR you will be forced to like what you get.

  9. #9
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    4,026
    This is a question of doing basic tuning. And the answer really depends on what you need. There is not magic, just make the log switches happen every 20 minutes. Are you completely rebuild all of the tables every day at a time when no one is logged in or otherwise using the system? Then try dropping the indexes before the load and recreate them after the loading process is finished. If you want to minimize redo, then make sure that all of the tables and all of the indexes are set to no logging.

    If you don't need to be in archivelog mode you might benefit from taking it out of archivelog mode, although that makes point in time recovery impossible.
    this space intentionally left blank

  10. #10
    Join Date
    Mar 2006
    Location
    Charlotte, NC
    Posts
    865
    Hi Gandolf,

    Thanks for your suggestions.

    I have 3 log groups with 500MB each. I am sure this is quite a big size for my small database (50GB). During normal business hours there is hardly any activity on this DB but when the sql loader starts after mid night the 500 MB logs are switching once in 10 minutes.

    Application team want constraints (pk, uk, nn, and ri) inforce on the tables before truncate and load the data every day. that's the way they filter the duplicate records or the records with null values.

    I have put the correspoiding tables and indexes in nologging last week but not seen big difference in archive generation (using convensional path loading).

    The database should be in archivelog mode. But the data loading using sql loader is transient and not required in case of recovery. So we have desided to go with direct path load with evaluate check_constraints reenable and unrecoverable options.

    Thanks,
    Vijay Tummala

    Try hard to get what you like OR you will be forced to like what you get.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width