Re: CBC latch contention on index root block
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