topic Re: Differences in cpu utilization between rx4660 and N-Class in Operating System - HP-UX

Differences in cpu utilization between rx4660 and N-Class

J.D._3 — Mon, 28 Mar 2005 20:53:57 GMT

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.

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

Steven E. Protter — Mon, 28 Mar 2005 22:21:55 GMT

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

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

A. Clay Stephenson — Mon, 28 Mar 2005 22:43:02 GMT

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

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

Hein van den Heuvel — Tue, 29 Mar 2005 00:20:53 GMT

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.

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

Tim D Fulford — Tue, 29 Mar 2005 06:53:05 GMT

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

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

doug mielke — Tue, 29 Mar 2005 08:14:47 GMT

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.

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

Geoff Wild — Tue, 29 Mar 2005 09:26:16 GMT

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

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

J.D._3 — Tue, 29 Mar 2005 12:37:48 GMT

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.

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

Steven E. Protter — Tue, 29 Mar 2005 12:56:59 GMT

On the Itanium,

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

SEP

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

J.D._3 — Tue, 29 Mar 2005 13:16:24 GMT

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.

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

Tim D Fulford — Tue, 29 Mar 2005 15:25:37 GMT

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

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

Jean-Luc Oudart — Wed, 30 Mar 2005 05:54:10 GMT

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

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

J.D._3 — Wed, 30 Mar 2005 12:03:59 GMT

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.