performance issue after upgrade to oracle 11.2.0.1 linux 32 bit
Date: Fri, 20 Nov 2009 06:22:24 -0800 (PST)
Message-ID: <a0db0f61-5250-45c7-8d12-9339e8844446_at_u25g2000prh.googlegroups.com>
Hi Friends,
I have one insteresting performance issue about 11.2.0.1. Any comments are appreciated.
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 Fri Nov 20 2009 - 08:22:24 CST