Thanks for the question, John.
Asked: April 06, 2018 - 10:39 am UTC
Last updated: April 08, 2018 - 6:54 am UTC
Version: 12+
Viewed 1000+ times
You Asked
I am looking at instance sizing and have some questions on how Oracle uses CPU cores. Assume this is a Standard Edition database so no queries will be running in parallel mode. Assuming system background processes are negligible:
*) When a single query runs in Oracle Standard Edition, does that one query run on a single CPU core if I have no other users on the system and no other queries running (background processes aside)? So having say 20 cores would make no difference to having 1 core, regardless of how much load that one query put on the system?
*) Do multiple cores only come into play when you have multiple processes running concurrently?
*) If I have an overnight batch process that is quite resource intensive but runs queries in series, one step after another, and then very light user transactional workload during the day, would it be sensible to size the CPU count for the daily usage given that the batch processes run one at a time, and outside of normal user activity?
*) I believe we can specify a maximum number of CPU cores for Oracle to use. Say I have a 16 core server running 2 Oracle instances, and I limited each instance to 8 cores - how would Oracle use those cores? Would the both attempt to use only the first 8 and the remaining 8 would be unused? Or would it use all 16, but only a maximum of 8 concurrently, and let the OS choose which ones to multiplex the work threads on?
I know there is a lot more to sizing than CPU cores, however I wanted to just clarify the points above first.
and Connor said...
*) When a single query runs in Oracle Standard Edition, does that one query run on a single CPU core if I have no other users on the system and no other queries running (background processes aside)? So having say 20 cores would make no difference to having 1 core, regardless of how much load that one query put on the system?
Correct. A single (non-parallel) query can only use a single core during its execution.
*) Do multiple cores only come into play when you have multiple processes running concurrently?
Yes.
*) If I have an overnight batch process that is quite resource intensive but runs queries in series, one step after another, and then very light user transactional workload during the day, would it be sensible to size the CPU count for the daily usage given that the batch processes run one at a time, and outside of normal user activity?
Yes, but you could make an argument that you are cutting things fine here. Any kind of anomalous behaviour (eg user adhoc query) might impact regular activity.
*) I believe we can specify a maximum number of CPU cores for Oracle to use. Say I have a 16 core server running 2 Oracle instances, and I limited each instance to 8 cores - how would Oracle use those cores? Would the both attempt to use only the first 8 and the remaining 8 would be unused? Or would it use all 16, but only a maximum of 8 concurrently, and let the OS choose which ones to multiplex the work threads on?
We don't *bind* to the cores, we simply use "8 core's worth". So even if you only had 1 Oracle instance, on a 16 core machine (with the instance limited to 8 cores), then over time, you'd see that each of the machines 16 cores would be running at (most) about 50% capacity, ie, at no given instant are we consuming more than 8 cores worth of work.
One thing I will say is - when it comes to Standard Edition, you need to work closely with your account manager, and read the license details reggarding physical machines carefully. It can be a bit of a minefield...I wish it wasn't.
Rating
(1 rating)
Is this answer out of date? If it is, please let us know via a Comment