high cpu on query

  • From: Joe Armstrong-Champ <joseph.armstrong-champ@xxxxxxxxx>
  • To: ORACLE-L <oracle-l@xxxxxxxxxxxxx>
  • Date: Fri, 29 Jun 2007 12:56:40 -0400

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


Other related posts: