CPU Consumption by Oracle
The main D.SIDE screen allows real-time monitoring of the overall CPU consumption of our Oracle instances.
For this, we have several indicators distributed across 3 different areas of the screen. Example:
1 – he DSIDE instance is hosted on a machine named ‘dside’ with 12 CPUs.
The database is running Oracle 19c Standard Edition.
2 – With an average of 6 active sessions (‘Average Active Sessions’) and the instance consuming only CPU, this means that 6 CPUs are in constant use. These 6 CPUs, relative to the total available (12), represent 50% of the machine’s capacity. This is reflected in the ‘DB CPU usage’ at 48%, as 5.82 CPUs are being used out of 12. The ‘DB CPU’ corresponds to Oracle’s consumption for the DSIDE instance we are monitoring here. Other programs or instances could be running on the machine, not just this Oracle instance. In this case, the ‘Host CPU usage’ may show a higher value. Here we observe that the machine is used at 52%, which indicates that nearly all activity on this machine is attributed to our Oracle instance.
3 – LThe ‘Global Activity’ curve summarizes CPU consumption and waits for each 5-second interval. Here, we mostly see green in this curve, which represents CPU usage. This indicates that there are almost no waits or they are not significant. We can spot a bit of red above the green, which corresponds to the ‘Concurrency’ wait class (as confirmed by the ‘Wait Events’ list below the curve), but the measure is not comparable to the CPU consumption, which remains the most important factor here.
The ‘Global Activity’ curve is visualized on a ‘CPU limit’ scale in the dropdown list. This means the maximum visible in the curve corresponds to the number of CPUs on the machine. The graph clearly shows that half of the CPU resources are used, leaving room before reaching the upper line, which represents the physical limit of the 12 CPUs.
Oracle Standard Edition SE2 CPU Thread Limit
For its Standard Edition SE2, Oracle specifies:
« each Oracle Database Standard Edition 2 database may use a maximum of 16 CPU threads at any time. When used with Oracle Real Application Clusters, each Oracle Database Standard Edition 2 database may use a maximum of 8 CPU threads per instance at any time. »
In standalone mode, an instance can use up to 16 CPU threads, and in a RAC cluster, each instance can use up to 8 CPUs. These limits are independent of the actual capacity of the machine hosting the instances.
This means, as per our previous example, that when consuming 6 CPUs on a node that is limited to 8 threads, we are no longer at 50% of the allowed capacity. We are still at 50% of the total machine capacity, but this is less relevant since, in SE2, we can only use 8 out of the 12 CPUs on this machine.
Therefore, here is the view we choose to display, based not on the 12 CPUs of the machine, but on the 8 CPUs imposed by the Standard Edition SE2:
1 – The information regarding the database we are connected to remains unchanged: Oracle 19c Standard Edition on a machine capable of providing 12 CPUs to its users.
2 – The load remains the same; we haven’t altered the Oracle traffic, so we still observe an average of 6 active sessions and a machine utilization of approximately 50%. What changes, however, is the upper limit used to process these values. While we still have about 5.9 CPUs in use, corresponding to the 6 active sessions, we no longer report this consumption relative to the total 12 CPUs but instead to the maximum of 8 CPUs allowed by the Standard Edition SE2. As a result, we go from 6/12 = 50% ‘DB CPU usage’ to 6/8 = 75% ‘DB CPU usage.’
Similarly, the upper limit is now clearly represented by a horizontal line corresponding to the maximum of 8 CPUs, rather than 12. It’s now much clearer that the CPU consumption by Oracle, shown in green, is closer to this limit. This makes it evident that our activity has less headroom to consume additional resources.
How D.SIDE Manages Oracle Standard Edition SE2
An option called ‘Apply SE restrictions to CPU usage’ specifically for Oracle Standard Edition SE2 is now available in the user preferences.
By default, this option is not enabled. If you want to use it, simply go to the ‘Options / Preferences’ menu, then in the ‘Standard Edition’ tab, check the ‘Apply SE restrictions’ option. Additionally, the ‘CPU threads limit’ values for standalone and RAC are adjustable. However, the default values are those described in the Oracle offering: a limit of 16 CPU threads for a standalone database, and 8 CPU threads for a RAC database.
If this option is enabled, once connected to a Standard Edition, the display will reflect what was shown above.
Note:Even if enabled, this option is ignored when connecting to an Oracle instance running in Enterprise Edition.