Re: performance issue after upgrade to oracle 11.2.0.1 linux 32 bit
Date: Fri, 20 Nov 2009 07:30:27 -0800 (PST)
Message-ID: <97516905-1b5e-461a-becc-39377a8f23ad_at_r24g2000prf.googlegroups.com>
Hi Charles,
I have put the 10053 trace here along with query 1. Good plan
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
/
from 10053 trace file
Final query after transformations:******* UNPARSED QUERY IS *******
SELECT /*+ OPT_ESTIMATE (GROUP_BY ROWS=4.000000 ) OPT_ESTIMATE (TABLE
"F" MIN=27.000000 ) OPT_ESTIMATE (INDEX_SCAN "F" "F4FEEITEM_PK"
MIN=27.000000 ) OPT_ESTIMATE (INDEX_FILTER "F" "F4FEEITEM_PK"
MIN=27.000000 ) OPT_ESTIMATE (TABLE "X" MIN=13.000000 ) OPT_ESTIMATE
(INDEX_SCAN "X" "X4PAYMENT_FEEITEM_PK" MIN=13.000000 ) OPT_ESTIMATE
(INDEX_FILTER "X" "X4PAYMENT_FEEITEM_PK" MIN=13.000000 ) */
"X"."SERV_PROV_CODE" "SERV_PROV_CODE","X"."B1_PER_ID1"
"B1_PER_ID1","X"."B1_PER_ID2" "B1_PER_ID2","X"."B1_PER_ID3"
"B1_PER_ID3",SUM(NVL("X"."FEE_ALLOCATION",0)) "GF_FEE" FROM
"ACCELA"."SETDETAILS" "S","ACCELA"."X4PAYMENT_FEEITEM"
"X","ACCELA"."F4FEEITEM" "F" WHERE "S"."SERV_PROV_CODE"='SACRAMENTO'
AND "S"."SET_ID"='CONNIE' AND "S"."REC_STATUS"='A' AND"S"."REC_STATUS"="X"."REC_STATUS" 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
"F"."REC_STATUS"="X"."REC_STATUS" AND
("X"."PAYMENT_FEEITEM_STATUS"<>'VOIDED' OR
"X"."PAYMENT_FEEITEM_STATUS" IS NULL) AND
"X"."SERV_PROV_CODE"='SACRAMENTO' AND
"F"."SERV_PROV_CODE"='SACRAMENTO' AND "X"."REC_STATUS"='A' AND
"F"."REC_STATUS"='A' GROUP BY
"X"."SERV_PROV_CODE","X"."B1_PER_ID1","X"."B1_PER_ID2","X"."B1_PER_ID3"
kkoqbc: optimizing query block SEL$1 (#1)
:
call(in-use=12596, alloc=32736), compile(in-use=149332,
alloc=161508), execution(in-use=2864, alloc=4060)
2. Poor plan
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
/
from 10053 trace file
Final query after transformations:******* UNPARSED QUERY IS *******
SELECT "X"."SERV_PROV_CODE" "SERV_PROV_CODE","X"."B1_PER_ID1"
"B1_PER_ID1","X"."B1_PER_ID2" "B1_PER_ID2","X"."B1_PER_ID3"
"B1_PER_ID3",SUM(NVL("X"."FEE_ALLOCATION",0)) "GF_FEE" FROM
"ACCELA"."SETDETAILS" "S","ACCELA"."X4PAYMENT_FEEITEM"
"X","ACCELA"."F4FEEITEM" "F" WHERE "S"."SERV_PROV_CODE"='SACRAMENTO'
AND "S"."SET_ID"='CONNIE' AND "S"."REC_STATUS"='A' AND"S"."REC_STATUS"="X"."REC_STATUS" 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
"F"."REC_STATUS"="X"."REC_STATUS" AND
("X"."PAYMENT_FEEITEM_STATUS"<>'VOIDED' OR
"X"."PAYMENT_FEEITEM_STATUS" IS NULL) AND
"X"."SERV_PROV_CODE"='SACRAMENTO' AND
"F"."SERV_PROV_CODE"='SACRAMENTO' AND "X"."REC_STATUS"='A' AND
"F"."REC_STATUS"='A' GROUP BY
"X"."SERV_PROV_CODE","X"."B1_PER_ID1","X"."B1_PER_ID2","X"."B1_PER_ID3"
kkoqbc: optimizing query block SEL$1 (#1)
:
call(in-use=12532, alloc=32736), compile(in-use=140036,
alloc=149244), execution(in-use=2720, alloc=4060)
Received on Fri Nov 20 2009 - 09:30:27 CST