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")=1)
  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 (no spam) dbs26 at (no spam) 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")=1)
   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")=1)
   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

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")=1)
  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
(snip)

On Nov 3, 10:29 am, lsllcm <lsl... at (no spam) gmail.com> wrote:


Note also that it is possible that the object statistics and optimizer
parameters (FIRST_ROWS_n, for example) differ between the Oracle
versions.

David's suggestion of a 10053 trace is a good one - just keep in mind
that such traces only appear during a hard parse.

On Nov 3, 8:22 am, Charles Hooper <hooperc2... at (no spam) yahoo.com> wrote:

On Nov 3, 10:29 am, lsllcm <lsl... at (no spam) gmail.com> wrote:

Note also that it is possible that the object statistics and optimizer
parameters (FIRST_ROWS_n, for example) differ between the Oracle
versions.

David's suggestion of a 10053 trace is a good one - just keep in mind
that such traces only appear during a hard parse.

A bit of a stretch, but consider this:  rownum is assigned after the
predicate is processed, but before sorting is done.  "Bug 6438892 :
Suboptimal plan with ROWNUM predicate and NESTED LOOPS." is in the
11.1.0.6 patch set, bug not published.  So maybe there is some
mysterious optimizer bug here, set off by the order by even though
rownum is not originally a predicate.  Try patching beyond the base
release and see if the problem is still there.

jg
--

Have you run an event 10053 trace to see what information the
optimizer is using to arrive at that decision?

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")=1)
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 (no spam) dbs26 at (no spam) 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")=1) 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")=1) 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

On Nov 3, 12:44 pm, joel garry <joel-ga... at (no spam) home.com> wrote:



On Nov 3, 8:22 am, Charles Hooper <hooperc2... at (no spam) yahoo.com> wrote:

On Nov 3, 10:29 am, lsllcm <lsl... at (no spam) gmail.com> wrote:

Note also that it is possible that the object statistics and optimizer
parameters (FIRST_ROWS_n, for example) differ between the Oracle
versions.

David's suggestion of a 10053 trace is a good one - just keep in mind
that such traces only appear during a hard parse.

A bit of a stretch, but consider this:  rownum is assigned after the
predicate is processed, but before sorting is done.  "Bug 6438892 :
Suboptimal plan with ROWNUM predicate and NESTED LOOPS." is in the
11.1.0.6 patch set, bug not published.  So maybe there is some
mysterious optimizer bug here, set off by the order by even though
rownum is not originally a predicate.  Try patching beyond the base
release and see if the problem is still there.

jg
--

The OP is experiencing problems in 11.2.0.1, which does not exhibit
the ROWNUM bug that you mentioned, based on my testing.  You might be
suggesting - what if the fix of that bug caused another bug?
Interesting, quite possible.

I noticed the large number of NESTED LOOPS in his posted plan also,
which is one of the reasons why I mentioned FIRST_ROWS_n
(OPTIMIZER_MODE).  It might even be the case that the OPTIMIZER_MODE
is set to FIRST_ROWS (or maybe even CHOOSE), rather than FIRST_ROWS_1,
FIRST_ROWS_10, FIRST_ROWS_100, ALL_ROWS, etc.

lsllcm, please try setting OPTIMIZER_FEATURES_ENABLE to 11.1.0.6 in
Oracle 11.2.0.1 and re-execute the SQL statement - note that this will
force a hard parse.  How does the performance compare?  If the
performance is better, set OPTIMIZER_FEATURES_ENABLE to 11.2.0.1, add
a couple extra spaces in the SQL statement, and try your test again.
Is the performance the same as it was on 11.1.0.6 and 10.2.0.4?

On Nov 3, 12:44 pm, joel garry <joel-ga... at (no spam) home.com> wrote:





On Nov 3, 8:22 am, Charles Hooper <hooperc2... at (no spam) yahoo.com> wrote:

On Nov 3, 10:29 am, lsllcm <lsl... at (no spam) gmail.com> wrote:

Note also that it is possible that the object statistics and optimizer
parameters (FIRST_ROWS_n, for example) differ between the Oracle
versions.

David's suggestion of a 10053 trace is a good one - just keep in mind
that such traces only appear during a hard parse.

A bit of a stretch, but consider this:  rownum is assigned after the
predicate is processed, but before sorting is done.  "Bug 6438892 :
Suboptimal plan with ROWNUM predicate and NESTED LOOPS." is in the
11.1.0.6 patch set, bug not published.  So maybe there is some
mysterious optimizer bug here, set off by the order by even though
rownum is not originally a predicate.  Try patching beyond the base
release and see if the problem is still there.

jg
--

The OP is experiencing problems in 11.2.0.1, which does not exhibit
the ROWNUM bug that you mentioned, based on my testing.  You might be
suggesting - what if the fix of that bug caused another bug?
Interesting, quite possible.

I noticed the large number of NESTED LOOPS in his posted plan also,
which is one of the reasons why I mentioned FIRST_ROWS_n
(OPTIMIZER_MODE).  It might even be the case that the OPTIMIZER_MODE
is set to FIRST_ROWS (or maybe even CHOOSE), rather than FIRST_ROWS_1,
FIRST_ROWS_10, FIRST_ROWS_100, ALL_ROWS, etc.

lsllcm, please try setting OPTIMIZER_FEATURES_ENABLE to 11.1.0.6 in
Oracle 11.2.0.1 and re-execute the SQL statement - note that this will
force a hard parse.  How does the performance compare?  If the
performance is better, set OPTIMIZER_FEATURES_ENABLE to 11.2.0.1, add
a couple extra spaces in the SQL statement, and try your test again.
Is the performance the same as it was on 11.1.0.6 and 10.2.0.4?

Charles Hooper
IT Manager/Oracle DBA
K&M Machine-Fabricating, Inc.- Hide quoted text -

- Show quoted text -

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.

The trace file is too long, I use three parts.
(Snip)
***************************************
PARAMETERS USED BY THE OPTIMIZER
********************************
*************************************
PARAMETERS WITH ALTERED VALUES
******************************
Compilation Environment Dump
optimizer_features_enable = 11.1.0.6
optimizer_mode = first_rows_100
optimizer_index_cost_adj = 10
optimizer_index_caching = 90
_optimizer_cost_based_transformation = off
Bug Fix Control Environment

-

Randolf: regarding the optimizer bug that was fixed in 11.2.0.1 which
was related to ROWNUM and incorrect cardinality estimates, I was
unable to force 11.2.0.1 to reproduce the incorrect cardinality
estimates by changing the OPTIMIZER_FEATURES_ENABLE parameter to match
a lower release number.  So, I guess that some fixes are buried deep
in the code.  I even tried disabling some of the bug fixes without
success.  This seems to imply that changing the
OPTIMIZER_FEATURES_ENABLE to an older version may not always reproduce
the old optimizer behavior, which is a bit unfortunate.

Yes, Jonathan blogged about an example of this in "FBI
problem" (http://jonathanlewis.wordpress.com/2007/11/18/fbi-problem/
) But it's still usually worth a try, if just to narrow
possibilities.

Also, just to clearly emphasize to the OP, it is recommended in a lot
of places not to set OPTIMIZER_INDEX_COST_ADJ and
OPTIMIZER_INDEX_CACHING, at least until testing shows they help - it's
likely a mistake to keep the settings from earlier versions. Seehttp://richardfoote.wordpress.com/2009/09/01/optimizer_index_caching-...
for some idea of what to look for.

jg
--

Thank for your good suggestions and comments:

I have done some test based on your good suggestions: below are some
test cases

--------------------case 2--------------------------------------------
2. What plan do you get in 11.2 if you're setting
OPTIMIZER_INDEX_COST_ADJ and OPTIMIZER_INDEX_CACHING to default
values:

Result: get below execution plan. The consistent read is 3656.

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> variable p2 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:='254413'

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.00
SQL> exec :p2:='Contractor'

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.01
SQL
SQL> --alter session set optimizer_features_enable = "11.1.0.6";
SQL> alter session set optimizer_index_caching = 0;

Session altered.

Elapsed: 00:00:00.00
SQL> alter session set optimizer_index_cost_adj = 100;

Session altered.

Elapsed: 00:00:00.01
SQL> alter session set optimizer_mode = ALL_ROWS;

Session altered.

Elapsed: 00:00:00.00
SQL> --alter session set optimizer_mode = FIRST_ROWS_100;
SQL
SQL
SQL> --SELECT
SQL> SELECT /*+ index(SD SETDETAILS_SETID_IX) */
2 xfi.serv_prov_code,
3 xfi.b1_per_id1,
(snip)

no rows selected

Elapsed: 00:00:00.53

Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
3656 consistent gets
0 physical reads
0 redo size
1763 bytes sent via SQL*Net to client
381 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
0 rows processed

--------------------item 4--------------------------------------------
4. A general question: Is your application actually using the
FIRST_ROWS_100 optimizer mode properly?
Yes, most of applications are oltp type and return first of rows. So
we choose aggressive OPTIMIZER_INDEX_COST_ADJ and
OPTIMIZER_INDEX_CACHING

--------------------item 5--------------------------------------------
5. question
Why is "_optimizer_cost_based_transformation = off" specified?
Why is "optimizer_mode = first_rows_100" specified?
Why is "optimizer_index_cost_adj = 10" specified?
Why is "optimizer_index_caching = 90" specified?
[lsllcm] The _optimizer_cost_based_transformation parameter is set
useless, I remove the setting
About other three parameters, please see item 4

--------------------item 6--------------------------------------------
6. system statistics: There are about 10 scheduled jobs. I stop them
at first.

Thank you again
lsllcm