Have you wondered how many CPUs are allocated to your database?
One of my reader has, and he asked me if I could give some clarification on the subject.
Today’s article will cover the dynamic init.ora parameter CPU_COUNT.
Let’s look at this parameter, what does this parameter represent?
On systems with CPUs that have multiple threads, the CPU_COUNT parameter, specifies the total number of available CPU threads.
What is the difference between a CPU and a CPU thread?
The CPU thread is a virtual version of the CPU core. Basically physical CPU cores are broken up into virtual cores, aka threads.
For example: the server that hosts your database, has 4 physical cores, with 2 threads each, resulting in an 8 CPU server. What will be the value of the CPU_COUNT parameter? Answer: 8, as there are 8 CPU threads available.
Another example, working backwards: the CPU_COUNT parameter shows a value of 16 CPU threads. You know that the physical CPU cores have 4 threads each. How many physical CPUs do you have? Answer: 4 physical CPU cores. You get the idea.
Now, let’s go back to the CPU_COUNT parameter. This parameter will show you the number of CPU threads, the number of virtual CPUs, and not the physical ones on systems with multi-threaded CPUs.
By default CPU_COUNT parameter’s value is set to 0, meaning that Oracle will pick up the value of the number of CPUs from the operating system. If the value of this parameter is set to something else than 0, the default value,
then the database will use that value in its calculations.
Who is using the CPU_COUNT parameter? Some database components are using the value reported by CPU_COUNT parameter, in their calculations. Such components are: the Optimizer, Parallel Query and Resource Manager.
Is it possible that execution plans will change if you change the value of this parameter?
Absolutely, as the cost based optimizer uses this parameter to calculate the cost of an execution plan.
How can you limit the number of CPUs allocated to a database, if the database server hosts multiple databases? Starting with 11gR2 Enterprise Edition, you can use the feature Instance Caging, to manage the CPU usage for databases. This feature uses the Resource Manager and the CPU_COUNT parameter, to limit the maximum amount of CPU threads allocated to an instance.
How does this feature work? How do you set it up? There are two steps:
1) Enable the Resource Manager
2) Set CPU_COUNT parameter
1) To enable the Resource Manager you use the statement below. This statement enables the default Resource Manager plan. You can also use another Resource Manager plan that you created in the database. One thing you want to confirm is that the Resource Manager plan has CPU directives, meaning that each consumer group has a maximum utilization limit of the allocated CPU.
ALTER SYSTEM SET RESOURCE_MANAGER_PLAN = default_plan;
2) You modify the CPU_COUNT parameter to the desired value. Since this parameter is dynamic, issue the following statement:
ALTER SYSTEM SET CPU_COUNT=8;
It is worth mentioning that there are two approaches when looking at setting up instance caging and determining how many CPUs to allocate to an instance: over-provisioning and partitioning.
Over-provisioning would be used for non-critical systems. With this approach, the total number of CPUs allocated to all the instances on the server, exceeds the total number of available CPUs. Example: The Server has 16 CPUs, you have 5 database instances with 4 allocated CPUs each. The total number of CPUs allocated to all the instances is 20, which is greater than 16.
Partitioning would be used for critical production systems, where you want to prevent instances interfering with each other. With this approach, the total number of CPUs allocated to all the instances on the server, is equal to
the number of CPUs the server has. Example: The Server has 16 CPUs, you have 4 database instances with 4 allocated CPUs each. The total number of CPUs allocated to all the instances is 16, which is the same as the number of CPUs on the server.
