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: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_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 = 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' 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: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

Original text of this message