Re: performance issue after upgrade to oracle 11.2.0.1 linux 32 bit.
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