Re: performance issue after upgrade to oracle 11.2.0.1 linux 32 bit
Date: Sun, 22 Nov 2009 03:43:27 -0800 (PST)
Message-ID: <695fd136-6536-4dfa-9298-9f0f26a64768_at_u18g2000pro.googlegroups.com>
Hi Randolf,
Thanks for your comments, below are good plan with gather_plan_statistics.
SQL> SELECT /*+ gather_plan_statistics */
2 X.SERV_PROV_CODE, 3 X.B1_PER_ID1, 4 X.B1_PER_ID2, 5 X.B1_PER_ID3, 6 SUM(NVL(X.Fee_Allocation, 0)) GF_FEE7 FROM SETDETAILS S, X4PAYMENT_FEEITEM X, F4FEEITEM F 8 WHERE
9 S.SERV_PROV_CODE = 'SACRAMENTO' 10 AND S.SET_ID = 'CONNIE' 11 AND S.REC_STATUS = 'A' 12 AND S.SERV_PROV_CODE = X.SERV_PROV_CODE 13 AND F.SERV_PROV_CODE = S.SERV_PROV_CODE 14 AND S.B1_PER_ID1 = X.B1_PER_ID1 15 AND S.B1_PER_ID2 = X.B1_PER_ID2 16 AND S.B1_PER_ID3 = X.B1_PER_ID3 17 AND F.B1_PER_ID1 = S.B1_PER_ID1 18 AND F.B1_PER_ID2 = S.B1_PER_ID2 19 AND F.B1_PER_ID3 = S.B1_PER_ID3 20 AND F.FEEITEM_SEQ_NBR = X.FEEITEM_SEQ_NBR 21 AND S.REC_STATUS = X.REC_STATUS 22 AND F.REC_STATUS = X.REC_STATUS 23 AND (X.PAYMENT_FEEITEM_STATUS != 'VOIDED' OR24 X.PAYMENT_FEEITEM_STATUS IS NULL)
25 AND "X"."SERV_PROV_CODE"='SACRAMENTO' 26 AND "F"."SERV_PROV_CODE"='SACRAMENTO' 27 AND "X"."REC_STATUS"='A' 28 AND "F"."REC_STATUS"='A'
29 GROUP BY
30 X.SERV_PROV_CODE, 31 X.B1_PER_ID1, 32 X.B1_PER_ID2, 33 X.B1_PER_ID3
34 /
Elapsed: 00:00:00.26
SQL> SELECT * FROM TABLE (DBMS_XPLAN.DISPLAY_CURSOR
(NULL,NULL,'ALLSTATS LAST')) ;
PLAN_TABLE_OUTPUT
SQL_ID 4tc7hc9r2t25s, child number 0
SELECT /*+ gather_plan_statistics */ 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 = PLAN_TABLE_OUTPUT
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) 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
Plan hash value: 305769021
PLAN_TABLE_OUTPUT
| Id | Operation | Name |Starts | E-Rows | A-Rows | A-Time | Buffers | Reads | OMem | 1Mem | Used-Mem |
| 0 | SELECT STATEMENT | | 1 | | 4 |00:00:00.02 | 77 | 3 | | | | | 1 | HASH GROUP BY | | 1 | 60951 | 4 |00:00:00.02 | 77 | 3 | 745K| 745K| 1360K (0)| | 2 | NESTED LOOPS | | 1 | | 13 |00:00:00.01 | 77 | 3 | | | | | 3 | NESTED LOOPS | | 1 | 60951 | 13 |00:00:00.01 | 65 | 3 | | | | | 4 | NESTED LOOPS | | 1 | 129 | 27 |00:00:00.01 | 33 | 3 | | | | |* 5 | TABLE ACCESS BY INDEX ROWID| SETDETAILS | 1 | 24 | 12 |00:00:00.01 | 6 | 0 | | | | |* 6 | INDEX RANGE SCAN | SETDETAILS_SETID1_IX | 1 | 24 | 12 |00:00:00.01 | 3 | 0 | | | | |* 7 | TABLE ACCESS BY INDEX ROWID| F4FEEITEM | 12 | 5 | 27 |00:00:00.01 | 27 | 3 | | | | |* 8 | INDEX RANGE SCAN | F4FEEITEM_PK | 12 | 1 | 27 |00:00:00.01 | 19 | 0 | | | |
PLAN_TABLE_OUTPUT
|* 9 | INDEX RANGE SCAN | X4PAYMENT_FEEITEM_PK | 27 | 1 | 13 |00:00:00.01 | 32 | 0 | | | | |* 10 | TABLE ACCESS BY INDEX ROWID | X4PAYMENT_FEEITEM | 13 | 473 | 13 |00:00:00.01 | 12 | 0 | | | | ------------------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
5 - filter("S"."REC_STATUS"='A')
6 - access("S"."SERV_PROV_CODE"='SACRAMENTO' AND
"S"."SET_ID"='CONNIE')
7 - filter("F"."REC_STATUS"='A')
8 - access("F"."SERV_PROV_CODE"='SACRAMENTO' AND
"F"."B1_PER_ID1"="S"."B1_PER_ID1" AND
"F"."B1_PER_ID2"="S"."B1_PER_ID2" AND
PLAN_TABLE_OUTPUT
"F"."B1_PER_ID3"="S"."B1_PER_ID3") filter("F"."SERV_PROV_CODE"="S"."SERV_PROV_CODE") 9 - access("X"."SERV_PROV_CODE"='SACRAMENTO' 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"."FEEITEM_SEQ_NBR"="X"."FEEITEM_SEQ_NBR")
filter(("S"."SERV_PROV_CODE"="X"."SERV_PROV_CODE" AND
"F"."FEEITEM_SEQ_NBR"="X"."FEEITEM_SEQ_NBR"))
10 - filter((("X"."PAYMENT_FEEITEM_STATUS" IS NULL OR
"X"."PAYMENT_FEEITEM_STATUS"<>'VOIDED') AND "X"."REC_STATUS"='A' AND
"S"."REC_STATUS"="X"."REC_STATUS" AND
"F"."REC_STATUS"="X"."REC_STATUS"))
Thanks
lsllcm
Received on Sun Nov 22 2009 - 05:43:27 CST