Actually I'm pretty sure that GATHER_STATS_JOB does not gather system statistics. However sometimes DBA's or consultants add another job to gather them on a regular basis. (Generally I'm not a fan.) You might try rolling back your objects stats. 10g keeps 31 days of history by default and you can revert to any timestamp with the DBMS_STATS.RESTORE_*_STATS procedures. If none of the four listed items change then your plan should not change. Oh yeah... and init params means both init and session params - because the CBO does factor in a few session params like workarea_size_policy. -Jeremy On 6/29/07, Joe Armstrong-Champ <joseph.armstrong-champ@xxxxxxxxx> wrote:
GATHER_STATS_JOB runs automatically every night. It has since we upgraded so I'm assuming that system stats are being gathered. Jeremy Paul Schneider wrote: > four things can change an exec plan: > 1) change in text of sql > 2) change in init params > 3) change in object stats (tables and indexes) > 4) change in system stats > > 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@xxxxxxxxx > <mailto:joseph.armstrong-champ@xxxxxxxxx>> 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 > -- > //www.freelists.org/webpage/oracle-l > > > > > > -- > Jeremy Schneider > Chicago, IL > http://www.ardentperf.com/category/technical
-- Jeremy Schneider Chicago, IL http://www.ardentperf.com/category/technical