Re: Oracle threading on HP (load balancing

rad_at_earthling.net wrote in article
<6smbb8$j5o$1_at_nnrp1.dejanews.com>...
>
> The problem is intermittent but common. And it's always s000 that
becomes
> 100% bound. There are up to six shared servers but s000 will
become 100% CPU
> bound (according to glance) yet the others are often below 5% (or
even 0%).
>

It is almost inevitable that s000 does more work that all the other shared servers.
I suspect that what you are seeing is one user/process that manages to send
in a CPU intensive query to the database fairly frequently. Try switching sql_trace
on against s000 - possibly the best way is to use oradbx, or oradebug and
hitting the process with event 10046. I think you will probably find some very
CPU-intensive code in the resulting trace file.

> PQO also doesn't appear to load balance the way we expect it to.
One process
> seems to take the load while the others sit fairly idle.

It can happen - if the data in the driving table are clumped in an unfortunate
fashion, then you can find that one PQ slave happens to pick almost all the
relevant driving rows in its rowid range scan. Examine the path of the imbalanced
queries, look at the distribution of data in the driving table, and look at v$pq_tqstat
for details of the row acquisition and passing.

By the way - mixing PQO and MTS is probably a bad idea unless you are very confident that the OLTP users come through MTS and the DSS users come through dedicated servers.

Jonathan Lewis Received on Fri Sep 04 1998 - 04:23:31 CDT