Re: Unknown lock type TC

On Wed, 17 Oct 2001 09:31:39 +0100, Jonathan Lewis wrote:
>
>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 Received on Wed Oct 17 2001 - 11:41:26 CDT