RE: Hyperthreading - Oracle license

  • From: "Mark W. Farnham" <mwf@xxxxxxxx>
  • To: "'Kevin Jernigan'" <kevin.jernigan@xxxxxxxxxx>
  • Date: Sat, 12 Jan 2013 09:44:42 -0500

+1
 

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@xxxxxxxxxx] 
Sent: Friday, January 11, 2013 8:42 PM
To: mwf@xxxxxxxx
Cc: cichomitiko@xxxxxxxxx; Finn.Jorgensen@xxxxxxxxxxxxxxxxx;
oracle-l@xxxxxxxxxxxxx
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@xxxxxxxxxx

        

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@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]
On Behalf Of Radoulov, Dimitre
Sent: Thursday, January 10, 2013 6:10 PM
To: Finn.Jorgensen@xxxxxxxxxxxxxxxxx
Cc: oracle-l@xxxxxxxxxxxxx
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:
 
@@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
--
//www.freelists.org/webpage/oracle-l
 
 
--
//www.freelists.org/webpage/oracle-l
 
 

 



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


Other related posts: