follow up on high cpu

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