performance issue after upgrade to oracle 11.2.0.1 linux 32 bit

From: lsllcm <lsllcm_at_gmail.com>
Date: Wed, 25 Nov 2009 06:48:21 -0800 (PST)
Message-ID: <c35a0d39-d090-40b9-b09e-88c090ff80b0_at_g1g2000pra.googlegroups.com>

Hi All,

I have one insteresting performance issue about 11.2.0.1. Any comments are appreciated.

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.

1. The query that uses index scan 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
2. The query that uses table full scan 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 = X.B1_PER_ID1 AND F.B1_PER_ID2 = X.B1_PER_ID2 AND F.B1_PER_ID3 = X.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

I have put both 10053 trace for both execution plan as below url, could you help have a look?

http://docs.google.com/View?id=ddqzpk2x_4cmpn7tfv http://docs.google.com/View?id=ddqzpk2x_5db7q59fw

Thanks
lsllcm Received on Wed Nov 25 2009 - 08:48:21 CST