Unknown lock type TC
Adon Keber
One of our applications are working badly and during research of a problem
I have encountered lock type TC in v$lock
First indication of problem was:
select type,count(*)
from v$lock
group by type
TY COUNT(*)
-- ----------
MR 33
PS 8
RT 1
TM 7
TS 2
TX 5
It seems that some SELECT query locks itself somehow.
Lock requested is usually S/ROW-X(SSX) and lock mode is EXCLUSIVE
Final indication of strange problem is:
SELECT ksqsttyp "Lock",
ksqstget "Gets",
ksqstwat "Waits"
FROM X$KSQST where KSQSTWAT>0
and result:
Lo Gets Waits
-- ---------- ----------
PE 3934019 3
PS 8389739 1
TC 1331031 2649
TM 3798880 64
TX 1780228 18
So applications are mainly waiting on TC lock.
I couldn't find any reference of TC lock type except on metalink where
Oracle's support person admits that he can't find reference of TC lock
(Doc ID 101144.998)
Thanks,
adon
Jonathan Lewis
I don't know what a TC is, but you could do
the following - find a session which has lost
a lot of time on TC locks (look in v$session_event
for a high time_waited on enqueues) then
hit the session with a 10046 trace at level 8.
Wait a while, then search the trace file for
WAIT #n name = 'enqueue' ela= p1=1413677060
(or perhaps 66 at the end).
This should give you a clue as to which cursor
is suffering from the waits, and this may help.
--
Jonathan Lewis
http://www.jlcomp.demon.co.uk
Host to The Co-Operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html
Author of:
Practical Oracle 8i: Building Efficient Databases
Screen saver or Life saver: http://www.ud.com
Use spare CPU to assist in cancer research.
Adon Keber wrote in message ...
Jonathan Lewis
Are you using Parallel Query - if you do a
search on metalink for "TC" and "enqueue"
you will find a forum thread that discusses
the TC enqueue.
I infer from reading it that this is the 8i equivalent
(or manifestation) of the 'extent-based checkpoint'
that used to take place in 7.3 when a parallel
query slave was about to start using direct reads.
It is a little-known fact that when a PX process is using
direct reads, it has to flush all dirty blocks in the read
range from the buffer to disc.
If this is your problem, then you may ease the problem
by taking actions to increase the speed with which the
database writers can get data down to disc. Alternatively
you might review your application to check whether there
is a constantly changing data set that is begin hit unnecessarily
by PX slaves, and change the application accordingly.
NOTE - although TC enqueues may have high visibility,
don't chase the issue too hard unless you are showing
a lot of wasted time on waits for 'enqueue' - and do use
v$session_event to identify the sessions suffering most.
--
Jonathan Lewis
http://www.jlcomp.demon.co.uk
Host to The Co-Operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html
Author of:
Practical Oracle 8i: Building Efficient Databases
Screen saver or Life saver: http://www.ud.com
Use spare CPU to assist in cancer research.
Adon Keber wrote in message ...
>
Adon Keber
>
>Are you using Parallel Query - if you do a
>search on metalink for "TC" and "enqueue"
>you will find a forum thread that discusses
>the TC enqueue.
>
>I infer from reading it that this is the 8i equivalent
>(or manifestation) of the 'extent-based checkpoint'
>that used to take place in 7.3 when a parallel
>query slave was about to start using direct reads.
>
>It is a little-known fact that when a PX process is using
>direct reads, it has to flush all dirty blocks in the read
>range from the buffer to disc.
This have pointed me to the right direction. It seems that one index
was missing which should be used in query which comes from web form.
This queries was executed in parallel because of table scan taking
8-10 sec instead of using index and taking less than 1 sec. Users from
web hasn't complained on delay so nobody noticed any problem (although
I noticed larger than usual CPU usage, increase in wait times, and
unusual increase in buffer miss ratio).
Query was on Top 5 list but it is always there, just buffer gets got
larger. Not to mention that yesterday we got 3 times as much requests
from web than usual.
At the same time application which loads the data in the same table
was experiencing sudden blocks and delays of unknown origin in unregular
intervals.
Each session was having high enqueue wait time and from time to time
I saw this session on 'Blocked locks' on Toad with unusual lock type TC.
Only later I noticed that there was always SID 4 in list of TC locks
in v$lock. SID 4 is of course CKPT.
It seems than inefficient query was causing constant checkpoints on
tablespace which was blocking DML from application.
Big thanks,
adon