This discussion is archived
6 Replies Latest reply: Mar 1, 2009 1:54 PM by Randolf Geist RSS

CKPT Questions

623520 Newbie
Currently Being Moderated
Hello,

In one of my 10g databases, I found that CKPT is happening frequently which causes sessions locks.
Where can I see how frequent CKPT is happening and how can I solve the frequent locking issue.

Thanks in advance
  • 1. Re: CKPT Questions
    asifkabirdba Guru
    Currently Being Moderated
    Every log switch causes a CKPT. Check your log switch frequency per hour. To reduce log switch frequency increase the size of your redo log files. Also check the alert log for any incomplete checkpoint. You can analyze the AWR report for any background wait events that can slow the database activity.

    Regards
    Asif Kabir
  • 2. Re: CKPT Questions
    Aman.... Oracle ACE
    Currently Being Moderated
    user620517 wrote:
    Hello,

    In one of my 10g databases, I found that CKPT is happening frequently which causes sessions locks.
    How did you conclude this that the sessions are being locked due to the chckpointing only? This should be worth to know as in general, checkpoints have nothing to do with the sessions and their works and this event doesn't cause sessions to be locked. What's the lock mode shown for the sessions?
    Where can I see how frequent CKPT is happening and how can I solve the frequent locking issue.
    You can check the switch happening from the alert log where the log switches and if there is any, incomplete checkpoints will be shown. In order to correct this, normally, the size of the redo log files should be changed to somewhat bigger. Otherwise, if the size is already sufficient, you may want to control the switching rate with the help of archive_lag_target parameter.

    For how to cnotrol the locking issue, its important to check that whether the sessions aer being locked truly due to this. I would recommend that you use Tanel's , session snapper to check for what sessions aerbeing locked.
    http://blog.tanelpoder.com/2007/12/06/oracle-session-snapper-v106-released/

    HTH
    Aman....
  • 3. Re: CKPT Questions
    hans forbrich Oracle ACE Director
    Currently Being Moderated
    user620517 wrote:
    Hello,

    In one of my 10g databases, I found that CKPT is happening frequently which causes sessions locks.
    Where can I see how frequent CKPT is happening and how can I solve the frequent locking issue.
    Just to make absolutely sure -

    When you say locks, are you looking at an output that tells you that some database resource is locked?

    Or are you saying the sessions are hanging and appear to be stopped for a brief time which you notice occurs at the same time as checkpoint is happening?
  • 4. Re: CKPT Questions
    623520 Newbie
    Currently Being Moderated
    Gentlement,

    I just want to clarify. When I opened OEM I found the following

    -> Blocking _______________Locks sessions ____________blocked Mode ____________Held Mode Requested
    -------> CKPT _________________1 ________________________ROW CHARE ________________None
    ---------------->myschema1 _______0 ________________________SHARE ROW EXCLUSIVE _____EXCLUSIVE




    Does this mean CKPT is blocking myschema1 or myschema1 is blocking CKPT??
  • 5. Re: CKPT Questions
    Aman.... Oracle ACE
    Currently Being Moderated
    Well, looking at this , it does show that the CKPT is blocking myschema1. But is myschema is experiencing any kind of issues in its working due to this?

    And just a thought, have you enabled direct sga attach or in-memory access mode in your em?
    HTH
    Aman....
  • 6. Re: CKPT Questions
    Randolf Geist Oracle ACE Director
    Currently Being Moderated
    user620517 wrote:
    I just want to clarify. When I opened OEM I found the following

    -> Blocking _______________Locks sessions ____________blocked Mode ____________Held Mode Requested
    -------> CKPT _________________1 ________________________ROW CHARE ________________None
    ---------------->myschema1 _______0 ________________________SHARE ROW EXCLUSIVE _____EXCLUSIVE
    Just to hazard a guess: I recently came across a situation where in 10.2.0.4 a session performing a "TRUNCATE PARTITION" was blocked by the CKPT process.

    The session is waiting for "enq: RO - fast object reuse" (e.g. V$SESSION_WAIT).

    This behaviour is basically normal and is caused by the fact that the CKPT process waits for the DBWR to write any dirty blocks to disk of the object to be truncated/dropped.

    Metalink Note 286363.1 describes the general processing in that case. It could also be a DROP operation.

    If the session however is blocked much longer than expected, it might be the issue described in MetaLink note 5177241.8, bug 5177241. The temporary workaround is to set "_db_fast_obj_truncate"=FALSE in that particular case.

    There is another known bug in that context: Metalink note 785232.1, bug 7287256, base bug 7385253. In this case the DBWR is consuming 100% CPU.

    Regards,
    Randolf

    Oracle related stuff blog:
    http://oracle-randolf.blogspot.com/

    SQLTools++ for Oracle (Open source Oracle GUI for Windows):
    http://www.sqltools-plusplus.org:7676/
    http://sourceforge.net/projects/sqlt-pp/

Legend

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