2 x Itanium 1.66ghz dual core processors

[enworb]

Hello Peoples

We have an installation of R12 EBS /11g Oracle.

When a long running, hard hitting process is run via EBS/Oracle as a single threaded program one of the processors on 'top' shows it is max'd out and the others sit there doing diddly.

System: server2 Mon Jun 7 14:42:30 2010
Load averages: 0.33, 0.35, 0.36
499 processes: 437 sleeping, 62 running
Cpu states:
CPU LOAD USER NICE SYS IDLE BLOCK SWAIT INTR SSYS
0 0.22 0.2% 0.0% 1.2% 98.6% 0.0% 0.0% 0.0% 0.0%
2 0.94 100.0% 0.0% 0.0% 0.0% 0.0% 0.0% 0.0% 0.0%
4 0.05 2.4% 0.0% 0.4% 97.2% 0.0% 0.0% 0.0% 0.0%
6 0.13 2.2% 0.0% 0.6% 97.2% 0.0% 0.0% 0.0% 0.0%
--- ---- ----- ----- ----- ----- ----- ----- ----- -----
avg 0.33 26.2% 0.0% 0.6% 73.2% 0.0% 0.0% 0.0% 0.0%

System Page Size: 4Kbytes
Memory: 10480060K (3721552K) real, 11396632K (4201904K) virtual, 2446696K free
Page# 1/56

CPU TTY PID USERNAME PRI NI SIZE RES STATE TIME %WCPU %CPU COMMAND
2 ? 14954 oradb2 178 20 1980M 91856K run 52:19 100.75 100.57 oracledb2
6 ? 9541 oradb2 178 20 1922M 34144K sleep 12:37 2.34 2.33 oracledb2
4 ? 5301 oradb3 178 20 1480M 81356K sleep 25:32 0.93 0.93 ora_m000_RS


Is this normal behaviour ? I would have thought that even if Oracle sends a single threaded process, HP-UX 11.31 would spread the load across all processors or am I mistaken ?

Thanks for your time

Dave

[Duncan Edmonstone]

Dave,

Why would HP-UX want to do that?

Moving the process so it was scheduled on another CPU wouldn't make it run any faster - in fact the processing effort involved in moving it to another CPU would actually make it slower.

Think about a 4 lane highway with 1 car on it... swapping lanes doesn't let the car go any faster...

Of course for multi-thread or multi-process apps, the load can be spread amongst CPUs (multiple cars in different lanes on the highway)

HTH

Duncan

[enworb]

Duncan

Thanks. I spoke to another HP guy and he seemed to think there could be a problem with the server. Although he didn't say what.

We have a process that looks to be processor bound, it is max'd out and the process takes 14 hours. Seems a bit of a waste that the other 3 processes or sitting there doing nothing.

Do I need faster CPUs in order to process quicker ? I cannot make the application or the process multi-threaded.

Thanks again

Dave

[Tim Nelson]

2 ways to make single threaded cpu bound process faster.

1) write more efficient code

2) get faster processors.

[Bill Hassell]

Another analogy:

If one woman can make a baby in 9 months, why can't 9 women working really hard make one in just 1 month?

The program oracledb2 is CPU bound (meaning that the program is computing like crazy). Since an Oracle database engine is designed to be multi-threaded, oracledb2 must be a simple program where each instruction must be completed before moving on to the next instruction. So while it might seem that a "load" should be able to be shared, a program must be totally redesigned to split separable tasks into individual streams or threads. If oracledb2 does not offer a config change for threads, only a faster processor will make it run faster.

But as always, talk to the developer or supplier of the program to see if it is running as expected.

[Duncan Edmonstone]

Dave,

So the "brute force and ignorance" answer is that yes you need a faster CPU... of course there's not much that goes faster than what you have on HP-UX anyway (the new Tukwila processor cores can boost right up to 1.867GHz and that combined with other changes might gain you 10-15% over what you have here, but that's probably not enough to fix what you have).

The more intelligent answer is to figure out what this process is actually doing and try and get it to do it more efficiently. It looks like an Oracle database shadow process for a database called "db2" - is that correct? If that's the case then I think you have another plan of attack... looking at the CPU lines in your otiginal post, I note that the CPU that is busy is spending 100% of its time in user space and 0.0% in system space... (was this just at this particular snapshot we see here, or is it consistently spending all its time in user space?) this suggests there's not much you can do to the OS kernel or IO stack to effect this process (it almost suggests the process is just spinning on some internal user space lock in Oracle code!) - so at this stage you need to figure out what that Oracle process is actually doing - do you have DBAs with the knowledge to gather Oracle performance data such as STATSPACK to look at this?

Either way at this stage I would be focussing on the application and database - not on the OS or hardware...

HTH

Duncan

[Dennis Handly]

>Bill: Since an Oracle database engine is designed to be multi-threaded

I don't believe Oracle DB is using threads on HP-UX. It still uses multiple processes.

[enworb]

Hi Duncan

I am a DBA and not that hot on HP-UX. I am working here with another DBA.

We are trying to import 60,000 purchase orders perhaps about 250,000 lines in total, it's quite a heavy process. The database is in good heath. We have used AWR (the new statspack) to see what the process is doing. We still see CPU wait events. It works really quick if we load say 3000 PO's and then the performance tails off and gets worse. If I tusc the pid that is doing the work the output shoots up the screen to begin with and then slows right down, the CPU is max'd out all the time.

It does spend most of it's time in user space.

Thanks for your advice.

[enworb]

the tusc ouput speeds up and slows down which leads me to believe (possibly incorrectly) that this is a CPU problem.

[Dennis Handly]

>the tusc output speeds up and slows down which leads me to believe that this is a CPU problem.

What do you mean by speeds up and slows down? You mean you aren't getting as many system calls per second?

This can be good if you aren't waiting, it means you are using the CPU.

So what are the calls being done?