DBAsupport.com Forums - Powered by vBulletin
Results 1 to 3 of 3

Thread: Locking by CKPT process

Hybrid View

  1. #1
    Join Date
    Sep 2001
    Location
    Ohio
    Posts
    334
    Hi!
    We just put in a new version of our application this weekend and now we are seeing a lot more waiting on enqueues. When I check to see who the blocker is, it is ALWAYS the CKPT process. We had seen these before, but now there are a lot of them and it's becoming somewhat problematic. When I check to see what "type" of lock it is, it runs the gamut...(TM,CI,TX,TC,IS,DX). Also, when I check to see the breakdown of waits by lock type this is what I get:

    SQL> select ksqsttyp "Lock",
    2 ksqstget "Gets",
    3 ksqstwat "Waits"
    4 from x$ksqst where ksqstwat > 0
    5 /

    Lo Gets Waits
    -- ---------- ----------
    CI 19970441 44
    SS 3 1
    TC 9962386 1060
    TX 14013678 6

    Also, the new application has begun to use DBMS_PIPES. I found several references to an "issue" with CKPT and DBMS_PIPES on metalink, but it referenced a document that has been deleted (as I am told by Oracle Tech). The Oracle tech also told me that there is no information on checkpoint locking available. Helpful, huh!

    Other helpful (possibly) information:
    - We have 5 log groups with 2 members each. Redo Log size is 300 MB.
    - Our log_buffer is 3 Meg.
    - log_checkpoint_interval = 460800
    - log_checkpoint_timeout = 1800
    - Our system is an OLTP system with ~1100-1200 sessions during the day.
    - We use MTS.

    If anybody has any ideas on this, or at least a direction to look, I would be very appreciative!

    Thanks for any help!
    Jodie




  2. #2
    Join Date
    Sep 2001
    Location
    Ohio
    Posts
    334
    Some additional information:

    It appears the app is committing VERY frequently. Could this be causing the locks?:

    SQL> select
    2 sum(decode(statistic#,4,value,0)) commits,
    3 sum(decode(name,'redo blocks written',value,0))/2 kb_written,
    4 sum(decode(name,'redo blocks written',value,0))*.5/sum(decode(statistic#,4,value,0)) AvgKb,
    5 sum(decode(name,'user commits',value/(86400*(sysdate-startup_time)),0)) cps
    6 from v$sysstat s, v$instance i
    7 /


    COMMITS kb_written AvgKbPerCom ComsPerSecond
    ------------ -------------- ------------- -------------
    13,290,883 37,557,589 2.826 62.477


  3. #3
    Join Date
    Sep 2001
    Location
    Ohio
    Posts
    334
    Can anybody out there help with this? Oracle has not provided any information either in our TAR our on their forums.

    Am I the only one that has seen this behavior???

    Thanks!
    Jodie

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