Re: CBC latch contention on index root block

From: Николаев Андрей Серапионович <>
Date: Mon, 21 Jan 2013 01:00:16 +0400 (MSK)
Message-ID: <>

I would like to add that this discussion revealed very interesting problem.

Some non-unnestable correlated subqueries repeatedly request CBC latches.

The simple testcase is:

create table s1(id primary key)
as select 1 from dual;

select 1 from (select rownum rn from dual connect by level <= 1000000)  where exists (select 0 from s1 where id=rn);

The above query in acquires CBC latch on index root block 2 million times in "exclusive" mode. Simultaneous execution of several queries will induce latch contention.

The same testcase in also acquires the latch 2 million times, but in "maybe-shared"/"shared" modes.

Therefore, the upgrade will relieve your contention.

However, it is even more profitable to rewrite problematic SQLs in order to get rid of non-unnestable subqueries, as was recommended by Jonathan.

Best regards.
Andrey Nikolaev

-- Received on Sun Jan 20 2013 - 22:00:16 CET

Original text of this message