After much deliberate (non guessing) work we think we know the reason
for the sudden change in the performance of some of our sql. I'd like to
share it with the group since so many people added to the discussion. It
may help others in the future, too.

About 3 weeks after we upgraded from 9.2 to 10.2 an often used query's
performance went from about 5 seconds to 50 seconds. We had used
export/import to do the upgrade. As I said, for about 3 weeks the
performance in 10g was similar to that experienced in 9i. Then literally
overnight the performance degraded. When we upgraded we let the default
statistics gathering job run nightly. We didn't do any stats gathering
ourselves. What we think happened was that the stats were imported from
the 9i db when the upgrade occurred. These stats had been collected
using the old analyze command. At some point the old stats were
overwritten with the dbms_stats stats. This is when we started seeing
our performance issues. The fix we have implemented in test is to delete
the stats and run the analyze command for some of the tables used in the
query. This has improved the performance. Looking back we probably
should have run the dbms_stats before the testing phase to shake out any
issues then.

Has anyone else has experienced this sort of thing in their environments?

Joe
--
http://www.freelists.org/webpage/oracle-l