| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: How to TC lock?
yong321_at_yahoo.com (Yong Huang) wrote in message news:<b3cb12d6.0406151937.ad68b8c_at_posting.google.com>...
> 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>...
>
> 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
Thanks Yong Huang.
I have solved this problem.
As someone report at metalink ,i have notice that ,we still have heavy
io load.
One partition of disk have always > 90% io busy. ...
0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0 0 c0t6d0 0.2 2.2 1.6 2.6 0.0 0.0 0.0 7.5 0 2 c0t10d0 11.0 5.2 86.9 23.4 0.0 0.2 0.0 9.6 0 13 c0t11d0 65.1 139.2 520.6 1113.3 0.0 1.0 0.0 4.8 0 97 c1t1d0...
And I check the database,find a awful sql. Which have lock 7 table and execute several times and without output. Modify that sql everything is ok.
From v$lock view,I find all of it,including TC lock and awful sql
lock.
I just don't know how TC lock act as.
"But Metalink Note:267332.1 for 10g locks calls it tablespace checkpoint, which is probably more correct."
I thinks so,too:)
I list v$lock output here for refer:
SQL> select * from v$lock;
ADDR KADDR SID TY ID1 ID2 LMODE REQUEST CTIME BLOCK
AB731C98 AB731CA8 3 RT 1 0 6 0 0 0 ADDR KADDR SID TY ID1 ID2 LMODE REQUEST CTIME BLOCK
AB7304B0 AB7304C0 4 TC 65606 0 2 0 0 1 AB730384 AB730394 4 TC 65569 0 2 0 49 1 AB72FE5C AB72FE6C 4 TC 65747 0 2 0 0 1 AB72F970 AB72F980 4 TC 65604 0 2 0 0 1 AB72F100 AB72F110 4 TC 65659 0 2 0 40 1 AB72EF98 AB72EFA8 4 TC 65605 0 2 0 0 1 AB72EDB8 AB72EDC8 4 TC 65657 0 2 0 0 1 AB72EB24 AB72EB34 4 TC 65567 0 2 0 0 1 AB731CD4 AB731CE4 5 TS 3 16976310 3 0 206418 0 AB730834 AB730844 5 PS 1 1 4 0 38238 0 AB7307BC AB7307CC 5 PS 1 0 4 0 38239 0 ADDR KADDR SID TY ID1 ID2 LMODE REQUEST CTIME BLOCK
AB730780 AB730790 5 PS 1 3 4 0 38238 0 AB730690 AB7306A0 5 PS 1 2 4 0 38238 0 AB7305A0 AB7305B0 5 TX 393235 3373363 6 0 852 1 AB730564 AB730574 5 PS 1 4 4 0 38238 0 016F7E30 016F7F04 12 TX 196626 3461822 6 0 1 0 ADD78738 ADD7874C 12 TM 18417 0 3 0 0 0 016F7E30 016F7F04 15 TX 655360 3230374 6 0 2495 0 ADD7B15C ADD7B170 15 TM 17325 0 3 0 2494 0 AB730870 AB730880 17 PS 1 3 4 0 38238 0 016F7E30 016F7F04 20 TX 327695 3434958 6 0 1871 0 ADD7B740 ADD7B754 20 TM 18480 0 3 0 1870 0 ADDR KADDR SID TY ID1 ID2 LMODE REQUEST CTIME BLOCK
ADD78398 ADD783AC 23 TM 18409 0 3 0 524 0 AB72EA34 AB72EA44 23 TX 393236 3375266 0 6 524 0 ADD7B4FC ADD7B510 37 TM 18409 0 3 0 990 0 AB730528 AB730538 37 TX 393236 3375266 0 6 990 0 016F7E30 016F7F04 53 TX 655377 3228475 6 0 0 0 016F7E30 016F7F04 81 TX 65548 3358644 6 0 30 0 ADD79788 ADD7979C 81 TM 18488 0 3 0 29 0 AB7305DC AB7305EC 82 PS 1 1 4 0 38238 0 016F7E30 016F7F04 85 TX 393216 3373590 6 0 947 0 ADD7ACD4 ADD7ACE8 85 TM 18465 0 3 0 946 0 AB72F448 AB72F458 86 TC 65567 0 5 6 0 0 ADDR KADDR SID TY ID1 ID2 LMODE REQUEST CTIME BLOCK
016F7E30 016F7F04 87 TX 131085 3425586 6 0 30 0 ADD78894 ADD788A8 87 TM 18499 0 3 0 57 0 AB730654 AB730664 93 PS 1 4 4 0 38238 0 AB731C20 AB731C30 97 PS 1 2 4 0 38238 0 ADD79E54 ADD79E68 110 TM 15063 0 3 0 42206 0 AB730618 AB730628 110 TX 196610 3460278 0 6 42206 0 016F7E30 016F7F04 111 TX 65543 3358178 6 0 43173 0 ADD79544 ADD79558 111 TM 18409 0 3 0 43172 0 ADD794D0 ADD794E4 111 TM 18441 0 3 0 43172 0 ADD7945C ADD79470 111 TM 14564 0 3 0 43172 0 ADD79374 ADD79388 111 TM 18438 0 3 0 43172 0 ADDR KADDR SID TY ID1 ID2 LMODE REQUEST CTIME BLOCK
ADD79300 ADD79314 111 TM 15063 0 3 0 43172 0 ADD7928C ADD792A0 111 TM 18433 0 3 0 43172 0 ADD787AC ADD787C0 111 TM 15043 0 3 0 43172 0 AB730960 AB730970 111 TX 524292 3251912 0 6 990 0 016F7E30 016F7F04 112 TX 524293 3254292 6 0 44632 1 ADD77844 ADD77858 112 TM 18441 0 3 0 44629 0 ADD77674 ADD77688 112 TM 15043 0 3 0 44630 0 ADD772D4 ADD772E8 112 TM 15063 0 3 0 44631 0 ADD7701C ADD77030 112 TM 18433 0 3 0 44629 0 ADD76F34 ADD76F48 112 TM 15045 0 3 0 44629 0 ADD76EC0 ADD76ED4 112 TM 18409 0 3 0 44629 0 ADDR KADDR SID TY ID1 ID2 LMODE REQUEST CTIME BLOCK
ADD76E4C ADD76E60 112 TM 18438 0 3 0 44629 0 ADD793E8 ADD793FC 113 TM 15063 0 3 0 42150 0 AB730CA8 AB730CB8 113 TX 196610 3460278 0 6 42150 0 AB730708 AB730718 116 PS 1 0 4 0 38238 0 016F7E30 016F7F04 118 TX 589826 3184838 6 0 43153 0 ADD798E4 ADD798F8 118 TM 15063 0 3 0 43152 0 AB730744 AB730754 118 TX 262163 3467037 0 4 43152 0 ADD78650 ADD78664 120 TM 18409 0 3 0 522 0 AB730294 AB7302A4 120 TX 393236 3375266 0 6 498 0 016F7E30 016F7F04 122 TX 589830 3181516 6 0 43090 1 ADD79D6C ADD79D80 122 TM 15043 0 3 0 43089 0 ADDR KADDR SID TY ID1 ID2 LMODE REQUEST CTIME BLOCK
ADD79C84 ADD79C98 122 TM 18433 0 3 0 43089 0 ADD79C10 ADD79C24 122 TM 14564 0 3 0 43088 0 ADD79B28 ADD79B3C 122 TM 18441 0 3 0 43088 0 ADD79AB4 ADD79AC8 122 TM 18409 0 3 0 43088 0 ADD79A40 ADD79A54 122 TM 18438 0 3 0 43089 0 ADD799CC ADD799E0 122 TM 15063 0 3 0 43089 0 AB7306CC AB7306DC 122 TX 524292 3251912 0 6 989 0 016F7E30 016F7F04 123 TM 16126 0 6 0 0 0 ADD778B8 ADD778CC 126 TM 15063 0 3 0 44541 0 AB730B40 AB730B50 126 TX 524293 3254292 0 6 44541 0 ADD7BCB0 ADD7BCC4 128 TM 18409 0 3 0 990 0 ADDR KADDR SID TY ID1 ID2 LMODE REQUEST CTIME BLOCK
AB730438 AB730448 128 TX 393236 3375266 0 6 990 0 ADD7A6F0 ADD7A704 129 TM 18409 0 3 0 990 0 AB73030C AB73031C 129 TX 393236 3375266 0 6 990 0 ADD77090 ADD770A4 130 TM 15063 0 3 0 44537 0 AB730CE4 AB730CF4 130 TX 524293 3254292 0 6 44537 0 016F7E30 016F7F04 131 TX 524305 3252987 6 0 42897 0 ADD79DE0 ADD79DF4 131 TM 15063 0 3 0 42896 0 AB7307F8 AB730808 131 TX 589830 3181516 0 4 42896 0 AB72F088 AB72F098 133 TC 65569 0 5 6 49 0 ADD7AF18 ADD7AF2C 136 TM 18409 0 3 0 1011 0 AB72F9AC AB72F9BC 136 TX 393236 3375266 0 6 1011 0 ADDR KADDR SID TY ID1 ID2 LMODE REQUEST CTIME BLOCK
ADD7B828 ADD7B83C 137 TM 18409 0 3 0 1011 0 AB72F358 AB72F368 137 TX 393236 3375266 0 6 1010 0 ADD77A14 ADD77A28 140 TM 15063 0 3 0 44279 0 AB730A8C AB730A9C 140 TX 524293 3254292 0 6 44279 0 ADD7B32C ADD7B340 141 TM 18409 0 3 0 1011 0 AB72FC04 AB72FC14 141 TX 393236 3375266 0 6 1010 0 ADD77CCC ADD77CE0 142 TM 18409 0 3 0 524 0 AB730078 AB730088 142 TX 393236 3375266 0 6 524 0 ADD790BC ADD790D0 144 TM 15063 0 3 0 43343 0 AB7308E8 AB7308F8 144 TX 524293 3254292 0 6 43343 0 ADD77518 ADD7752C 145 TM 18409 0 3 0 1011 0 ADDR KADDR SID TY ID1 ID2 LMODE REQUEST CTIME BLOCK
AB72F754 AB72F764 145 TX 393236 3375266 0 6 1011 0 ADD77348 ADD7735C 146 TM 18409 0 3 0 1011 0 AB72F4C0 AB72F4D0 146 TX 393236 3375266 0 6 1011 0 ADD76DD8 ADD76DEC 147 TM 15063 0 3 0 44531 0 AB7309D8 AB7309E8 147 TX 524293 3254292 0 6 44531 0 016F7E30 016F7F04 148 TX 393236 3375266 6 0 1012 1 ADD7B570 ADD7B584 148 TM 18409 0 3 0 1011 0 AB7303FC AB73040C 148 TX 393235 3373363 0 6 851 0 016F7E30 016F7F04 149 TX 524292 3251912 6 0 44221 1 ADD77FF8 ADD7800C 149 TM 18409 0 3 0 44218 0 ADD77F10 ADD77F24 149 TM 18433 0 3 0 44218 0 ADDR KADDR SID TY ID1 ID2 LMODE REQUEST CTIME BLOCK
ADD77E9C ADD77EB0 149 TM 15045 0 3 0 44220 0 ADD77E28 ADD77E3C 149 TM 15063 0 3 0 44220 0 ADD77C58 ADD77C6C 149 TM 18441 0 3 0 44218 0 ADD77BE4 ADD77BF8 149 TM 18438 0 3 0 44218 0 ADD77B70 ADD77B84 149 TM 15043 0 3 0 44220 0 ADD7775C ADD77770 150 TM 15063 0 3 0 44291 0 AB730C6C AB730C7C 150 TX 524293 3254292 0 6 44291 0 ADD7B000 ADD7B014 152 TM 18409 0 3 0 990 0 AB730258 AB730268 152 TX 393236 3375266 0 6 990 0 ADD77DB4 ADD77DC8 155 TM 15063 0 3 0 44269 0 AB730C30 AB730C40 155 TX 524293 3254292 0 6 44269 0 ADDR KADDR SID TY ID1 ID2 LMODE REQUEST CTIME BLOCK
ADD7B89C ADD7B8B0 157 TM 18409 0 3 0 990 0 AB72F538 AB72F548 157 TX 393236 3375266 0 6 990 0 ADD7792C ADD77940 159 TM 15063 0 3 0 44240 0 AB730A14 AB730A24 159 TX 524293 3254292 0 6 44240 0 ADD77A88 ADD77A9C 160 TM 15063 0 3 0 44277 0 AB730BB8 AB730BC8 160 TX 524293 3254292 0 6 44277 0 ADD78E78 ADD78E8C 161 TM 18409 0 3 0 1011 0 AB72FF88 AB72FF98 161 TX 393236 3375266 0 6 1011 0 ADD771EC ADD77200 163 TM 18409 0 3 0 1010 0 AB72FED4 AB72FEE4 163 TX 393236 3375266 0 6 1010 0 016F7E30 016F7F04 164 TX 262163 3467037 6 0 43419 1 ADDR KADDR SID TY ID1 ID2 LMODE REQUEST CTIME BLOCK
ADD78F60 ADD78F74 164 TM 18441 0 3 0 43418 0 ADD78EEC ADD78F00 164 TM 15043 0 3 0 43418 0 ADD78E04 ADD78E18 164 TM 18438 0 3 0 43418 0 ADD78D90 ADD78DA4 164 TM 18409 0 3 0 43418 0 ADD78D1C ADD78D30 164 TM 15063 0 3 0 43418 0 ADD78CA8 ADD78CBC 164 TM 18433 0 3 0 43418 0 ADD7758C ADD775A0 164 TM 14564 0 3 0 43418 0 AB7308AC AB7308BC 164 TX 524292 3251912 0 6 990 0 ADD7AA1C ADD7AA30 166 TM 18409 0 3 0 1011 0 AB72F844 AB72F854 166 TX 393236 3375266 0 6 1011 0 ADD77F84 ADD77F98 167 TM 15063 0 3 0 44226 0 ADDR KADDR SID TY ID1 ID2 LMODE REQUEST CTIME BLOCK
AB730AC8 AB730AD8 167 TX 524293 3254292 0 6 44226 0 ADD7B1D0 ADD7B1E4 170 TM 18409 0 3 0 990 0 AB730A50 AB730A60 170 TX 393236 3375266 0 6 990 0 ADD7AD48 ADD7AD5C 177 TM 18409 0 3 0 990 0 AB730B7C AB730B8C 177 TX 393236 3375266 0 6 990 0 ADD781C8 ADD781DC 178 TM 15063 0 3 0 44155 0 AB730BF4 AB730C04 178 TX 524292 3251912 0 6 44155 0 ADD7A764 ADD7A778 179 TM 18409 0 3 0 1011 0 AB72FA9C AB72FAAC 179 TX 393236 3375266 0 6 1011 0 ADD7B244 ADD7B258 185 TM 18409 0 3 0 990 0 AB72F010 AB72F020 185 TX 393236 3375266 0 6 969 0 ADDR KADDR SID TY ID1 ID2 LMODE REQUEST CTIME BLOCK
016F7E30 016F7F04 187 TX 196610 3460278 6 0 43569 1 ADD78C34 ADD78C48 187 TM 15063 0 3 0 43568 0 ADD78B4C ADD78B60 187 TM 15045 0 3 0 43563 0 ADD78AD8 ADD78AEC 187 TM 15043 0 3 0 43563 0 ADD78A64 ADD78A78 187 TM 18409 0 3 0 43563 0 ADD7897C ADD78990 187 TM 18441 0 3 0 43563 0 ADD78908 ADD7891C 187 TM 18438 0 3 0 43563 0 ADD785DC ADD785F0 187 TM 18433 0 3 0 43563 0 AB731D10 AB731D20 187 TX 524292 3251912 0 6 990 0 AB72EB9C AB72EBAC 188 TC 65657 0 5 6 0 0 ADD7AC60 ADD7AC74 189 TM 18409 0 3 0 990 0 ADDR KADDR SID TY ID1 ID2 LMODE REQUEST CTIME BLOCK
AB730000 AB730010 189 TX 393236 3375266 0 6 990 0 ADD7B984 ADD7B998 190 TM 18409 0 3 0 990 0 AB73003C AB73004C 190 TX 393236 3375266 0 6 990 0 ADD7AEA4 ADD7AEB8 192 TM 18409 0 3 0 990 0 AB72F268 AB72F278 192 TX 393236 3375266 0 6 990 0 016F7E30 016F7F04 194 TX 327691 3433289 6 0 8624 0 ADD7B2B8 ADD7B2CC 194 TM 18409 0 3 0 8600 0 ADD7ADBC ADD7ADD0 194 TM 15043 0 3 0 8616 0 ADD7A8C0 ADD7A8D4 194 TM 15045 0 3 0 8616 0 ADD7A2DC ADD7A2F0 194 TM 15063 0 3 0 8623 0 ADD7A098 ADD7A0AC 194 TM 18438 0 3 0 8600 0 ADDR KADDR SID TY ID1 ID2 LMODE REQUEST CTIME BLOCK
ADD76FA8 ADD76FBC 194 TM 18433 0 3 0 8600 0 ADD76CF0 ADD76D04 194 TM 18441 0 3 0 8600 0 016F7E30 016F7F04 199 TX 65536 3356446 6 0 43275 0 ADD791A4 ADD791B8 199 TM 18409 0 3 0 43273 0 ADD79130 ADD79144 199 TM 15063 0 3 0 43274 0 ADD79048 ADD7905C 199 TM 15043 0 3 0 43274 0 ADD78FD4 ADD78FE8 199 TM 18433 0 3 0 43273 0 ADD78BC0 ADD78BD4 199 TM 18438 0 3 0 43273 0 ADD7840C ADD78420 199 TM 15045 0 3 0 43274 0 ADD76C7C ADD76C90 199 TM 18441 0 3 0 43273 0 ADD79958 ADD7996C 200 TM 18409 0 3 0 990 0 ADDR KADDR SID TY ID1 ID2 LMODE REQUEST CTIME BLOCK
AB730474 AB730484 200 TX 393236 3375266 0 6 990 0 ADD7A350 ADD7A364 210 TM 18409 0 3 0 990 0 AB730348 AB730358 210 TX 393236 3375266 0 6 990 0 016F7E30 016F7F04 211 TX 327690 3433919 6 0 43268 0 ADD79218 ADD7922C 211 TM 15063 0 3 0 43267 0 AB730924 AB730934 211 TX 262163 3467037 0 4 43267 0 ADD7B488 ADD7B49C 214 TM 18409 0 3 0 990 0 AB72F8F8 AB72F908 214 TX 393236 3375266 0 6 990 0 ADD7A024 ADD7A038 215 TM 18409 0 3 0 497 0 AB72FC40 AB72FC50 215 TX 393236 3375266 0 6 497 0 AB72FD6C AB72FD7C 216 TC 65605 0 5 6 0 0 ADDR KADDR SID TY ID1 ID2 LMODE REQUEST CTIME BLOCK
AB72F484 AB72F494 224 TC 65604 0 5 6 0 0 AB72F40C AB72F41C 227 TC 65747 0 5 6 0 0 AB72EAAC AB72EABC 228 TC 65659 0 5 6 40 0 AB7301E0 AB7301F0 247 TC 65606 0 5 6 0 0
268 rows selected.
Thanks.
Eygle Received on Fri Jun 18 2004 - 02:20:51 CDT
 |
 |