We upgraded to 10.2 a month ago and everything was running fine until 2 days ago when a query started running very slow in prod. It is selecting data from a view using distinct and does an 'order by', too. It runs ok in a copy of the db which was refreshed just after the upgrade. The difference in the number of rows between the 2 databases isn't that much but the execution plans are very different. The indexes are the same in both. I manually updated the stats for the affected tables in another copy which was refreshed yesterday but the plan didn't change. There is a high percentage of cpu involved in the long running query.
Questions:- what can cause a difference in the execution plans besides the stats and indexes?
- in general what can cause a query to use a lot of cpu? Thanks. Joe -- //www.freelists.org/webpage/oracle-l