Re: performance issue after upgrade to oracle 22.214.171.124 linux 32 bit
Date: Sat, 21 Nov 2009 12:27:31 -0800 (PST)
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'
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.
Oracle related stuff blog:
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