Processor affinity

[Alan Casey]

Hi,

I have oracle 8i running on an A500 HP-UX 11i server, we seem to have a CPU bottleneck on the system which is slowing oracle processes.

However, it looks like the Oracle processes will only bind to a single processor, this system has 2 processors, and one of them is almost always at 100% when running queries, the other is barely used at all.

The PSET software is NOT installed on this system, and a mpsched -qp on all of the Oracle server processes shows no binding.

Can anyone help explain why Oracle processes are behaving like this?
I know that an mpctl call by the Oracle software can do this, but I have no way to tell if this is the case.

[Mark Greene_1]

Interestingly, the word "affinity" does not appear in any of the man pages. However, the on-line manual does have a small section about it:

http://docs.hp.com/hpux/onlinedocs/B3782-90716/00/00/34-con.html

HTH
mark

[George Petrides_1]

Hi, we had the same exact problem about 2.5 years ago with a K460 running HP-UX 11, Oracle 8.something and People Soft financials. Instead of spreading processes on 4 CPUS all non-root processes were running on one CPU. If my memory serves me correctly, it was fixed by a patch...

[Edward Carey]

Hi Alan,

I wonder if you find out what happne to your server...??? it seems like I have similar problem running Oracle9i on HPUX 11i on a RP7410server...

Please help...

Thanks,
Ed

[Bill Hassell]

It isn't a problem at all, it's perfectly normal. A single process can never be shared across processors. That's basic computer programming 101. To use the other processor(s), you must:

- run more processes.

- rewrite the application to use threads.

A thread is a mini-process which is created and monitored by the main process. An easy example of a threaded application is your favorite browser. While things are downloading, you can change options, start something printing, even change your mind and go to another URL. The overall browser code is monitoring events (mouse movements, clicks, keystrokes, etc) and starts an internal routine that performs this subtask.

So until Oracle is configured to run threaded (or if it is already, it apparently doesn't have more than one long term task to accomplish), you'll see just one processor being used.

[Steve Lewis]

It doesn't have to be Oracle itself that uses mpctl(). You can write a program yourself to move processes around. It just has to be run as root. This obviously won't be very simple when processes finish quickly, but some, like the log writer, listener etc can be affinitied to the empty cpu, to free up the other cpu for SQLs.

Use top to work out which processes you want to move, then use the program below to move them.

/* set_cpu.c assigns a process ($1) to cpu($2) */
/* note that cpus are numbered 0,1,2,3... */

#include
#include

int main(int argc, char *argv[])
{
int pid, cpu, numcpus;

if (argc<2) return 10;
if ((pid=atoi(argv[1])) < 0) return 11;
if ((cpu=atoi(argv[2])) < 0) return 12;

/* find out how many cpus are installed */
numcpus = mpctl(MPC_GETNUMSPUS,0,0);

/* if numcpus=4, cant request cpu 4, since numbered 0,1,2,3 */
if (cpu >= numcpus) return 13;

/* now try to assign the pid to the cpu and
return its number */

return mpctl(MPC_SETPROCESS,cpu,pid);

}

[Michael Schulte zur Sur]

Hi Bill,

it looks like the Oracle processes

this sounds like plural too me!

However, how many oracle processes and how much do they use the one cpu?

Are the clients local or remote?

Michael