Simpler Navigation for Servers and Operating Systems
Completed: a much simpler Servers and Operating Systems section of the Community. We combined many of the older boards, so you won't have to click through so many levels to get at the information you need. Check the consolidated boards here as many sub-forums are now single boards.
General
cancel
Showing results for 
Search instead for 
Did you mean: 

Differences in cpu utilization between rx4660 and N-Class

SOLVED
Go to solution
J.D._3
Frequent Advisor

Differences in cpu utilization between rx4660 and N-Class

Hi,

I have 2 databases, one in an N-Class and another in an itanium rx4640. The N-Class has 8 cpu's 32gb memory. The itanium has 4 cpus 16gb memory. The databases are oracle9.2.0.4 and when a dba runs an identical query, the N-Class finishes 1hr 40 mins faster than the itanium. The N-Class also uses all 8 cpu's to 100% while the itanium only uses about 50% of all its cpus. The query he runs is:

exec sys.dbms_stats.gather_table_stats(ownname=>'UserName', tabname=>'TableName', degree => dbms_stats.default_degree)

Where should I look for in the system to find out why the run time has a pretty large range (1hr 40 mins) between the 2 servers? Basically, we want to understand that if we move our production database from the N-class to the itanium, will we benefit from this configuration or should we add more cpu/memory to the itaniums. Thanks J.D.
12 REPLIES
Steven E. Protter
Exalted Contributor
Solution

Re: Differences in cpu utilization between rx4660 and N-Class

The difference could result from a number of factors:

* fragmentation of the data.
* How oracle's "plans" resolve the query.
* kernel parameters on the two boxes.
* SGA differences.

I recall hearing a story of how Oracle on a couple of fast Intel boxes was getting its hindquarters kicked in queries by an old K Class box.

Turns out the K class box was executing its queries in a totally different fashion. Just because they are the same query does not mean they are running the same.

If you run a trace or take alook at how the query is being resolved, you will see why the N class box is beating the Itaniums. If you optimize the rx4640 boxes you should be able to get them to outperform the N box.

SEP
Steven E Protter
Owner of ISN Corporation
http://isnamerica.com
http://hpuxconsulting.com
Sponsor: http://hpux.ws
Twitter: http://twitter.com/hpuxlinux
Founder http://newdatacloud.com
A. Clay Stephenson
Acclaimed Contributor

Re: Differences in cpu utilization between rx4660 and N-Class

You are falling into the CPU trap. In almost every case, you are going to find that Oracle is not CPU bound but rather is I/O bound. In many database platforms the CPU is the least important component. Over the years, I've found that database bottlenecks almost always follow this pattern: 1) I/O, 2) Memory, 3) CPU - use Glance and the database performance statistics to identify your bottlenecks. If possible spread your I/O over as many SCSI paths as possible and use RAID 1/0 in favor of RAID5 (or RAID5DP/RAID6).
If it ain't broke, I can fix that.
Hein van den Heuvel
Honored Contributor

Re: Differences in cpu utilization between rx4660 and N-Class

hmmmm 1) Where both Oracles tuned about the same? SGA size?
hmmmm 2) Memory and IO systems need to have 'enough' oomph before CPU power becomes important. Did both boxes offers similar IO?
If your applition reall used thse 32Gb, then 16GB will be a serious handicap!


Hmmm 2: analyze table would not seem to be a reasonable approximation for any reasonalble application. It just does a tablescan and some counting. So with would be IO bound, and use minimal CPU. Even the memory would not be too critical.


>> Where should I look for in the system to find out why the run time has a pretty large range (1hr 40 mins

How did the CPU times, withing that elapsed time, compare?


The only reall way to presict you application behaviour on the new box.. it to try the application!
Can you get a loaner/tester and do a trial migration? You want to do that anyway no?
- to make sure you actaully knwo how to do the migration in detail no?
- to make sure you get the procedures set up just right
- to have a good estimate on how long the real/finale migration will take
- to perform a performance evaluation.


Good luck,
Hein.
Tim D Fulford
Honored Contributor

Re: Differences in cpu utilization between rx4660 and N-Class

Hi

To me this sounds like a classic IO bottleneck on the rx4640. That said, there is not enough info to say one way or the otehr!

Are the disksubsystem for the two systems the same? If different please say how.

1 - If you have OV PerformanceAgent (MeasureWare); then look at the IO statistics of the two boxes. I would focus on the service times to each LUN/Disk. also check

2 - If you do not have OVPA, look at "sar -d" stats; again looking at the IO rates of te two systems and the service times.

Regards

Tim
-
doug mielke
Respected Contributor

Re: Differences in cpu utilization between rx4660 and N-Class

I agree to look at the sar -d and -b stats.

I'd also ask if these are identical databases, how was the itanium version created. Dropped indexes or rule based vs cost based config change could be something to look at.

If export import was used, there are some paramters the DBA could have touched during the creation or the 'new' instance.
Geoff Wild
Honored Contributor

Re: Differences in cpu utilization between rx4660 and N-Class

Just a quick thought on the cpu's - remember, you are going from 8 to 4 - think of it this way - one system has 8 workers - now they may be a bit slower - but they can do more work at a given time...as far as I know, Oracle dedicates 1 cpu for "work" processess - so in effect - your N class has 7 workers and your Rx has only 3!

Another analogy - a freeway - you have 1 with 7 lanes + a maintenance lane - max speed 100 KM per hour. The Itanium freeway only has 3 lanes and a maintenance lane - max speed 120 KM per hour. Well...it does take a rocket scientist to see which freeway can push through the most traffic....

A final thought - on Itanium - did you re-compile your apps (if any)?

Rgds...Geoff
Proverbs 3:5,6 Trust in the Lord with all your heart and lean not on your own understanding; in all your ways acknowledge him, and he will make all your paths straight.
J.D._3
Frequent Advisor

Re: Differences in cpu utilization between rx4660 and N-Class

Thanks for everyone's reply. I'll try to response back as much as I can with this one thread since I got such a good turn out.

The databases are identical. Both are test machines that were restored from the same backup set from a production N-Class (same as the one in question). All the kernel parameters are the same. We did use glance to see how the cpu's are being used. The SGA settings may be a bit different since the #of cpu & amount of memory in the box. Some of the kernel parameters (ie. shmax) may be tweaked a bit toward the amount of physical memory in each box.

They are using the same physical RAID storage device (EMC CX600) using RAID5 but the NClass has its database spread over more RAID (7 RAID of 5 disks each) as opposed to the rx4640 (4 RAID of 5 disks each). So basically, smaller luns on more spindles vs bigger luns on less spindles.

The queries are done with no user sessions and our dba assures me that there is no writes done with this query. Our initial thinking is with newer technology = less but faster cpu = less $$cost that could run the same database.

But since the rx4640 is only using 50% of the cpu's shouldn't it try to use more power out of it since it has more reserved? Disk I/O on both machines is very minimal when the query is run. I'm still boggled what is preventing the database from using most of the cpus resource, making its query run longer? All comments welcome. Thanks, J.D.
Steven E. Protter
Exalted Contributor

Re: Differences in cpu utilization between rx4660 and N-Class

On the Itanium,

analyze the tables and schemas. This will help the database rebuild its statistical information and run its sql more efficiently.

SEP
Steven E Protter
Owner of ISN Corporation
http://isnamerica.com
http://hpuxconsulting.com
Sponsor: http://hpux.ws
Twitter: http://twitter.com/hpuxlinux
Founder http://newdatacloud.com
J.D._3
Frequent Advisor

Re: Differences in cpu utilization between rx4660 and N-Class

Hi Steven

I'm having the dba do that and also analyze the oracle execution plans. I'm at the same time verifying which disks the 9 datafiles in the table are hitting on each machine. I'll keep everyone posted. Thanks, J.D.
Tim D Fulford
Honored Contributor

Re: Differences in cpu utilization between rx4660 and N-Class

Hi

> They are using the same physical RAID
> storage device (EMC CX600) using RAID5 but
> the NClass has its database spread over
> more RAID (7 RAID of 5 disks each) as
> opposed to the rx4640 (4 RAID of 5 disks
> each). So basically, smaller luns on more
> spindles vs bigger luns on less spindles.

So If I'm reading this right then you are using 35 disks on N-Class & 20 on rx4640; If the N-class is falttening its disks, then the rx4640 will only do about 1/2 of the IO before it flattens its disks... If you are able to get the rx4640 box another 3 LUNs (not quite doubling its IO capacity) you should be able to get it to go at a similar speed as the N-Class.

You might want to re-assure yourself by looking at the IO stats also..

can you post "typical" sar -d on the two systems?

Regards

Tim
-
Jean-Luc Oudart
Honored Contributor

Re: Differences in cpu utilization between rx4660 and N-Class

Hi,

there is may be another possibility.
There is a bug 3048318 where some DBMS_STATS gather options can be slow. It's been confirmed for 9.2.0.4 and affect "most" platforms.
The fix is to install 9.2.0.5 patch set.

source Metalink

Regards
Jean-Luc
fiat lux
J.D._3
Frequent Advisor

Re: Differences in cpu utilization between rx4660 and N-Class

Hi Tim,

We'll be trying out another test where we would actually be using the LUNS from the NClass and move them all into the rx4640 and testing it that way. We're thinking its a little bit closer to comparing the 2 different architecture. At least this way we can isolate the issue a bit more.

Jean-Luc,
My apologies for mentioning that the databases are 9.2.0.4. They're actually 9.2.0.6. We've been running 9204 a while and I got so used to typing that instead of the actual version we're running now.

We're hoping to do this test this week. I'll keep everyone posted. Thanks again for the replies.