Re: performance issue after upgrade to oracle 11.2.0.1 linux 32 bit.
Date: Wed, 18 Nov 2009 04:53:56 -0800 (PST)
Message-ID: <997ce688-ae47-486a-8f13-4aae8cd00157_at_g27g2000yqn.googlegroups.com>
On Nov 18, 5:26 am, lsllcm <lsl..._at_gmail.com> wrote:
> Hi Charles and Randolf,
>
> There is one more interesting issue.
>
> When I use A JOIN B and A JOIN C, the optimizer choose index scan on
> table C.
> When I use A JOIN B and B JOIN C, the optimizer choose full table scan
> on table C.
>
> Below is test case:
>
> Case 1: When I use A JOIN B and A JOIN C, the optimizer choose index
> scan on table C.
>
> SELECT X.SERV_PROV_CODE,
> X.B1_PER_ID1,
> X.B1_PER_ID2,
> X.B1_PER_ID3,
> SUM(NVL(X.Fee_Allocation, 0)) GF_FEE
> FROM SETDETAILS S, X4PAYMENT_FEEITEM X, F4FEEITEM F
> WHERE S.SERV_PROV_CODE = 'SACRAMENTO'
> AND S.SET_ID = 'CONNIE'
> AND S.REC_STATUS = 'A'
> AND S.SERV_PROV_CODE = X.SERV_PROV_CODE
> AND F.SERV_PROV_CODE = S.SERV_PROV_CODE
> AND S.B1_PER_ID1 = X.B1_PER_ID1
> AND S.B1_PER_ID2 = X.B1_PER_ID2
> AND S.B1_PER_ID3 = X.B1_PER_ID3
> AND F.B1_PER_ID1 = S.B1_PER_ID1
> AND F.B1_PER_ID2 = S.B1_PER_ID2
> AND F.B1_PER_ID3 = S.B1_PER_ID3
> AND F.FEEITEM_SEQ_NBR = X.FEEITEM_SEQ_NBR
> AND S.REC_STATUS = X.REC_STATUS
> AND F.REC_STATUS = X.REC_STATUS
> AND (X.PAYMENT_FEEITEM_STATUS != 'VOIDED' OR
> X.PAYMENT_FEEITEM_STATUS IS NULL)
> GROUP BY X.SERV_PROV_CODE, X.B1_PER_ID1, X.B1_PER_ID2, X.B1_PER_ID3
Please post the other query also. Transitive closure should apply to the query, and Oracle should be able to derive the additional join conditions:
If A=B and B=C, then A=C
If A=B and A=C, then B=C
Thus, after transformation the two queries should appear nearly identical.
Please generate a 10053 trace at level 1 for each of the two queries. Search the 10053 trace for the following line: Final query after transformations:******* UNPARSED QUERY IS *******
Below that line in a 11.2.0.1 10053 trace file you should see the final version of the query after transformation (although it appears that Oracle 11.1.0.6 and above may not show derived "IS NOT NULL" predicates in the final query output.) Please post the transformed version of each query which appears below the line "Final query after transformations:******* UNPARSED QUERY IS *******" in the trace file.
There must be a significant difference in the two transformed versions of the queries as one plan predicts that 40,516 rows will be returned while the other predicts that 1 row will be returned.
If you are now experiencing performance problems with a different query, you might consider creating a new message thread in this group - there is a chance that more people will see the new message thread and offer assistance.
Charles Hooper
IT Manager/Oracle DBA
K&M Machine-Fabricating, Inc.
Received on Wed Nov 18 2009 - 06:53:56 CST