Re: high cpu on query

  • From: "Jeremy Paul Schneider" <jeremy.schneider@xxxxxxxxxxxxxx>
  • To: "Joe Armstrong-Champ" <joseph.armstrong-champ@xxxxxxxxx>
  • Date: Fri, 29 Jun 2007 15:49:45 -0500

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.


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
>     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
>     both. I manually updated the stats for the affected tables in
>     copy which was refreshed yesterday but the plan didn't change. There
>     a high percentage of cpu involved in the long running query.
>     Questions:
>     - what can cause a difference in the execution plans besides the
>     and indexes?
>     - in general what can cause a query to use a lot of cpu?
>     Thanks.
>     Joe
>     --
> --
> Jeremy Schneider
> Chicago, IL

Jeremy Schneider
Chicago, IL

Other related posts: