Re: Hyperthreading - Oracle license
Date: Fri, 11 Jan 2013 17:41:31 -0800
Message-ID: <50F0BF4B.3000705_at_oracle.com>
There is a better way to use OPQ with 11g: You can enable Auto DOP - by setting parallel_degree_policy to "auto", and then use Resource Manager to limit the degree of parallelism based on the type of workload. Starting with 11.2, you can also use Parallel Statement Queuing to avoid DOP downgrades when there are not enough parallel servers to process a parallel statement. Parallel Statement Queuing queues the parallel statement until enough parallel servers free up. Using Resource Manager, you can limit the number of parallel servers a particular workload uses, and control the order of the parallel statement queues...KJ
*Kevin Jernigan* (650) 607-0392 (o) *Senior Director Product Management* (415) 710-8828 (m) kevin.jernigan_at_oracle.com <mailto:kevin.jernigan_at_oracle.com> *Advanced Compression - ACO*: *Information Lifecycle Management* - ILM Advanced Row Compression *Temporal database* (Total Recall etc) Advanced LOB Compression *SecureFiles* Advanced LOB Deduplication *Database File System* - DBFS RMAN Backup Compression *Direct NFS Client* - dNFS Data Pump Export Compression *CloneDB* Data Guard Redo Network Transport Compression *Database Resource Manager* - DBRM Flashback Data Archive History Table Optimization *Continuous Query Notification* - CQN *Hybrid Columnar Compression* - HCC *Index Organized Tables* - IOT *Database Smart Flash Cache* *OISP*
On 1/11/13 6:28 AM, Mark W. Farnham wrote:
> 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
>
>
-- http://www.freelists.org/webpage/oracle-lReceived on Sat Jan 12 2013 - 02:41:31 CET