Re: CPU being hit hard by a query

Harry_Boswell_at_deq.state.ms.us wrote in message news:<ihaci0pkeuhsumhgdv3qo7uibim8krmpbo_at_4ax.com>...
> I have a query that runs on three servers, three different instances.
> On my Sun E4500 (4 processor), the query takes about two minutes. On
> a Sun 280R (single processor), it takes about 2 minutes. On a Sun
> E250 (single processor), it basically doesn't end. I've let it go as
> long as 1 1/2 hours before canceling it. (I realize the E250 is much
> less box than the others.)
>
> The instances are configured pretty much the same. But I've noticed
> that when the query runs on the E250, vmstat shows user CPU
> utilization hovering around 95%. This doesn't happen on the other
> boxes - the 280R has a brief spike at around 40% utilization, the 4500
> doesn't even notice the query. Is there something I can do, from an
> Oracle tuning perspective, to lessen the impact on CPU resources on
> the E250? This seems to be the bottleneck that's keeping this from
> running.
>
> Thanks,
> Harry Boswell

I guess it's a lack of statistics for the tables involved on the query.

You can try to check the last_analyzed column on user_tables view

select last_analyzed from user_tables where table_name in ('table1','table2',...'table n');

if the last_analyzed column is null or it is extremly old you can try (re)analyzing the tables to get up-to-date statistics in this way

analyze table table1 estimate statistics for table for all indexes for all indexed columns sample 30 percent.

The retry your query.

Anyway the E250 and the E4500 have the same class of processor and a noparallel query use just 1 processor to process it so if the server is using 100% of a CPU to processs the query in vmstat you will see a cpu usage of 25% on 4 way box. To get more significan results you should use mpstat.

Hope this help and always sorry for my bad english Received on Mon Aug 23 2004 - 10:37:37 CDT