CPU affinity in Oracle comparison to SQL Server

 
OS: REL7 64 (24 core), DB version: Oracle 12.1.0.2.0
OS: WINDOWS SERVER 2016 64 (4 core), DB version: SQL Server 2016

In the current topic I am going to explain CPU affinity in Oracle with comparison to SQL Server.

It is known that we can use SQL Server Management Studio to make CPU affinity in SQL Server .Please see below.

Even by using query like below, we can find out on which core a process is running on in SQL Server.

SELECT	ipir.command, ipir.scheduler_id, os.cpu_id, .........
 FROM sys.dm_exec_requests ipir  INNER JOIN sys.dm_os_schedulers os
  ON ipir.scheduler_id = os.scheduler_id

But what about Oracle? Which approach should we use in Oracle? Does Oracle have such abilities to do CPU affinity?

Lets make some test and get answer for questions mentioned above.

LSCPU command shows 24 core in the system.

Lets set CPU_COUNT parameter for the database to 1.

The question is: If we set CPU_COUNT to 1 which of 24 processors will be used by process and can we make CPU affinity by Oracle itself?

Lets make a test by using steps below.

1. Open SQLPLUS and run SELECT * FROM DBA_OBJECTS for emulation some activity.
2. Find the PID of SQLPLUS process and use it in the TOP command.
3. In the TOP command activate P column if it is not activated (press F then J then ENTER to activate CPU P column).
4. Watch the CPU usage by process in TOP GUI.

From the last image we see (P column) that the process uses CPU 7 but after a while it will change to CPU 6 and etc…

As I understood which PROCESSOR will be used by process considers randomly (might be by using system internal algorithm).

Even TASKSET command shows us that no CPU affinity is configured for the process:  pid 23033’s current affinity list: 0-23

Conclusion: By setting CPU_COUNT we just set the number of using CPU (as expected) and which CPU will be used by process considers randomly (might be by using system internal algorithm).

There are two approaches for CPU caging “partition” and “over-subscribe“.

For maximum isolation one can use “partition” approach. With the partition approach, the sum of the CPU_COUNTs is less than or equal to the number of CPUs.
Example: suppose the total number of CPUs (i.e. CPU threads/cores) is 24.
We could set CPU_COUNT=8 for database , CPU_COUNT=8 for database B, and CPU_COUNT=8 for database C.
The disadvantage of the partition approach is that any CPU unused by one database instance cannot be used by another.

For better CPU utilization efficiency, use the “over-subscribe” approach.
Example: for a server with 24 CPUs, you could set CPU_COUNT=24 for database A, database B, and database C.
The sum of the CPU_COUNTs is 72, which is greater than the number of CPUs.
Therefore, if all databases are using their full CPU allocation, there will be some CPU contention.

However, by using TASKSET utility we can configure CPU affinity for Oracle process. Thanks for you attention!

Appendix: MOS: Doc ID 1585184.1. Using PROCESSOR_GROUP_NAME to bind a database instance to CPUs or NUMA nodes on Linux.

By using this MOS DOC you can achieve your target by creating Linux CGROUP group. This is an another solution.