RE: high cpu on query

Some more for the list:  

5) Change in schema (create/drop indexes, histograms, partition/cluster/parallelism declarations, etc.)

6) Stored outline manipulation

7) Oracle CBO code change (e.g., upgrade)    

Cary Millsap

Hotsos Enterprises, Ltd.

http://www.hotsos.com

Nullius in verba  

Hotsos Symposium 2008 / March 2-6 / Dallas

Visit www.hotsos.com for curriculum and schedule details...

From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Jeremy Paul Schneider
Sent: Friday, June 29, 2007 2:02 PM
To: joseph.armstrong-champ_at_tufts.edu
Cc: ORACLE-L
Subject: Re: high cpu on query  

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

--

http://www.freelists.org/webpage/oracle-l Received on Fri Jun 29 2007 - 15:48:13 CDT