Re: How to TC lock?

eygle_at_itpub.net (eygle) wrote in message news:<f9bc34d.0406132254.23e1df88_at_posting.google.com>...
> I find losts of TC lock in my database.
>
> From metalink ,I know that's "Thread Checkpoint equeue".
> When TC lock is there,the database is very slowly.
>
> From v$lock ,we can see:
>
> oracle:/usr/oracle8>sqlplus "/ as sysdba"
>
> SQL*Plus: Release 8.1.5.0.0 - Production on Mon Jun 14 14:47:22 2004
>
> (c) Copyright 1999 Oracle Corporation. All rights reserved.
>
>
> Connected to:
> Oracle8i Enterprise Edition Release 8.1.5.0.0 - Production
> With the Partitioning and Java options
> PL/SQL Release 8.1.5.0.0 - Production
>
> SQL> set linesize 120
> 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
> AB730528 AB730538 4 TC 65578 0 2 0 156 1
> AB730438 AB730448 4 TC 65555 0 2 0 156 1
> AB7302D0 AB7302E0 4 TC 65590 0 2 0 109 1
> AB730294 AB7302A4 4 TC 65577 0 2 0 156 1
> AB7300F0 AB730100 4 TC 65586 0 2 0 205 1
> AB72FD6C AB72FD7C 4 TC 65583 0 2 0 156 1
> AB72FC04 AB72FC14 4 TC 65559 0 2 0 156 1
> AB72F880 AB72F890 4 TC 65677 0 2 0 155 1
>
> ADDR KADDR SID TY ID1 ID2 LMODE REQUEST CTIME BLOCK
> -------- -------- ---- -- ---------- --- ---------- ------- ---------- ----------
> AB72F664 AB72F674 4 TC 65585 0 2 0 156 1
> AB72F5EC AB72F5FC 4 TC 65570 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
>
> ADDR KADDR SID TY ID1 ID2 LMODE REQUEST CTIME BLOCK
> -------- -------- ---- -- ---------- --- ---------- ------- ---------- ----------
> AB72F7CC AB72F7DC 124 TC 65559 0 5 6 156 0
> AB73003C AB73004C 125 TC 65555 0 5 6 156 0
> AB7306CC AB7306DC 139 TC 65585 0 5 6 156 0
> AB72FB8C AB72FB9C 184 TC 65599 0 5 6 156 0
>
> 26 rows selected.
>
> 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 Received on Tue Jun 15 2004 - 16:30:11 CDT