Re: high cpu on query

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_at_tufts.edu> 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_at_tufts.edu
> > <mailto: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
> >
> >
> >
> >
> >
> > --
> > Jeremy Schneider
> > Chicago, IL
> > http://www.ardentperf.com/category/technical
>

-- 
Jeremy Schneider
Chicago, IL
http://www.ardentperf.com/category/technical

--
http://www.freelists.org/webpage/oracle-l