CPU Usage | Process   «Prev  Next»
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:
  1. Monitor CPU usage with the ps command
  2. Execute and interpret vmstat
  3. Describe dispatching priority
  4. Run the nice command to change CPU dispatching priorities

In Oracle, how can I create a parallel query that specifies the number of CPUs with the 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:
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.