Operating System - HP-UX
1748239 Members
3781 Online
108759 Solutions
New Discussion юеВ

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