Lesson 1
Tuning for CPU usage
In order for an Oracle database to perform its tasks, the Oracle user processes must share the Central Processing Unit (CPU) resources of the database server.
CPU Configurations
Some database servers contain several CPUs (Symmetric multi-processing SMP) or many CPUs (Massively Parallel MPP). In these cases, multiple CPUs can service Oracle work. Unless you use CPU binding, which is covered in a later lesson, the OS will automatically distribute work among your multiple CPUs. You can exploit the multiple CPUs by using parallel functions. The best way to accomplish this is with a parallel query that specifies the number of CPUs with the PARALLEL DEGREE clause.
A CPU can only address a single request at any given time, and the OS maintains queues to allow for the illusion of instantaneous response time. The OS attempts to keep all of the CPUs as busy as possible while maximizing task throughput. The purpose of this module is to show you how to monitor CPU usage and maximize CPU consumption on your Oracle database server.
By the time you finish this module, you should be able to:
By the time you finish this module, you should be able to:
- Monitor CPU usage with the ps command
- Execute and interpret vmstat
- Describe dispatching priority
- Run the nice command to change CPU dispatching priorities
Create a "parallel query" specifying the "number of CPUs" with PARALLEL DEGREE clause
As of the latest updates in Oracle Database, the task of specifying the number of CPUs directly using the `PARALLEL` degree clause has not been deprecated, but Oracle has optimized how parallelism is managed with the introduction of Automatic Degree of Parallelism (Auto DOP). This feature allows Oracle to automatically manage the degree of parallelism based on the system's current workload and the resource requirements of the query.
Here's a more detailed look at how this works:
The capability to manually set the degree of parallelism using `PARALLEL DEGREE` is not deprecated and can still be utilized, especially in environments where DBAs want to maintain tight control over query execution. However, for optimal performance and resource management, particularly in fluctuating workload environments, utilizing Auto DOP is often more effective.
It's also worth noting that settings and best practices can vary between different versions of Oracle Database, so it's advisable to consult the specific documentation or release notes for the version you are using.
Here's a more detailed look at how this works:
- Manual Specification: You can still manually specify the degree of parallelism for a SQL statement or table using the `PARALLEL` clause. For example, `PARALLEL (degree)` where `degree` can be a specific number, can be used to control how many parallel execution processes Oracle should use for the operation.
- Auto DOP: This is recommended for most systems because it dynamically adjusts parallel execution based on the current system workload and available resources. It takes into consideration the number of CPUs, among other factors, to decide the optimal degree of parallelism.
- Parallel Hint: Within SQL queries, you can use parallel hints to suggest or enforce a specific degree of parallelism, for instance, `/*+ PARALLEL(4) */`, which suggests using four parallel threads.
The capability to manually set the degree of parallelism using `PARALLEL DEGREE` is not deprecated and can still be utilized, especially in environments where DBAs want to maintain tight control over query execution. However, for optimal performance and resource management, particularly in fluctuating workload environments, utilizing Auto DOP is often more effective.
It's also worth noting that settings and best practices can vary between different versions of Oracle Database, so it's advisable to consult the specific documentation or release notes for the version you are using.
PARALLEL DEGREE Clause
The PARALLEL DEGREE clause in Oracle allows you to specify the number of parallel execution servers that should be used to process a parallel query. This can be useful if you want to limit the amount of resources that the query can use, or if you want to ensure that the query runs with a specific number of parallel execution servers.
Here is an example of how to create a parallel query that specifies the number of CPUs with the PARALLEL DEGREE clause:
In this example, the PARALLEL(4) clause specifies that the query should use 4 parallel execution servers. You can replace the number 4 with the number of parallel execution servers that you want to use for your query. Note that the PARALLEL DEGREE clause can only be used in the hint section of a query, which is indicated by the /*+ syntax. The hint section is used to give the optimizer additional information about how to process the query.
The next lesson explains how you can monitor CPU consumption.
Here is an example of how to create a parallel query that specifies the number of CPUs with the PARALLEL DEGREE clause:
SELECT /*+ PARALLEL(4) */ * FROM your_table;
In this example, the PARALLEL(4) clause specifies that the query should use 4 parallel execution servers. You can replace the number 4 with the number of parallel execution servers that you want to use for your query. Note that the PARALLEL DEGREE clause can only be used in the hint section of a query, which is indicated by the /*+ syntax. The hint section is used to give the optimizer additional information about how to process the query.
The next lesson explains how you can monitor CPU consumption.