performance issue after upgrade to oracle 11.2.0.1 linux 32 bit.
Date: Tue, 3 Nov 2009 07:29:46 -0800 (PST)
Message-ID: <1343a45b-d0ee-4fc2-b96e-c6902cbe1100_at_q40g2000prh.googlegroups.com>
Hi All,
I have one performance issue after upgrade to oracle 11.2.0.1 linux 32 bit.
The optimizer always choose index of "order by column", not use index of "where clause columns". Any comments are appreciated.
Below are three test cases:
Case 1: TEST on 11.2.0.1 DBs with ORDER BY ORDER BY gf_fee_apply_date Result: Choose incorrect execution plan.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing
options
SQL> set autotrace trace SQL> set timing on SQL> SQL> variable spc varchar2(15); SQL> variable bfd varchar2(40); SQL> variable efd varchar2(40); SQL> variable p1 varchar2(60); SQL> exec :spc:='SACRAMENTO';
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.00
SQL> exec :bfd:='2005-12-05';
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.01
SQL> exec :efd:='2008-12-04 23:59:59';
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.00
SQL> exec :p1:='SET07%'
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.00
SQL>
SQL> SELECT xfi.serv_prov_code, 2 xfi.b1_per_id1, 3 xfi.b1_per_id2, 4 xfi.b1_per_id3, 5 xfi.feeitem_seq_nbr, 6 xfi.invoice_nbr, 7 xfi.gf_fee_period, 8 xfi.gf_fee, 9 xfi.gf_des, 10 xfi.gf_unit, 11 xfi.gf_udes, 12 finv.invoice_date AS gf_fee_apply_date, 13 xfi.feeitem_invoice_status, 14 xfi.gf_l1, 15 xfi.gf_l2, 16 xfi.gf_l3, 17 xfi.x4feeitem_invoice_udf1, 18 xfi.x4feeitem_invoice_udf2, 19 xfi.x4feeitem_invoice_udf3, 20 xfi.x4feeitem_invoice_udf4, 21 xfi.gf_fee_schedule, 22 xfi.fee_schedule_version, 23 xfi.rec_date, 24 xfi.rec_ful_nam, 25 xfi.rec_status, 26 f4.GF_COD, 27 f4.GF_PRIORITY
28 FROM x4feeitem_invoice xfi, setdetails sd, f4invoice finv, F4FEEITEM f4
29 WHERE sd.serv_prov_code = xfi.serv_prov_code 30 AND sd.b1_per_id1 = xfi.b1_per_id1 31 AND sd.b1_per_id2 = xfi.b1_per_id2 32 AND sd.b1_per_id3 = xfi.b1_per_id3 33 AND xfi.serv_prov_code = f4.serv_prov_code 34 AND xfi.b1_per_id1 = f4.b1_per_id1 35 AND xfi.b1_per_id2 = f4.b1_per_id2 36 AND xfi.b1_per_id3 = f4.b1_per_id3 37 And xfi.feeitem_seq_nbr = f4.feeitem_seq_nbr 38 AND finv.serv_prov_code = xfi.serv_prov_code 39 AND finv.invoice_nbr = xfi.invoice_nbr 40 AND sd.serv_prov_code = :spc 41 AND upper(sd.set_id) = :p1 42 AND xfi.rec_status = 'A' 43 AND xfi.feeitem_invoice_status = 'INVOICED'44 ORDER BY gf_fee_apply_date
45 /
no rows selected
Elapsed: 00:02:15.28
Execution Plan
Plan hash value: 3748720781
| Id | Operation | Name |
Rows | Byt
es | Cost (%CPU)| Time |
| 0 | SELECT STATEMENT | |
103 | 242
05 | 9 (0)| 00:00:01 |
| 1 | NESTED LOOPS |
| |
| | |
| 2 | NESTED LOOPS | |
103 | 242
05 | 9 (0)| 00:00:01 |
| 3 | NESTED LOOPS |
| 3 | 5
79 | 8 (0)| 00:00:01 |
| 4 | NESTED LOOPS |
| 39 | 60
45 | 4 (0)| 00:00:01 |
| 5 | TABLE ACCESS BY INDEX ROWID| F4INVOICE |
60771 | 14
24K| 1 (0)| 00:00:01 |
|* 6 | INDEX RANGE SCAN | F4INVOICE_DATE_IX
| 27 |
| 1 (0)| 00:00:01 |
|* 7 | TABLE ACCESS BY INDEX ROWID| X4FEEITEM_INVOICE
| 1 | 1
31 | 1 (0)| 00:00:01 | |* 8 | INDEX RANGE SCAN | X4FEEITEM_INVOICE_NBR_IX
| 1 |
| 1 (0)| 00:00:01 | |* 9 | TABLE ACCESS BY INDEX ROWID | SETDETAILS
| 1 |
38 | 1 (0)| 00:00:01 | |* 10 | INDEX RANGE SCAN | SETDETAILS_PID_IX
| 1 |
| 1 (0)| 00:00:01 | |* 11 | INDEX UNIQUE SCAN | F4FEEITEM_PK
| 1 |
| 1 (0)| 00:00:01 |
| 12 | TABLE ACCESS BY INDEX ROWID | F4FEEITEM
| 34 | 14
28 | 1 (0)| 00:00:01 |
Predicate Information (identified by operation id):
6 - access("FINV"."SERV_PROV_CODE"=:SPC)
7 - filter("XFI"."FEEITEM_INVOICE_STATUS"='INVOICED' AND
"XFI"."REC_STATUS"='
A')
8 - access("XFI"."SERV_PROV_CODE"=:SPC AND
"FINV"."INVOICE_NBR"="XFI"."INVOIC
E_NBR")
9 - filter(UPPER("SET_ID")=:P1)
10 - access("SD"."SERV_PROV_CODE"=:SPC AND
"SD"."B1_PER_ID1"="XFI"."B1_PER_ID1
" AND
"SD"."B1_PER_ID2"="XFI"."B1_PER_ID2" AND
"SD"."B1_PER_ID3"="XFI"."
B1_PER_ID3")
11 - access("F4"."SERV_PROV_CODE"=:SPC AND
"XFI"."B1_PER_ID1"="F4"."B1_PER_ID1
" AND
"XFI"."B1_PER_ID2"="F4"."B1_PER_ID2" AND
"XFI"."B1_PER_ID3"="F4"."
B1_PER_ID3" AND
"XFI"."FEEITEM_SEQ_NBR"="F4"."FEEITEM_SEQ_NBR")
Statistics
0 recursive calls
0 db block gets
2891740 consistent gets
25708 physical reads
0 redo size
1763 bytes sent via SQL*Net to client
380 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
0 rows processed
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
2. Case 2: test on 11.2.0.1 without ORDER BY ORDER BY
gf_fee_apply_date
Result: Choose correct execution plan.
C:\>sqlplus accela/dbs_at_dbs26 @e:\tools\sql\test.sql
SQL*Plus: Release 10.2.0.4.0 - Production on Sat Oct 31 13:40:08 2009
Copyright (c) 1982, 2007, Oracle. All Rights Reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing
options
SQL> set autotrace trace SQL> set timing on SQL> SQL> variable spc varchar2(15); SQL> variable bfd varchar2(40); SQL> variable efd varchar2(40); SQL> variable p1 varchar2(60); SQL> exec :spc:='SACRAMENTO';
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.00
SQL> exec :bfd:='2005-12-05';
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.00
SQL> exec :efd:='2008-12-04 23:59:59';
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.00
SQL> exec :p1:='SET07%'
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.00
SQL>
SQL> SELECT xfi.serv_prov_code, 2 xfi.b1_per_id1, 3 xfi.b1_per_id2, 4 xfi.b1_per_id3, 5 xfi.feeitem_seq_nbr, 6 xfi.invoice_nbr, 7 xfi.gf_fee_period, 8 xfi.gf_fee, 9 xfi.gf_des, 10 xfi.gf_unit, 11 xfi.gf_udes, 12 finv.invoice_date AS gf_fee_apply_date, 13 xfi.feeitem_invoice_status, 14 xfi.gf_l1, 15 xfi.gf_l2, 16 xfi.gf_l3, 17 xfi.x4feeitem_invoice_udf1, 18 xfi.x4feeitem_invoice_udf2, 19 xfi.x4feeitem_invoice_udf3, 20 xfi.x4feeitem_invoice_udf4, 21 xfi.gf_fee_schedule, 22 xfi.fee_schedule_version, 23 xfi.rec_date, 24 xfi.rec_ful_nam, 25 xfi.rec_status, 26 f4.GF_COD, 27 f4.GF_PRIORITY
28 FROM x4feeitem_invoice xfi, setdetails sd, f4invoice finv, F4FEEITEM f4
29 WHERE sd.serv_prov_code = xfi.serv_prov_code 30 AND sd.b1_per_id1 = xfi.b1_per_id1 31 AND sd.b1_per_id2 = xfi.b1_per_id2 32 AND sd.b1_per_id3 = xfi.b1_per_id3 33 AND xfi.serv_prov_code = f4.serv_prov_code 34 AND xfi.b1_per_id1 = f4.b1_per_id1 35 AND xfi.b1_per_id2 = f4.b1_per_id2 36 AND xfi.b1_per_id3 = f4.b1_per_id3 37 And xfi.feeitem_seq_nbr = f4.feeitem_seq_nbr 38 AND finv.serv_prov_code = xfi.serv_prov_code 39 AND finv.invoice_nbr = xfi.invoice_nbr 40 AND sd.serv_prov_code = :spc 41 AND upper(sd.set_id) = :p1 42 AND xfi.rec_status = 'A' 43 AND xfi.feeitem_invoice_status = 'INVOICED'44 -- ORDER BY gf_fee_apply_date
45 /
no rows selected
Elapsed: 00:00:00.06
Execution Plan
Plan hash value: 1421766072
| Id | Operation | Name |
Rows | Byt
es | Cost (%CPU)| Time |
| 0 | SELECT STATEMENT | |
101 | 369
66 | 5 (20)| 00:00:01 |
| 1 | NESTED LOOPS |
| |
| | |
| 2 | NESTED LOOPS | |
101 | 369
66 | 5 (20)| 00:00:01 |
| 3 | NESTED LOOPS |
| 3 | 9
72 | 4 (25)| 00:00:01 |
|* 4 | HASH JOIN |
| 3 | 9
00 | 3 (34)| 00:00:01 |
| 5 | TABLE ACCESS BY INDEX ROWID| SETDETAILS |
107 | 40 66 | 1 (0)| 00:00:01 | |* 6 | INDEX RANGE SCAN | SETDETAILS_SETID_IX | 107 | | 1 (0)| 00:00:01 | |* 7 | TABLE ACCESS BY INDEX ROWID| X4FEEITEM_INVOICE | 2680 | 3 42K| 1 (0)| 00:00:01 | |* 8 | INDEX RANGE SCAN | X4FEEITEM_INVOICE_NBR_IX | 255K| | 1 (0)| 00:00:01 |
| 9 | TABLE ACCESS BY INDEX ROWID | F4INVOICE
| 1 |
24 | 1 (0)| 00:00:01 | |* 10 | INDEX UNIQUE SCAN | F4INVOICE_PK
| 1 |
| 1 (0)| 00:00:01 | |* 11 | INDEX UNIQUE SCAN | F4FEEITEM_PK
| 1 |
| 1 (0)| 00:00:01 |
| 12 | TABLE ACCESS BY INDEX ROWID | F4FEEITEM
| 34 | 14
28 | 1 (0)| 00:00:01 |
Predicate Information (identified by operation id):
4 - access("SD"."SERV_PROV_CODE"="XFI"."SERV_PROV_CODE" AND
"SD"."B1_PER_ID1"
="XFI"."B1_PER_ID1"
AND "SD"."B1_PER_ID2"="XFI"."B1_PER_ID2" AND
"SD"."B1_PER_ID3"="XF
I"."B1_PER_ID3")
6 - access("SD"."SERV_PROV_CODE"=:SPC AND UPPER("SET_ID")=:P1)
7 - filter("XFI"."FEEITEM_INVOICE_STATUS"='INVOICED' AND
"XFI"."REC_STATUS"='
A')
8 - access("XFI"."SERV_PROV_CODE"=:SPC)
10 - access("FINV"."SERV_PROV_CODE"=:SPC AND
"FINV"."INVOICE_NBR"="XFI"."INVOI
CE_NBR")
11 - access("F4"."SERV_PROV_CODE"=:SPC AND
"XFI"."B1_PER_ID1"="F4"."B1_PER_ID1
" AND
"XFI"."B1_PER_ID2"="F4"."B1_PER_ID2" AND
"XFI"."B1_PER_ID3"="F4"."
B1_PER_ID3" AND
"XFI"."FEEITEM_SEQ_NBR"="F4"."FEEITEM_SEQ_NBR")
Statistics
0 recursive calls
0 db block gets
3 consistent gets
3 physical reads
0 redo size
1763 bytes sent via SQL*Net to client
380 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
0 rows processed
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
C:\>
3. Case 3: TEST on 11.1.0.6/10.2.0.4 DBs Result: Choose correct execution plan.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing
options
SQL> set autotrace trace
SP2-0618: Cannot find the Session Identifier. Check PLUSTRACE role is
enabled
SP2-0611: Error enabling STATISTICS report
SQL> set timing on SQL> SQL> variable spc varchar2(15); SQL> variable bfd varchar2(40); SQL> variable efd varchar2(40); SQL> variable p1 varchar2(60); SQL> exec :spc:='SACRAMENTO';
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.01
SQL> exec :bfd:='2005-12-05';
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.00
SQL> exec :efd:='2008-12-04 23:59:59';
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.01
SQL> exec :p1:='SET07%'
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.00
SQL>
SQL> SELECT xfi.serv_prov_code, 2 xfi.b1_per_id1, 3 xfi.b1_per_id2, 4 xfi.b1_per_id3, 5 xfi.feeitem_seq_nbr, 6 xfi.invoice_nbr, 7 xfi.gf_fee_period, 8 xfi.gf_fee, 9 xfi.gf_des, 10 xfi.gf_unit, 11 xfi.gf_udes, 12 finv.invoice_date AS gf_fee_apply_date, 13 xfi.feeitem_invoice_status, 14 xfi.gf_l1, 15 xfi.gf_l2, 16 xfi.gf_l3, 17 xfi.x4feeitem_invoice_udf1, 18 xfi.x4feeitem_invoice_udf2, 19 xfi.x4feeitem_invoice_udf3, 20 xfi.x4feeitem_invoice_udf4, 21 xfi.gf_fee_schedule, 22 xfi.fee_schedule_version, 23 xfi.rec_date, 24 xfi.rec_ful_nam, 25 xfi.rec_status, 26 f4.GF_COD, 27 f4.GF_PRIORITY
28 FROM x4feeitem_invoice xfi, setdetails sd, f4invoice finv, F4FEEITEM f4
29 WHERE sd.serv_prov_code = xfi.serv_prov_code 30 AND sd.b1_per_id1 = xfi.b1_per_id1 31 AND sd.b1_per_id2 = xfi.b1_per_id2 32 AND sd.b1_per_id3 = xfi.b1_per_id3 33 AND xfi.serv_prov_code = f4.serv_prov_code 34 AND xfi.b1_per_id1 = f4.b1_per_id1 35 AND xfi.b1_per_id2 = f4.b1_per_id2 36 AND xfi.b1_per_id3 = f4.b1_per_id3 37 And xfi.feeitem_seq_nbr = f4.feeitem_seq_nbr 38 AND finv.serv_prov_code = xfi.serv_prov_code 39 AND finv.invoice_nbr = xfi.invoice_nbr 40 AND sd.serv_prov_code = :spc 41 AND upper(sd.set_id) = :p1 42 AND xfi.rec_status = 'A' 43 AND xfi.feeitem_invoice_status = 'INVOICED'44 ORDER BY gf_fee_apply_date
45 /
Elapsed: 00:00:00.51
Execution Plan
Plan hash value: 2996231674
| Id | Operation | Name |
Rows | Bytes
| Cost (%CPU)| Time |
| 0 | SELECT STATEMENT | |
417 | 90072
| 16 (13)| 00:00:01 |
| 1 | SORT ORDER BY | |
417 | 90072
| 16 (13)| 00:00:01 |
|* 2 | HASH JOIN | |417 | 90072
| 15 (7)| 00:00:01 |
| 3 | NESTED LOOPS |
| |
| | |
| 4 | NESTED LOOPS | |
29 | 5626
| 11 (0)| 00:00:01 |
| 5 | NESTED LOOPS | |
3 | 441
| 8 (0)| 00:00:01 |
| 6 | TABLE ACCESS BY INDEX ROWID| SETDETAILS |
3 | 105
| 2 (0)| 00:00:01 |
|* 7 | INDEX RANGE SCAN | SETDETAILS_SETID_IX |
3 |
| 1 (0)| 00:00:01 |
|* 8 | TABLE ACCESS BY INDEX ROWID| X4FEEITEM_INVOICE |
1 | 112
| 2 (0)| 00:00:01 |
|* 9 | INDEX RANGE SCAN | X4FEEITEM_INVOICE_PK |
1 |
| 1 (0)| 00:00:01 |
|* 10 | INDEX UNIQUE SCAN | F4FEEITEM_PK |
1 |
| 0 (0)| 00:00:01 |
| 11 | TABLE ACCESS BY INDEX ROWID | F4FEEITEM |
10 | 470
| 1 (0)| 00:00:01 |
| 12 | TABLE ACCESS BY INDEX ROWID | F4INVOICE |
9453 | 203K
| 3 (0)| 00:00:01 |
|* 13 | INDEX RANGE SCAN | F4INVOICE_DATE_IX |
9453 |
| 2 (0)| 00:00:01 |
Predicate Information (identified by operation id):
2 - access("FINV"."SERV_PROV_CODE"="XFI"."SERV_PROV_CODE" AND
"FINV"."INVOICE_NBR"="XFI"."INVOICE_NBR")
7 - access("SD"."SERV_PROV_CODE"=:SPC AND UPPER("SET_ID")=:P1)
8 - filter("XFI"."FEEITEM_INVOICE_STATUS"='INVOICED' AND
"XFI"."REC_STATUS"='
A')
9 - access("XFI"."SERV_PROV_CODE"=:SPC AND
"SD"."B1_PER_ID1"="XFI"."B1_PER_ID
1" AND
"SD"."B1_PER_ID2"="XFI"."B1_PER_ID2" AND
"SD"."B1_PER_ID3"="XFI"."
B1_PER_ID3")
10 - access("F4"."SERV_PROV_CODE"=:SPC AND
"XFI"."B1_PER_ID1"="F4"."B1_PER_ID1
" AND
"XFI"."B1_PER_ID2"="F4"."B1_PER_ID2" AND
"XFI"."B1_PER_ID3"="F4"."
B1_PER_ID3" AND
"XFI"."FEEITEM_SEQ_NBR"="F4"."FEEITEM_SEQ_NBR")
13 - access("FINV"."SERV_PROV_CODE"=:SPC)
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release
11.1.0.6.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing
options
Received on Tue Nov 03 2009 - 09:29:46 CST