RE: Hyperthreading - Oracle license

The automation tools Kevin references now allow us to have the machine react to the actual load in a much finer grain that old fashioned predictive "interactive" versus "batch" work shifts and even improve the situation when you do have varying overall work shifts. This allows you to use as much of the machine as is prudent while preserving headroom, for example, for interactive load.  

Thanks for the reminder Kevin!  

mwf  

From: Kevin Jernigan [mailto:kevin.jernigan_at_oracle.com] Sent: Friday, January 11, 2013 8:42 PM
To: mwf_at_rsiz.com
Cc: cichomitiko_at_gmail.com; Finn.Jorgensen_at_constellation.com; oracle-l_at_freelists.org
Subject: Re: Hyperthreading - Oracle license  

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         

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-l