Re: performance issue after upgrade to oracle 11.2.0.1 linux 32 bit

From: Randolf Geist <mahrah_at_web.de>
Date: Sat, 21 Nov 2009 12:27:31 -0800 (PST)
Message-ID: <4018eb0f-905f-4539-826f-a2b25ce63ed5_at_v30g2000yqm.googlegroups.com>



On Nov 20, 5:15 pm, Charles Hooper <hooperc2..._at_yahoo.com> wrote:
> You will probably notice that these generated predicates appear in the
> Good execution plan due to transitive closure - I do not know why they
> do not appear in the bad pan (maybe an altered optimizer parameter?):
>   AND "X"."SERV_PROV_CODE"='SACRAMENTO'
>   AND "F"."SERV_PROV_CODE"='SACRAMENTO'
>   AND "X"."REC_STATUS"='A'
>   AND "F"."REC_STATUS"='A'
Charles,

it looks like the generated predicates appear in both traces I think, the "good" and the "bad" one.

To the OP: You might encounter something along the lines I've outlined here: http://oracle-randolf.blogspot.com/2009/10/multi-column-joins.html

Depending on the way you perform multi-column joins, the multi-column sanity checks built into recent versions of the CBO might get bypassed or not - that is one possible explanation, but with the amount of information provided, these are only guesses.

Note that in both cases, the "good" and the "bad", you're not really joining A->B->B->C or A->B->A->C, but both are different mixtures, because if these join predicates:

Statement 1) AND F.REC_STATUS = X.REC_STATUS

Statement 2) AND F.SERV_PROV_CODE = S.SERV_PROV_CODE

which you would need to turn into

Statement 1) AND F.REC_STATUS = S.REC_STATUS

Statement 2) AND F.SERV_PROV_CODE = X.SERV_PROV_CODE

to have achieved what you've described.

Can you confirm that these are actually the PK/FK relations between those tables, no spurious join predicates on non-FK/PK columns?

You should run the statement with STATISTICS_LEVEL = ALL (or the GATHER_PLAN_STATISTICS hint) to compare the estimates to the actual cardinalities - you might have correlated columns among the join columns that are not recognized by the optimizer by default.

It seems to be odd that you get these OPT_ESTIMATE hints but no SQL Profile seems to be enabled - may be this is something new in 11.2, but usually you should see a corresponding note in the "Notes" section of an DBMS_XPLAN.DISPLAY output and in the 10053 trace file (e.g. a reference to a plan found in the SPM - SQL Plan Management feature introduced in 11.1). May be these hints get introduced by an Outline or SQL Baseline from SPM.

Regards,
Randolf

Oracle related stuff blog:
http://oracle-randolf.blogspot.com/

Co-author of the upcoming "OakTable Expert Oracle Practices" book: http://www.apress.com/book/view/1430226684 http://www.amazon.de/Expert-Oracle-Practices-Database-Administration/dp/1430226684 Received on Sat Nov 21 2009 - 14:27:31 CST

Original text of this message