-
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
-
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
-
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
