1 2 Previous Next 19 Replies Latest reply: Aug 18, 2010 7:18 AM by 695836 Go to original post RSS
      • 15. Re: log file switch (checkpoint not complete)

        Shared Pool Statistics        Begin   End
        ------  ------
        Memory Usage %:   93.66   93.74
        +% SQL with executions>1: 60.41 60.94+
        +% Memory for SQL w/exec>1: 60.89 61.66+
        Top 5 Timed Events
        +~~~~~~~~~~~~~~~~~~ % Total+
        Event                                               Waits    Time (s) Ela Time
        -------------------------------------------- ------------ ----------- --------
        log file switch (checkpoint incomplete)            35,936      35,100    42.81
        enqueue                                             6,144      16,684    20.35
        buffer busy waits                                  17,190      13,346    16.28

        1. For "log file switch (checkpoint incomplete)" Increase the redo group from 2. Add more redo log group in your database.
        2. It seems that shared pool is highly utilized so you need to increase shared pool size from 1,312M.
        3. For high enqueue and buffer busy wait take snapshots of level 7 for segment statistics and then generate stats pack for level 7 snapshots. And paste the segment statistics portion here.

        Rajinder Kumar
        • 16. Re: log file switch (checkpoint not complete)

          we are already haing 3 loggroups of 100mb each logfile and even after taking statspack report i found that logfile size is quite high, we can easliy crosscheck with user call as compared to commits per sec.

          So do uthink we should add more redo loggroups, i thin this is the temp. solution.Because users are crying only once in ever 3-4 days
          • 17. Re: log file switch (checkpoint not complete)
            Jonathan Lewis
            user13389425 wrote:
            Please find the whole stats[pack report
            Not quite - you gave us lots of stuff I didn't want (yet) and none of the stuff I did ask for.However: first approximation:<blockquote>Your log file write times are very slow - and you've got three log files per log file group (why?)The log file sizes aren't (generally) a problem, but the multiplexing is slowing you down a bit.The sample of alert log you showed us, though, suggests you have moments of extreme processing which flood the log files - so bigger log files and more log files might help to smooth out the disaster caused by the spikes.</blockquote>Perhaps more significantly, you have three archive locations - one local and two apparently remote. Most of the time reported suggests that your main problem lies in clearly the online logs to the remote locations. (Again, this may only be occuring when the workload spikes, it may be when something outside the database causes the target machines to get overloaded.)When a checkpoint can't complete the database can't make any more changes - which can drive up lots of other wait figures. Failure to complete checkpoints tends to occur nowadays because all the online logs are full and the first online log can't be reused because the archiver is still archiving it.  Again, you may be able to limit the damage due to workload spikes by making much more online log space available.Check your log file destination, archiver configurations, network capacity, and the state of the remote servers.RegardsJonathan Lewis                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   
            • 18. Re: log file switch (checkpoint not complete)
              Jonathan Lewis Sir:

              Thanks for your reply

              If we increase the logfile then in case of failure it is difficult for us to recover the db, pls correct me if I am wrong.Three logfile per loggroup is because just for the sake of backup and recovery(muliplexing), should we need to cut down the number of logfile per grouup.an another question is how much redo gorups one must be having.

              We are also getting one more wait event "ARCH wait on SENDREQ".This event is gernating because of two drsites and right now we are haing 3 arch processes.

              -bash-3.00$ ps -ef|grep arc
              oracle 2123 29183 0 16:07 pts/4 00:00:00 grep arc
              oracle 2285 1 0 Aug16 ? 00:00:04 ora_arc0_icai
              oracle 2744 1 0 Aug16 ? 00:00:00 ora_arc1_icai
              oracle 25317 1 0 Aug15 ? 00:00:00 ora_arc3_icai

              waht arch is doing , i had put a strace on ARCH
              -bash-3.00$ cat out.txt
              rt_sigprocmask(SIG_BLOCK, [ALRM], NULL, 8) = 0
              setitimer(ITIMER_REAL, {it_interval={0, 0}, it_value={0, 0}}, NULL) = 0
              rt_sigprocmask(SIG_UNBLOCK, [ALRM], NULL, 8) = 0
              rt_sigprocmask(SIG_BLOCK, NULL, [], 8)  = 0
              rt_sigprocmask(SIG_BLOCK, [ALRM], NULL, 8) = 0
              setitimer(ITIMER_REAL, {it_interval={0, 0}, it_value={32, 250000}}, NULL) = 0
              rt_sigprocmask(SIG_UNBLOCK, [ALRM], NULL, 8) = 0
              semop(950274, 0x7fbfffe7b0, 1 <unfinished ...>
              • 19. Re: log file switch (checkpoint not complete)
                To get rid of this error,you have two options:
                1)Either add more DBWR processes
                2)Add more redo log files.

                So that DBWR has enough time to catch up with LGWR.

                If you have set FAST_START_MTTR_TARGET,you can use log advisor to give optimal redo log size:

                SQL> select optimal_logfile_size from v$instance_recovery;

                Edited by: Hashmi on Aug 18, 2010 5:16 AM
                1 2 Previous Next