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@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
>     --
>     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

Other related posts: