Re: CBC latch contention on index root block

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



Hello!
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 10.2.0.4 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 11.2.0.3 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
http://andreynikolaev.wordpress.com

--

http://www.freelists.org/webpage/oracle-l Received on Sun Jan 20 2013 - 22:00:16 CET

Original text of this message