RE: Hyperthreading - Oracle license

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

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.


-----Original Message-----
From: [] On Behalf Of Radoulov, Dimitre
Sent: Thursday, January 10, 2013 6:10 PM To:
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 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.



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

Original text of this message