Re: CBC latch contention on index root block

Your original query transforms into something of the form:

select where
cust_num = K
or exists (subquery for cust_num = K)
or (cust_num = -1 and not exists (subquery for cust_num = K)

The optimizer cannot handle (predicate OR subquery) efficiently (see http://jonathanlewis.wordpress.com/2007/02/26/subquery-with-or/ ) so it has had to do a tablescan to get 892 rows that might match the customer or the subquery, with the consequence that it has ended up running the subquery 871 times. Either you have found a latch-related bug which makes this subquery execution highly contentious, or it is the number of calls - combined with the number of concurrent users - that makes this so inefficient that you have a problem. If it's a bug you need a patch; if it's inherent then you can rewrite the query

select
where cust_num = K
union all
select where cust_num = -1 and not exists (subquery for cust_num = K) union all
select where exists(subquery for cust_num = K)

With suitable indexing the first two parts can be more efficient that your current query (I've omitted the abs_typ_id for clarity, but an index starting with abs_typ_id and cust_num in some order would be useful). You don't have to worry about accidental duplicates from the first two pieces

For the last part of the union all you need to ensure that you include a predicate to eliminate the rows already returned through the first two parts - and your optimum plan would be unnesting and converting to a nested loop join. I am assuming that this table will always be a very small table and that you would drive off an index on the cust_num when accessing this table because very few of your customers will appear in the table.

Regards

Jonathan Lewis
http://jonathanlewis.wordpress.com/all-postings

Author: Oracle Core (Apress 2011)
http://www.apress.com/9781430239543

• Original Message ----- From: "oracledba" <oracledba71_at_gmail.com> To: <jonathan_at_jlcomp.demon.co.uk> Cc: "Oracle-L Freelists" <Oracle-L_at_freelists.org> Sent: Saturday, January 19, 2013 12:34 AM Subject: Re: CBC latch contention on index root block

| Here is the query with the plan and predicate section.CUST_NUM value is
| same for all 4 bind variables.
|
| ---------------------------------------------------------------------------------------------------

--
http://www.freelists.org/webpage/oracle-l