i'm pretty sure that if those four things don't change then your plan can't
change. do you have a job collecting system stats?

-Jeremy

On 6/29/07, Joe Armstrong-Champ <joseph.armstrong-champ_at_tufts.edu> wrote:
>> 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> --> http://www.freelists.org/webpage/oracle-l>>>