When you have no right sizing, when 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 necessary to help server to keep working without hang. Oracle 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 Oracle Database administrator and you can put your opinion about this post as a comment because may be 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 Count
I did lots of search and I found one parameters to limit CPU for one database, instance, pluggable database and container database. Define “CPU Count” or using “Oracle Database Resource Manager” is recommended when multiple instance 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 Oracle Database can not understand different between physical cores and thread and uses threads as CPU count, server will be overloaded.
Anyway, I guess that when you didn’t right sizing and don’t aware about development tasks and other tasks like that or have no control about what doing end users, configuring CPU count lower that available CPU on server will help to prevent server hang during processing huge workloads.
Resource management is improved version by version and Oracle 19c can do dynamic CPU scaling as well.
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
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
Using Oracle Database Resource Manager
Database Instance Caging: A Simple Approach to Server Consolidation