Hi Tom,
A tricky question, recently we upgraded our systems to 11.2.0.4 and started to observe some queries taking much longer (from mins to 10+ hours).
On analysing the taces / explain we found the access path had changed from the previous one, so if initiated a full stats update, but to no benefit.

A copy of database was made prior to upgrade and we ran the same query there which worked as it had worked before.
Parameter comparison resulted no difference.

On analysing the 2 traces (optimiser) we found that the only difference on the 2 DB's on that tables / indexes for the particular index (BSAD~1) is different which forces the optimiser to select it over primary index is the value of RESC_IO (in bold).
The extract is place here (Production : bad perfomance, Non Prod - good performance)

The key question is
1. Can we influence the RESC_IO value
2. How is this resource cost built by Oracle in first place
3. What other steps can we take to influence this to force selection of BSAD~0 index (without using hints).
If required I can attache the full trace of both the systems.


Prod
DB1
Index: BSAD~0
resc_io: 7.000000 resc_cpu: 96689
ix_sel: 7.1054e-09 ix_sel_with_filters: 4.4767e-14
***** Logdef predicate Adjustment ******
Final IO cst 0.000000 , CPU cst 12300
***** End Logdef Adjustment ******
Cost: 1.401104 Resp: 1.401104 Degree: 1
ColGroup Usage:: PredCnt: 3 Matches Full: Partial: #7 (1 2 3 )
ColGroup Usage:: PredCnt: 3 Matches Full: Partial: #7 (1 2 3 )
Access Path: index (RangeScan)
Index: BSAD~1
resc_io: 7.000000 resc_cpu: 101437
ix_sel: 7.1054e-09 ix_sel_with_filters: 7.1054e-09
Cost: 1.400923 Resp: 1.400923 Degree: 1
Considering index with inlist as filter
ColGroup Usage:: PredCnt: 2 Matches Full: Partial:
ColGroup Usage:: PredCnt: 3 Matches Full: Partial: #7 (1 2 3 )
Access Path: index (RangeScan)
Index: BSAD~1
resc_io: 6.000000 resc_cpu: 88046
ix_sel: 8.5975e-07 ix_sel_with_filters: 7.1054e-09
***** Logdef predicate Adjustment ******
Final IO cst 0.000000 , CPU cst 0
***** End Logdef Adjustment ******

NonProd
Access Path: index (RangeScan)
Index: BSAD~0
resc_io: 7.000000 resc_cpu: 122089
ix_sel: 6.5239e-07 ix_sel_with_filters: 4.5333e-12
***** Logdef predicate Adjustment ******
Final IO cst 0.000000 , CPU cst 12300
***** End Logdef Adjustment ******
Cost: 7.069318 Resp: 7.069318 Degree: 1
ColGroup Usage:: PredCnt: 3 Matches Full: Partial: #5 (1 2 3 ) Sel: 6.5239e-07
ColGroup Usage:: PredCnt: 3 Matches Full: Partial: #5 (1 2 3 ) Sel: 6.5239e-07
Access Path: index (RangeScan)
Index: BSAD~1
resc_io: 59.000000 resc_cpu: 1052825
ix_sel: 6.5239e-07 ix_sel_with_filters: 6.5239e-07
Cost: 59.042709 Resp: 59.042709 Degree: 1
Considering index with inlist as filter
ColGroup Usage:: PredCnt: 2 Matches Full: Partial:
ColGroup Usage:: PredCnt: 3 Matches Full: Partial: #5 (1 2 3 ) Sel: 6.5239e-07
Access Path: index (RangeScan)
Index: BSAD~1
resc_io: 58.000000 resc_cpu: 936485
ix_sel: 9.3269e-07 ix_sel_with_filters: 6.5239e-07
***** Logdef predicate Adjustment ******
Final IO cst 0.000000 , CPU cst 0
***** End Logdef Adjustment ******