I'm using VFP SQL passthrough via Oracle ODBC to Oracle (7 and 8). A customer was reporting very slow performance (around 50 seconds). I tested *my* installation with twice the number of records and managed to get performance of 24 seconds.

I changed the prefetch count to various values and, with a setting of 120 managed to reduce the time by ~50% to 12 seconds.

I then changed the setting BACK to a prefetch value of 1 and added a DO COVERAGE TO... to one of my units. Performance increased so that the time took only 3.2 seconds. I removed the DO COVERAGE lines and performance stayed the same. I have rebooted, thinking that perhaps the ODBC driver had got stuck at 120 for the prefetch, but no change - performance is still staggeringly faster.

Question: has Oracle detected the increase prefetch and updated its statistical tables used for internal optimisation, or something? I'd really like to know what is going on, in case it undoes itself!

Cross-posted to VFP and Oracle forums.