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

From: lsllcm <lsllcm_at_gmail.com>
Date: Wed, 18 Nov 2009 02:26:06 -0800 (PST)
Message-ID: <fed3ac04-120e-42b6-9729-f4af1a20d1ad_at_k13g2000prh.googlegroups.com>



Hi Charles and Randolf,

There is one more interesting issue.

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.

Below is test case:

Case 1: When I use A JOIN B and A JOIN C, the optimizer choose index scan on table C.

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 Elapsed: 00:00:00.06
Plan hash value: 305769021
| Id  | Operation                       | Name                 | E-
Rows | OMem | 1Mem | Used-Mem
|   0 | SELECT STATEMENT                |
|        |       |       |

PLAN_TABLE_OUTPUT


|   1 |  HASH GROUP BY                  |                      |
40516 |   745K|   745K|  668K (0)
|   2 |   NESTED LOOPS                  |
|        |       |       |
|   3 |    NESTED LOOPS                 |                      |
40516 |       |       |
|   4 |     NESTED LOOPS                |                      |
85 |       |       |
|*  5 |      TABLE ACCESS BY INDEX ROWID| SETDETAILS           |
24 |       |       |
|*  6 |       INDEX RANGE SCAN          | SETDETAILS_SETID1_IX |
24 |       |       |
|*  7 |      TABLE ACCESS BY INDEX ROWID| F4FEEITEM            |
3 |       |       |
|*  8 |       INDEX RANGE SCAN          | F4FEEITEM_PK         |
1 |       |       |
|*  9 |     INDEX RANGE SCAN            | X4PAYMENT_FEEITEM_PK |
1 |       |       |
|* 10 |    TABLE ACCESS BY INDEX ROWID  | X4PAYMENT_FEEITEM    |
475 |       |       |
---------------------------------------------------------------------------------------------------

PLAN_TABLE_OUTPUT


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
"F"."B1_PER_ID3"="S"."B1_PER_ID3")

   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
PLAN_TABLE_OUTPUT


              "F"."FEEITEM_SEQ_NBR"="X"."FEEITEM_SEQ_NBR")
       filter("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'))
Note
  • Warning: basic plan statistics not available. These are only collected when:
    • hint 'gather_plan_statistics' is used for the statement or
    • parameter 'statistics_level' is set to 'ALL', at session or system level

55 rows selected.

Elapsed: 00:00:04.05
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

Case 2: When I use A JOIN B and B JOIN C, the optimizer choose full table scan on table C.
Plan hash value: 2372462434


| Id  | Operation                       | Name                 | E-
Rows | OMem | 1Mem | Used-Mem |
|   0 | SELECT STATEMENT                |
|        |       |       |          |

PLAN_TABLE_OUTPUT


|   1 |  HASH GROUP BY                  |                      |
1 |   745K|   745K|  660K (0)|
|*  2 |   HASH JOIN                     |                      |
95859 |   744K|   744K| 1185K (0)|
|   3 |    NESTED LOOPS                 |
|        |       |       |          |
|   4 |     NESTED LOOPS                |                      |
95859 |       |       |          |
|*  5 |      TABLE ACCESS BY INDEX ROWID| SETDETAILS           |
24 |       |       |          |
|*  6 |       INDEX RANGE SCAN          | SETDETAILS_SETID1_IX |
24 |       |       |          |
|*  7 |      INDEX RANGE SCAN           | X4PAYMENT_FEEITEM_PK |
1 |       |       |          |
|*  8 |     TABLE ACCESS BY INDEX ROWID | X4PAYMENT_FEEITEM    |
3921 |       |       |          |
|*  9 |    TABLE ACCESS FULL            | F4FEEITEM            |
1173K|       |       |          |
----------------------------------------------------------------------------------------------------


PLAN_TABLE_OUTPUT


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
"S"."B1_PER_ID3"="X"."B1_PER_ID3")
PLAN_TABLE_OUTPUT

   8 - filter((("X"."PAYMENT_FEEITEM_STATUS" IS NULL OR

              "X"."PAYMENT_FEEITEM_STATUS"<>'VOIDED') AND
"X"."REC_STATUS"='A'))

   9 - filter(("F"."SERV_PROV_CODE"='SACRAMENTO' AND
"F"."REC_STATUS"='A'))
Note


  • Warning: basic plan statistics not available. These are only collected when:
    • hint 'gather_plan_statistics' is used for the statement or
    • parameter 'statistics_level' is set to 'ALL', at session or system level

54 rows selected.

Elapsed: 00:00:02.80
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

Thanks
lsllcm Received on Wed Nov 18 2009 - 04:26:06 CST

Original text of this message