Nagi

Thank you for your response. I'm confident that the problem does not relate to optimizer statistics. Monitoring is turned on for all tables (expect SYS and SYSTEM of course) and we gather stale every hour. Also, I think that the plan the optimiser is coming up with is the best it can. It uses the index which is built on all three columns that appear in the WHERE clause, and therefore must perform a range scan and a table access by rowid. This is why I am attempting to tune IO rather than the SQL (we can't retwrite the SQL of course, being third party app). The only alternative access path I can think of is a FTS and given the table has almost 5 million rows this is slower. Trace Analyzer suggests that the bottle neck is not reading the index itself:

Details for wait event Times Count Max. Total Blocks
'db file sequential read (single block scan)' Waited Zero Time Wait Waited Accessed
----------------------------------------------------------------- --------- --------- --------- --------- ---------
proddta.f4211.................................................... 139990 72438 0.56 821.72 139990
proddta.f4211_6.................................................. 2136 735 0.07 16.94 2136
----------------------------------------------------------------- --------- --------- --------- --------- ---------
total............................................................ 142126 73173 0.56 838.66 142126

but reading the table (hence the improved performance after the re-org of the table). That is why I am attempting to tune IO or eliminate the table access altogether.

Thanks

Austin