Re: How to TC lock?

yong321_at_yahoo.com (Yong Huang) wrote in message news:<b3cb12d6.0406151120.7bdb2ad1_at_posting.google.com>...
> eygle_at_itpub.net (eygle) wrote in message news:<f9bc34d.0406132254.23e1df88_at_posting.google.com>...
> > SQL> select * from v$lock where type='TC';
> > ADDR KADDR SID TY ID1 ID2 LMODE REQUEST CTIME BLOCK
> > -------- -------- ---- -- ---------- --- ---------- ------- ---------- ----------
> > AB730CA8 AB730CB8 4 TC 65595 0 2 0 157 1
> > AB730B40 AB730B50 4 TC 65599 0 2 0 156 1
> > AB730654 AB730664 4 TC 65550 0 2 0 156 1
...
> > AB730924 AB730934 10 TC 65570 0 5 6 156 0
> > AB72FA60 AB72FA70 17 TC 65590 0 5 6 109 0
> > AB731C20 AB731C30 20 TC 65677 0 5 6 155 0
> > AB730C30 AB730C40 25 TC 65577 0 5 6 156 0
> > AB72F538 AB72F548 35 TC 65595 0 5 6 157 0
> > AB72FA24 AB72FA34 41 TC 65550 0 5 6 156 0
> > AB72F934 AB72F944 88 TC 65583 0 5 6 156 0
> > AB72F4C0 AB72F4D0 94 TC 65578 0 5 6 156 0
> > AB72FD30 AB72FD40 123 TC 65586 0 5 6 205 0
...
> > Some TC lock where block =1 ,how this lock effect database?
>
> I searched and haven't found any definitive answer. TC is usually
> called thread checkpoint. But Metalink Note:267332.1 for 10g locks
> calls it tablespace checkpoint, which is probably more correct. Nobody
> has decoded the ID1 parameter. This lock may have to do with CKPT
> process that got stuck. So check elsewhere for performance problems.
> Some people report TC lock wait on parallel executions and tablespace
> backup mode. I don't know if you have the same issue and whether lock
> mode has to match theirs to say it's the same problem. What if you
> trace the CKPT process with OS tracing tool? Does it show any errors?
> It should report semaphore- and I/O-related syscalls once every 3
> seconds or more often. Usually CKPT has the highest accumulative
> physicall I/O's on a fairly quiet database.
>
> In case you haven't, read Jonathan's messages at
> http://groups.google.com/groups?threadm=1003307326.10536.0.nnrp-07.9e984b29%40news.demon.co.uk
>
> Yong Huang

Just a little more comment. The reason I say TC probably means tablespace checkpoint instead of thread checkpoint is that when you put a tablespace in backup mode or offline it, TC locks are taken; on my 9.0.1.3.1 database, each operation requests and gets TC locks 5 times according to v$enqueue_stat. Checkpointing or switching logfiles does not increment the lock requests. "Thread checkpoint" is nothing but database checkpoint in single instance database and local checkpoint in OPS or RAC. I imagine alter system checkpoint would increment TC requests if TC meant thread checkpoint.

The reason CKPT may be the culprit is that SID 4 is usually CKPT. Your v$lock shows that CKPT is blocking other sessions. So you're seeing a symptom for which a real problem may be somewhere else, such as the I/O subsystem. I don't know what ID1 is but it may be used to tell you which user session is blocked by which TC lock taken by CKPT.

Yong Huang Received on Tue Jun 15 2004 - 22:37:10 CDT