RE: Hyperthreading - Oracle license
Date: Sat, 12 Jan 2013 09:44:42 -0500
Message-ID: <01a001cdf0d3$5bf0dd10$13d29730$_at_rsiz.com>
+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_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-lReceived on Sat Jan 12 2013 - 15:44:42 CET