cpu 1

[Igaduma]

Hi all,

we've got oracle running on an E-250 with 2 cpu's.
When checking the cpu usage with prstat -a
oracle only takes out 50%, never more, always maxing out at 50%.
How come it isn't using 70 or 80, I thought that since 2 proc are in the system the system will create a psrset with 2 cpus in.




its a default sol8 installation with a standard oracle8 on top, nothing extra, no changes in /etc/system, except SGA & NIC.

Any help welcome!

I

 

[jimbopalmer]

Any one oracle process (user), can only be running on one CPU, 50% of your total CPUs, but the total number of oracle processes can consume all your CPUs, if you have multiple users. (additionally al the unix processes can consume CPU time from your CPUs as well)

There is a parallel query option that uses hints in the queries to allow one process to consume more than one CPU, but it always seemed 'high maintenance' to me, if I got more CPUs, I would need to re-tune each hint. I have felt better about dividing up my problem into many processes, and keeping all the CPUs in use that way. I tried to remain child-like, all I acheived was childish.

 

[Igaduma]

Hi,

So, if I get it right 1 user can have 50% usage, while another connects and starts working he can have also a full blown 50% which then would max at 100?

I'm getting confused with all the other processes oracle runs I think.

So, the user can have 50% while running a query, the oracle db writer can use the idle 50% to flush right?

thanks,
L

 

[Igaduma]

Hi,

Another thing,

If I was to set cpu1 to no-intr this would mean that cpu1 would only be used for user procs right ?
while at that point cpu0 remains idle for any incoming interrupts?

db writer procs is a user or system proc?

god...i need an oracle course!


thanks all,

I

 

[jimbopalmer]

One user can only drive one CPU idirectly n Oracle (barring Parallel query, which I have not played with) although he may also wake up dbwriter and fsflush so that both CPUs are in use, but HIS process is pegged at 50% and background jobs are using part of the other CPU.

I have not tried to tie a CPU to a user process, as in my scenario I have 150 'active' users , 8 of which can actually get a CPU, then the interrupts start.

Should you suspect that you have too few CPUs, sar -q will show you the percent of time some program wishes it was running but is not (%runocc), and the number of programs waiting to run when any program is waiting to run. (runq-sz)


a value of
1.2 3

indicates that you wanted another CPU 3% of the time and when you did, you wanted 1.2 CPUs more

a value of
4.7 84

indicates that you wanted another CPU 84% of the time and when you did, you wanted 4.7 CPUs more

(my personal 'best' was 37 CPUs more, I limited the number of background jobs that ran at once) I tried to remain child-like, all I acheived was childish.

 

[Igaduma]

Hi Jimbopalmer,

I notice your post are always top-notch!

thanks for the info, that sar-for-dummies explanation helped me alot.

Now all I have to do is wait for peak times and turn pale...
hopefully not...


I

 

[jimbopalmer]

When I post, I get corrected, THEN I learn more than I knew!

There are two more values in sar -q but they only are shown if you are out of memory so bad unix swaps entire programs out of RAM (the man page claims this never happens but I see it sometimes)

you can sar previous days data, but I always have to go to the man page to figure out how
I tried to remain child-like, all I acheived was childish.

 

[Igaduma]

Hi Jimbo,

When we first started with this server & oracle I ran sar for several days and drafted up some nice multi color charts for the management, just had sar running for days and exported the whole lot to asci and used excel.

I don't have any cpu problems or hammering, heck, the average load never exceeds 1.8 - 2.2 (prstat) at peak times so I'm very happy with it,
I just want to fully understand the whole thing and fine tune everything since we are pretty limited to get new additional hardware.
But now the sar -q will shine a whole new light, although it doesn't seem to report *anything* at all...which is always good

Just finished installing Sol9 on a ultra10 and yes, finally I can dabble with Resource manager & bandwidth manager for free!

I wonder tho if the resource manager package included with sol9 works on sol8,
Hmm, I have a sol8 dev machine on which I could try that.

I