Re: performance issue after upgrade to oracle 11.2.0.1 linux 32 bit

From: lsllcm <lsllcm_at_gmail.com>
Date: Sun, 22 Nov 2009 03:41:13 -0800 (PST)
Message-ID: <bbee9e63-245a-487d-be7f-25afc28f10ae_at_z4g2000prh.googlegroups.com>

Hi Randolf,

Thanks for your comments, below are bad 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_FEE

  7 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 = X.B1_PER_ID1
 18     AND F.B1_PER_ID2 = X.B1_PER_ID2
 19     AND F.B1_PER_ID3 = X.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' OR

 24 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:13.39
SQL> SELECT * FROM TABLE (DBMS_XPLAN.DISPLAY_CURSOR (NULL,NULL,'ALLSTATS LAST')) ; PLAN_TABLE_OUTPUT

---

SQL_ID 2t5d84xnrdhzm, 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 =
X.B1_PER_ID1 AND F.B1_PER_ID2 = X.B1_PER_ID2 AND F.B1_PER_ID3 = PLAN_TABLE_OUTPUT

---

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) 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: 2372462434

---

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:13.27 |   34058 |  33097 |       |
|          |
|   1 |  HASH GROUP BY                  |                      |
1 |      1 |      4 |00:00:13.27 |   34058 |  33097 |   745K|   745K|
1165K (0)|
|*  2 |   HASH JOIN                     |                      |
1 |  91293 |     13 |00:00:13.98 |   34058 |  33097 |   744K|   744K|
1427K (0)|
|   3 |    NESTED LOOPS                 |                      |
1 |        |     28 |00:00:00.01 |      62 |      0 |       |
|          |
|   4 |     NESTED LOOPS                |                      |
1 |  95522 |     44 |00:00:00.01 |      27 |      0 |       |
|          |
|*  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 |      INDEX RANGE SCAN           | X4PAYMENT_FEEITEM_PK |
12 |      1 |     44 |00:00:00.01 |      21 |      0 |       |
|          |
|*  8 |     TABLE ACCESS BY INDEX ROWID | X4PAYMENT_FEEITEM    |
44 |   3907 |     28 |00:00:00.01 |      35 |      0 |       |
|          |

PLAN_TABLE_OUTPUT

---

|*  9 |    TABLE ACCESS FULL            | F4FEEITEM            |
1 |   1173K|   1174K|00:00:10.84 |   33996 |  33097 |       |
|          |
------------------------------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---

   2 - access("F"."SERV_PROV_CODE"="S"."SERV_PROV_CODE" 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")

   5 - filter("S"."REC_STATUS"='A')
   6 - access("S"."SERV_PROV_CODE"='SACRAMENTO' AND
"S"."SET_ID"='CONNIE')

   7 - 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
PLAN_TABLE_OUTPUT

---

              "S"."B1_PER_ID3"="X"."B1_PER_ID3")
       filter("S"."SERV_PROV_CODE"="X"."SERV_PROV_CODE")
   8 - 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"))

   9 - filter(("F"."SERV_PROV_CODE"='SACRAMENTO' AND
"F"."REC_STATUS"='A'))
Received on Sun Nov 22 2009 - 05:41:13 CST