Oracle cpu_count intel hyperthreading incorrect Oracle Tips by Burleson Consulting Mike Ault - April 14, 2005

On 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 9i and 10g database where the CBO has been set to create execution plans based on CPU costs. Note:153761.1 on metalink 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 10.1.0.2.
  
  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 Metalink Note:276877.1)

• You are using automatic parallelism (Oracle9i and 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.

How does cpu_count get set?

The 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. 

Intel 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.

Why is cpu_count important?

The Oracle cpu_count is determined when you start Oracle.  The 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 system. 

The cpu_count is used to determine the settings for several important init.ora parameters:

·         fast_start_parallel_rollback - 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.

• db_block_lru_latches – 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 (<9i) 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 calculated as:

                   CPU_COUNT * PARALLEL_THREADS_PER_CPU

·         log_buffer and redo copy patches - The number of CPUs is used to determine the value of log_buffer if the log_buffer parameter is not set in the initialization parameters. For details on LGWR, 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.

This 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 multiple cores?

• If you use hyper-threading, have you had any problems?
   

• If you had to stop using hyper-threading, why?
   

• If you are using dual-core CPUs, have you had any problems?

Here are some comments and notes from MetaLink:

From Mike Ault:

1. 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.

2. A 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.

3. 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 MetaLink:

Gina Thunder (Oracle employee - MetaLink)

Oracle 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 Hyperthreading

(Note from Author: When asked pointedly about various comments on use/non-use of hyperthreading, the only answer I got from Metalink analyst was the pointer to this note.)

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 Hyper-threading are:

Bug: 4202437

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 place). 

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.

Bug: 1507768

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 DLL are:

"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 following immediately: 

• 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 installation time.

Bug No. 4184876

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 ?
  ---------  -------------  --------------
      47      1M( 1048576)  NO  (no error)
      47     18M(18874368)  NO  (no error)
      48      1M( 1048576)  YES (ORA-7304)
      48     16M(16777216)  YES (ORA-7304)
      48     17M(17825792)  YES (ORA-7304)
      48     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 (9.2.0.6).

On 9.2.0.6, 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 scripts.  You can buy it direct from the publisher for 30%-off and get instant access to the code depot of Oracle tuning scripts.