CPU Core Limit in Oracle Database: Best Solution for Dummies

CPU core limit will help you, when you have no right sizing and you have no control what be happened on database and when you didn’t any optimization, limiting using resources by database or any other system will be necessary to help server to keep working without hang. Database be able to use all processing resources on a server and other process will be dead or hang if they couldn’t receive enough time for processing. CPU is the most important component and resource on any computer system, so if you want to stable system then the needed resources should be measured by right sizing or limiting some processes to delivering minimal resources to other processes.

I’m not Database administrator and you can put your opinion about this post as a comment because maybe I was wrong.


What’s Our Story?

Our DBA team has two parts, part one: server administration – part two: database administration and tuning.

Each part is subdivision of one department and it’s wonderful. They are a team!

Some of our database servers are deployed on virtual machine (VMware vSphere platform) and many of the database servers are using as database for developing new feature of our applications. Actually, they are part of development ecosystem. Developers doing some tasks on servers that the tasks are not like the tasks which should be run for development. Those tasks are like performance test tasks!.

Anyway, when the servers are overloaded by the tasks, DBA team just grumble about secure shell connection to the servers because servers don’t respond to any connection but when we checking servers, the servers have high CPU usage, memory usage and generating huge IOPS on disks.

Sometimes, disk bandwidth is more than 1GB/s and IOPS is more than 6K!. Some of our production servers don’t generate this huge load!.

I’ve mentioned that I’m not DBA but I think that we can do better resource management or even apply limitation to resource usage for keep servers responsive for all other tasks.

Oracle Database CPU (Core) Count

I did lots of search and I found one parameter to limit CPU for one database, instance, pluggable database and container database. Define “CPU Count” or using “Database Resource Manager” is recommended when multiple instances are running on a server or there is multi-tenancy, but I think that the parameters would help to keep server stable such as our story’s servers.

Also Intel Hyper-Threading or other technologies like it, would help to run 2x parallel processing on server at least but when you need to power for processing, these technologies will be bottleneck on system. Why? Because when power is needed for processing then we are talking about processor frequency and because Database cannot understand different between physical processors and threads and uses threads as processor count, server will be overloaded.

Anyway, I guess that when you didn’t right size and don’t aware about development tasks and other tasks like that or have no control about what doing end users, configuring processor count lower that available processor on server will help to prevent server hang during processing huge workloads.

Resource management is improved version by version and latest versions can do dynamic processor scaling as well.

Further Reading

Oracle Help Center

Dynamic CPU Scaling in Oracle Database 19c

Oracle cpu_count and number of cores

How to limit CPU-Consumption of a database

Instance Caging to Manage CPU Usage in Oracle Database 11g Release 2

How To Check Cpu Count Oracle

Oracle and CPU utilization metrics

Oracle cpu_count intel hyperthreading incorrect

Oracle11G 100% CPU consumption troubleshooting

CPU Count Consideration for Oracle Parameter Setting When Using Hyper-Threading Technology

Fix Oracle Hanging

Oracle 100% CPU tips

Using Oracle Database Resource Manager

Database Instance Caging: A Simple Approach to Server Consolidation

Oracle 11g Instance Caging – limit database CPU consumption

Davoud Teimouri

Professional blogger, vExpert 2015/2016/2017/2018/2019/2020/2021/2022/2023, vExpert NSX, vExpert PRO, vExpert Security, vExpert EUC, VCA, MCITP. This blog is started with simple posts and now, it has large following readers.

1 Response

  1. 25/01/2024

    […] CPU Core Limit in Oracle Database: Best Solution for Dummies […]

Leave a Reply

Your email address will not be published. Required fields are marked *