Oracle / CPU usages

[Samuel Scott]

Problem: All Oracle (10G) processes run on only 1 of 4 cpus on my RP7410,OS 11.11. CPUs 1 and 2 are idle 100% of the time. CPU 3 averages 95% usage and often goes to 100%. Users are complaining of slow responses. Can anyone tell me why Oracle (or the HP scheduler) refuses to use the other CPUs? We have a sister system with the exact kernel configuration that is not seeing these problems. I verified the patches are the same also. Here is a 10-hour summary from sar:

cpu %usr %sys %wio %idle
Average 0 0 0 0 99
Average 1 0 0 0 100
Average 2 0 0 0 100
Average 3 98 1 1 0

I've seen this problem mentioned before in the forums

[Steven E. Protter]

Shalom,

You may find solutions by searching the previous threads in ITRC.

Checklist:
* OS patches - there may be new ones
* Oracle patches - this might be an oracle defect
* SGA settings
* Kernel settings.

There might be something different on this system causing the problem. Everything about this tells me to check for an application defect.

SEP

[Duncan Edmonstone]

Check you don't have any processor sets defined on this system

psrset

will show if you have more than the default PSET defined.

HTH

Duncan

[Dennis Handly]

You use more than one CPU if you have multiple processes or threads. Or if the system is so busy the process goes from CPU to CPU.

>the exact kernel configuration that is not seeing these problems

Is it running the same applications?
Are you using any special scheduling software that would bind the application to just that CPU?

[Hein van den Heuvel]

I suspect that the perception of the problem being just 1 cpu being used is a red herring.

The really problem is probably that it is used 98% for SYSTEM time.

Figure out what is happening there!
Use TOP or UNIX95 PS -ef or whatever.
Maybe even system call tracing is needed.

This is NOT normal, and may well slow down everything. Swapping, caused by sever memory mis-configurations might explain this.
What is the memory/paging situation? vmstat?


>> I've seen this problem mentioned before in the forums

Ok, now pray tell, what were those topics? Did they help you? Did you follow up on suggestions and checks for that problem? what did you learn from that? What further clarification is needed?

Hope this helps some,
Best Regards,
Hein van den Heuvel ( at gmail dot com )
HvdH Performance Consulting

[Malcolm Leckie]

You may wish to check that Oracle knows about the multiple CPU's on the box as it will not use them without being told.

SELECT * FROM v$parameter
WHERE NAME='cpu_count'

will let you know how many CPU's it can use.

[Samuel Scott]

Thanks all. I've been distracted by other problems today and am just now getting back to this.

That last sentence should have read: I've seen this problem mentioned before in the forums, but haven't noticed any solutions.

Malcome, Cpu_count is 4.

Dennis. The processes will be different. This is a development box, the other is production. But the development kernel was copied from the production. Patches are the same.

Hein, Interesting. I will check that. I'm getting this from the Oracle DBA, so I assumed it's Oracle and not system processes, but I will definitely will check it out.

Duncan, no processor sets.

[Hein van den Heuvel]

The system time is probable still spend by Oracle processes, just triggering the system do do inner more / system stuff like interrupts or semaphors or, more likely, memory management. That would happen in the context of an Oracle process, but it would be a system tasks. I'd _like_ to see more than 90% user time and consequenctly less than 10% system time for a healthy Oracle setup, but have come to accept 20% system time... but no more!

fwiw,

Hein.

[Dennis Handly]

>Hein: The really problem is probably that it is used 98% for SYSTEM time.

I read that as CPU 3, 98% user.

[Hein van den Heuvel]

Dennis it right, I managed to read it wrong. Sorry.
Darn 'spaceless' presentation in the ITRC forum.

Hein.

[Samuel Scott]

The last three mornings I have been monitoring this issue via glance, top, and sar. The problem has not reappeared. I did see in Glance where one process occasionally would jump up briefly and grab 20- 25% of the CPU. That process was called ORACLEBS1D, and may be a key to the problem.

btw, I found no matches on ITRC when I entered ORACLEBS1D.

Here is my conclusion. Under normal circumstances, it appears CPU usage on all CPUs remains less than 1%. Understanding that processes (even multi-threaded) don't normally 'jump'from one CPU to the next if they don't have to, but prefer to run on only one CPU. Also, the Scheduler doesn't try to load balance CPUs. Following that, my conclusion is an ORACLE user process (ORACLEBS1D?) consumes excessive CPU cycles on CPU 3 and it just appears that all oracle processes are using it, when in fact the other processes are running less than 1% and wouldn't be noticed.

This is a development box so it makes sense that the problem may have disappeared. Perhaps the user realized his mistake and quielty made the correction.

I'll continue to monitor this for a while, but I think the problem is not with the HP Scheduler or even with ORACLE, but with a runaway development process.

If anyone has other ideas, let me know.

[Samuel Scott]

solution: check last response for details on possible solution. One runaway processes is most likely.