Re: Responsiveness of Server at high CPU load

"Jonathan Lewis" <jonathan_at_jlcomp.demon.co.uk> wrote:

>
>Since you have two CPU's, you can only run at 99%
>if you have at least two statements (or two copies of
>the same statement) running concurrently - are you
>running with parallel execution.

Well, I should have mentioned this. This is a two CPU machine. BUT: hyperthreading was switched on at the time of the problem. Using top, I see 4 CPUs... At times, the sum of percentages is considerably higher than 200%, so it seems to be worth using this feature.

I already switched hyperthreading off, just to be sure. But then I got an answer from an Oracle official stating that he found nothing against hyperthreading in the whole Oracle docu arsenal.

At the time of the problem, two instances were active. I should have made a snapshot of processes but I didn't. Yes, parallel_automatic_tuning=true, but there are no tables with a degree of parallelism > 1 or default, so nothing should be running in parallel (I made a test and found out that parallel queries were slower on this server, which has strong CPUs but not that strong storage).

>One other detail - are you running with resource manager
>activated, this gave me a lot of trouble in earlier versions
>of 9 (but not 9.2, and I thought it was an HP issue at the
>time).

No, but you and Mr. Sybrand Bakker are pushing me to consider using the Resource Manager. Unfortunately, the database suffering from this problem has an unusual design where only one account does everything on behalf of other users, whose accounts are managed "externally" (on user tables) by the application. So the only resource trade I can make is between SYS and this account. Don't know if that buys me something.

>Finally - The documentation for Oracle 9 installs (the last
>time I read it) said something about letting Oracle handle
>the scheduling by giving the oracle account the rtprio and
>a couple of other o/s privileges. If you have done this, AND
>have a bad SQL problem AND a latch-related bug then
>it might explain why things are grinding to a halt.

No, I didn't do that but your question brings me to the idea of trying that out...

Bye
Rick Denoire Received on Wed Dec 17 2003 - 18:17:40 CST