Oracle cpu_count intel hyperthreading incorrect
Oracle Tips by Burleson Consulting
Mike Ault - April 14, 2005
Oracle servers with hyper-threaded processors (i.e. Intel servers on
Windows and Linux) Oracle doubles the value for cpu_count.
This can be an issue when:
You have turned-on CPU-based optimizer
costing - For Oracle and 10g database where the CBO has
been set to create execution plans based on CPU costs.
Note:153761.1 on MOSC
describes the system statistics collection process to turn on
CPU costing. Note that according to Bug 2820066 CPU cost
is always computed regardless of optimizer mode when
optimizer_index_cost_adj is set in Oracle versions less than
However, CPU cost is not used unless the undocumented parameter
"_optimizer_cost_model" is set to "cpu" for example:
alter session set "_optimizer_cost_model"=cpu;
This parameter defaults to
CHOOSE. (from MOSC Note:276877.1)
You are using automatic parallelism (Oracleand earlier) - If cpu_count is doubled and the
parallel_automatic_tuning is set to "true", the CBO will
believe that full-table scans are less expensive based on doing
them in parallel using a default degree of cpu_count x
parallel_threads_per_cpu, and influence the optimizer to
choose a full-table scan.
As long as there are memory waits for the hyper-threading
technology to take advantage of, and the table is spread over
multiple disks or is partitioned, this can be a performance
boost, however on machines where there are no memory waits, the
system is IO constrained or the table is not partitioned then it
can reduce performance.
If you are using Oracle parallel query -
The number of processors is an important factor in determining
the optimal degree of parallelism for a parallel full-table scan
as shown in the previous point.
does cpu_count get set?
oracle kernel will determine the number of CPUs on a system during
startup. However, some new processors (i.e. Intel) have dual
cores, where we have two CPU's on one chip, and they appear as two
processors when they only occupy one slot, while this is good for
software performance it can be hazardous to your license, Oracle has
stated that they will charge a CPU license fee for the extra cores
in multiple core CPUs.
Hyper-threading chips are an example of a single chip that appears
to be two CPU's to the Oracle kernel. On Intel-based Oracle
servers running Linux or Windows, the cpu_count is set to
double the number of physical CPUs when using the hyper-threading
setting of the OS or BIOS.
is cpu_count important?
Oracle cpu_count is determined when you start Oracle.
cpu_count affects the Oracle cost-based optimizer through many
calculated parameters that use cpu_count as their basis and
are considered every time that Oracle creates an execution plan for
a SQL statement. A doubled setting for cpu_count can
result in changes to your explain plans when moving from a single to
multiple CPUs or when you increase the number of CPUs in your
cpu_count is used to determine the settings for several
important init.ora parameters:
- The default value is 2 times the
number of CPU of your system, but some DBAs recommend setting this
value to 4 times the cpu_count.
– This defaults to the following formula : (CPU_COUNT x
6) or (DB_BLOCK_BUFFERS/50), whichever is less. In earlier
releases it defaulted to CPU_COUNT/2. If you set
DB_BLOCK_LRU_LATCHES higher than this max value on systems where
it is available (<) or set the undocumented parameter _DB_BLOCK_LRU_LATCHES
higher, then Oracle just ignores this and sets it internally to
the above calculated value. However it will be set to Num_pools
x (CPU_COUNT/2) if multiple buffer pools (default, recycle, keep
and any of the multi-block size pools) are setup.
parallel_max_servers - This parameter controls the
maximum number of OPQ factotum processes (p000, p001) that are
spawned during a parallel query when parallel_automatic_tuning
is enabled. The DEFAULT degree of parallelism is
CPU_COUNT * PARALLEL_THREADS_PER_CPU
and redo copy patches - The number
of CPUs is used to determine the value of log_buffer if the
parameter is not set in the initialization parameters. For details
click here. There is still argument about whether the
cpu_count influences the behavior of actions against the
log_buffer. Of course other important internalized parameters
that deal with the log buffer are redo copy latches (2 times the
number of CPUs when cpu_count>1) and redo allocation latches.
tip has caused great confusion and debate (again, my apologies), yet
this appears to be an issue because the final conclusions are still
not completely clear. The questions seem to hinge around:
Does Oracle recommend using hyperthreading on
processors with this capability?
Does the doubling of the cpu_count adversely
affect parallel query operations? (Maybe, could be good or bad)
Does the cpu_count affect your Oracle
license costs? (Answer: cpu_count no, physical cores,
yes. A single-core hyperthreading CPU counts as one CPU for
licensing, a dual-core CPU counts as 2)
So, let us know, do you use hyper-threading or
If you use hyper-threading, have you
had any problems?
If you had to stop using
If you are using dual-core CPUs,
have you had any problems?
are some comments and notes from MOSC:
From Mike Ault:
Oracle will set cpu_count at each startup based on the
perceived number of CPUs, this should be the same as shown in the
top, vmstat and other commands on UNIX and Linux.
hyper-threaded or dual core shows as 2 CPUs for each CPU. A
hyper-threaded CPU is considered a single CPU for licensing, a
dual-core CPU is considered 2 CPUs for licensing.
Oracle may use multiple lgwr_io_slaves if they are configured by
setting dbwr_io_slaves to > 0, but their use is not tied to the
number of CPUs.
Notes from MOSC:
Gina Thunder (Oracle employee - MOSC)
will work just fine on any OS that is running and recognizes a
hyper-threading enabled system and will take advantage of the
logical CPUs to their fullest extent. Since no additional code was
added on the Oracle end to take advantage of hyper-threading, this
information is relevant to all Oracle versions and is considered a
supported configuration. When Oracle asks the OS how many CPUs are
in the system, the OS just reports the total number of logical CPUs.
This is transparent to Oracle as it doesn't know the difference.
Unfortunately, I am not sure how this affects licensing.
From Note: 205089.1: Oracle and
(Note from Author: When asked
pointedly about various comments on use/non-use of hyperthreading,
the only answer I got from MOSC analyst was the pointer to this
Oracle will work just fine on any
O/S that is running and recognizes a hyper-threading enabled
system. In addition, it will take advantage of the logical CPUs to
their fullest extent (assuming the O/S reports that it recognizes
that hyper-threading is enabled). It is also a supported
configuration...any issues should be logged as bugs. All
Oracle versions can take advantage of hyper-threading, since no
support code has been added for it, all the changes were in the O/S,
the bios and the hardware.
When Oracle asks the O/S how many
CPUs are in the system, the O/S just reports the total number of
logical CPUs and Oracle doesn't know the difference.
The only bugs reported on
The agent, when started, consumes
100% CPU immediately, all targets show as a pending status excepting
for database targets which have metric collection errors. When
trying to configure a database target with the dbsnmp
Customer is using Red Hat
Enterprise Linux ES with dual cpu's which had been setup as a
multi-processor server with 4 virtual CPUs (hyper threading in
If the machine is started in
multi-processor mode using the smp kernel(2.4.21-27.0.1.ELsmp) , the
agent goes into a CPU spin as soon as it is started.
All the metrics fail with errors.
The same does not occur if the
machine is run in single CPU running vanilla kernel.
CPUID detection for Intel?
Pentium? 4 processor system:
Intel has identified that
applications which use the Symantec Just in Time Compiler library
file ("symcjit.dll" for Microsoft Windows* operating system and "symc_jlt.nlm"
for Novell Netware* 5.1) may not run properly on Pentium? 4
processor systems because the library does not properly identify the
processor. The failure typically is that the affected application
simply terminates. Under Microsoft Windows*, the properties of the
"Symantec Java! JustInTime
Compiler Version 3.10.107 for JDK 1.2 Copyright (C) will also not
work. To confirm the version you are using, select the DLL, right
click on the selected DLL and select Properties, and then the
Version tab. Intel encourages all Software developers to do the
Check if your application(s)
uses the Symantec Just in Time Library file Request an updated
library file from your provider
Test your application with the
new library file on Pentium? 4 processor system
Ensure you have plans in place
to provide application updates and address customer concerns
This will be a huge impact to
support. I am not sure how often this CPUID detection is
performed with our software but apparently it is done at
When setting CPU_COUNT larger than 48 and REDO_BUFFER smaller than
18M (18874368), ORA-7304 is returned and we can't startup database.
If setting CPU_COUNT as 47, ORA-7304 is not returned.
I can confirm
the reproducibility as below.
CPU_COUNT REDO_BUFFER REP ?
--------- ------------- --------------
1048576) NO (no error)
18M(18874368) NO (no error)
1048576) YES (ORA-7304)
16M(16777216) YES (ORA-7304)
17M(17825792) YES (ORA-7304)
18M(18874368) NO (no error)
This problem occurs only on R10g(10.1.0.2 and 10.1.0.3), and doesn't
reproduce on R9.2 (126.96.36.199).
On 188.8.131.52, when setting CPU_COUNT=48 and REDO_BUFFER=1M, there is
no error and we can startup database.
If you like Oracle tuning, see the book "Oracle
Tuning: The Definitive Reference", with 950 pages of tuning tips and
You can buy it direct from the publisher for 30%-off and get
instant access to the code depot of Oracle tuning scripts.