RE: Hyperthreading - Oracle license

From: Mark W. Farnham <mwf_at_rsiz.com>
Date: Fri, 11 Jan 2013 09:28:25 -0500
Message-ID: <025601cdf007$ebbf42f0$c33dc8d0$_at_rsiz.com>

If I recall correctly from when OPQ was introduced, the idea is to make the DEFAULT such that a single query WILL consume the entire machine attempting to answer a single question as quickly as possible. Then you lower it to support multiple simultaneous workloads. Gary Hallmark used to stress that everything about OPQ was to solve a single query as fast as possible; that was the goal. You may detune it if you are supporting a different workload. (Which most often is the case.)

Now, that said, if you find yourself wanting to INCREASE that default, measure carefully, because you are *probably* making a mistake.

For folks with a routine non-global day-time, night-time work schedule it is not a good idea to allow day-time queries to run at parallelism designed to use the whole machine. Often 1 or 2 is a useful setting for maximum overall throughput. A higher setting for parallelism might then be useful for the night-time batch jobs running fewer at a time. This is one of the tuning knobs in Oracle for which (while you can make a pretty good a priori setting for the maximum) the optimal setting for a given workload will vary.

Likewise a good "detuned" setting for parallelism of a mixed workload with many jobs running simultaneously is unlikely to be a good setting for a system where the workload is one or two massive queries at a time.

mwf

-----Original Message-----
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Radoulov, Dimitre
Sent: Thursday, January 10, 2013 6:10 PM To: Finn.Jorgensen_at_constellation.com
Cc: oracle-l_at_freelists.org
Subject: Re: Hyperthreading - Oracle license

On 01/10/2013 11:09 PM, Jorgensen, Finn wrote:
> At my company on a Sun T5240 host which reports 128 CPU's due to extreme
hyperthreading, I had to restrict CPU_COUNT otherwise running utlrp.sql as part of new database creation spawned so many parallel threads it brought the server to its knees.
>

Hi Finn,
you can manually adjust the degree of parallelism for utlrp:

_at__at_utlprp.sql X

See ORA-04031 error when upgrading to 11.2.0.2 during utlrp.sql step [ID 1275067.1].

That said, for me, one of the first things to do after a fresh 11.2 install is adjust the max_parallel_server processes, the default seems too hight.

Regards
Dimitre

--
http://www.freelists.org/webpage/oracle-l


--
http://www.freelists.org/webpage/oracle-l

Received on Fri Jan 11 2013 - 15:28:25 CET