Lesson 2	Monitoring CPU usage on a database server
Objective	Monitor CPU usage with the ps command.

Monitor CPU usage via the ps command

Processes consume CPU Resources

As you already know, an Oracle instance consists of a set of background processes that begin at database startup time and continue to run on the database server until the database is stopped. These processes constitute the Oracle instance and are the processes that will consume CPU resources on behalf of the Oracle database.

Binding Oracle Processes to specific CPUs: In environments where a database server has multiple CPUs, many DBAs have the option of dedicating Oracle processes to run on specific CPUs. This is an advantage because the Oracle background processes often loose their context when the OS directs the process to use another CPU. The context of a process is the internal instruction set that consists of the CPU registers and their current contents. When an Oracle process switches CPUs, these instruction sets must be moved to the new CPU, slowing down execution time. The implementation details are different for every OS, but in IBMs AIX operating system, process binding is achieved with the bindprocessor UNIX command. Check the Oracle OS-specific documentation for your environment to see how to do process binding in your environment.

Processes are Competing

These processes are always executing and competing for CPU resources on our database server. In order to check for processes that are consuming an inordinate amount of CPU cycles, you can use the ps command. For example, sometimes Oracle sessions may become disconnected from the database and spin, causing a huge amount of CPU activity. The following example show how to monitor the amount of resources that each process is consuming.

diogenes:>

A terminal output of Oracle background processes.

diogenes:> ps -ef | grep donl
UID        PID  PPID  C STIME TTY          TIME CMD
Oracle    10040     1   0 14:05:45 -        0:18 ora_dbw0_donl
oracle    15176     1   0 14:05:46 -        0:00 ora_reco_donl
oracle    25920     1   0 14:05:45 -        29:12 ora_ckpt_donl
oracle    28220     1   0 14:05:45 -        1:20 ora_lgwr_donl
oracle    35363     1   0 14:05:44 -        0:02 ora_pmon_donl
oracle    42564     1   0 14:05:46 -        0:16 ora_smon_donl

Analysis:

The text shows a snapshot of Oracle processes related to a database instance, filtered by the string "donl" in the command name (CMD).
The UID column indicates the user identity under which each process is running, all listed here as "Oracle" or "oracle".
The PID is the process ID for each running process.
The PPID column shows that each process is a child of the process with PID 1, which is typically the init process in UNIX systems.
The C column for processor utilization is zero for all processes except one, suggesting they are not actively using CPU time at the snapshot moment.
STIME indicates the start time of each process.
TTY is not associated with a terminal, denoted by -, meaning these are background processes.
TIME gives the cumulative CPU time each process has used.
CMD lists specific Oracle background processes:
- ora_dbw0_donl: Oracle database writer
- ora_reco_donl: Oracle recovery process
- ora_ckpt_donl: Oracle checkpoint process
- ora_lgwr_donl: Oracle log writer
- ora_pmon_donl: Oracle process monitor
- ora_smon_donl: Oracle system monitor

This output is a typical representation of Oracle background processes necessary for the normal operation of an Oracle database instance. Each named process has a specific role in managing the database's stability, performance, and recovery mechanisms.

Analyze the following uploaded image. Print off the 1) text and 2) any relevant code in the image.

A terminal output of Oracle background processes, filtered and sorted by a specific condition.

diogenes:>ps -ef | grep orasort | tail 
oracle    37894     1   0 15:06:30 -        1:59 oracledon1 (DESCRIPTION=(LOCAL=NO)(SDU=8192))
oracle    13216     1   0 07:10:33 -        2:23 oracledon1 (DESCRIPTION=(LOCAL=NO)(SDU=8192))
oracle    16586     1   0 12:05:23 -        2:24 oracledon1 (DESCRIPTION=(LOCAL=NO)(SDU=8192))
oracle    12872     1   0 09:43:04 -        2:53 oraclehr1 (DESCRIPTION=(LOCAL=NO)(SDU=8192))
oracle    71318     1   0 07:19:29 -        3:53 oracledon1 (DESCRIPTION=(LOCAL=NO)(SDU=8192))
oracle    104258    1   0 08:18:12 -        4:57 oracledon1 (DESCRIPTION=(LOCAL=NO)(SDU=8192))
oracle    145334    1   0 05:04:48 -        5:01 oracledon1 (DESCRIPTION=(LOCAL=NO)(SDU=8192))
oracle    46980     1  45 10:38:41 -        8:38 oracledon1 (DESCRIPTION=(LOCAL=NO)(SDU=8192))
oracle    150538    1   0 08:21:13 -        9:31 oraclehr1 (DESCRIPTION=(LOCAL=NO)(SDU=8192))

ps -ef | grep orasort | tail

The command `ps -ef | grep orasort | tail` is used to display the last few lines of the output from `ps -ef`, filtered for entries containing "orasort".
The `UID` column indicates the user identity under which each process is running, all listed here as "oracle".
The `PID` is the process ID for each running process.
The `PPID` shows that each process is a child of the process with PID 1, which is typically the init process in UNIX systems.
The `C` column for processor utilization shows mostly zero, indicating minimal current CPU usage, except for one process which is significantly higher (45).
`STIME` indicates the start time of each process.
`TTY` is not associated with a terminal, shown as `-`, indicating these are background processes.
`TIME` gives the cumulative CPU time each process has used.
`CMD` indicates the command name, appended with a connection description `(DESCRIPTION=(LOCAL=NO)(SDU=8192))`, which provides details about the Oracle network connection, suggesting these processes are not connected locally and use a specific session data unit (SDU) size.

This output is particularly useful for monitoring specific Oracle processes, their resource usage, and networking parameters.

Start by using the ps command to view how each background process is using CPU resources. Type (S1)ps -ef|grep don1(S0).
diogenes>
ps -ef | grep don1
Please check your entry and try again.
Here are all of the Oracle processes on our host. Notice that the database writer process (dbw0), the recoverer process (reco) the checkpoint process (ckpt), the log writer (lgwr), the process monitor process (pmon) and the system monitor process (smon) all make up the Oracle instance. Click anywhere on the screen to continue the simulation.

Within the ps command we see several relevant columns, including the user who owns the process (UID) and the time that the process started (STIME), and most importantly, the TIME column. This column shows the total amount of CPU consumed by the process.
To see the top consumers of CPU on a database server, issue the following command:
```
ps -ef | grep ora | sort | tail
```
diogenes:>
ps -ef | grep ora | sort | tail
Please check your entry and try again.
Here we have directed UNIX to display the top CPU consumers, and we see that process 150538 is the top CPU consumer with 9:31 minutes of CPU time. This is the end of the simulation. Click the Exit button.

Using the ps command to monitor CPU usage

Start by using the ps command to view how each background process is using CPU resources. Type ps –ef|grep don1.
Here are all of the Oracle processes on our host. Notice that the database writer process (dbw0), the recoverer process (reco) the checkpoint process (ckpt), the log writer (lgwr), the process monitor process (pmon) and the system monitor process (smon) all make up the Oracle instance. Click anywhere on the screen to continue the simulation.
Within the ps command we see several relevant columns, including the user who owns the process (UID) and the time that the process started (STIME), and most importantly, the TIME column. This column shows the total amount of CPU consumed by the process. Click anywhere again to continue.
To see the top consumers of CPU on a database server, issue the following command: ps –ef|grep ora|sort +6|tail.
Here we have directed UNIX to display the top CPU consumers, and we see that process 150538 is the top CPU consumer with 9:31 minutes of CPU time. This is the end of the simulation.

The next lesson demonstrates another way to monitor CPU usage.

ps -ef | grep ora | sort +6 | tail

The `+6` before the last pipe in your command is considered incorrect in modern versions of the `sort` command.
The `+6` syntax is deprecated and may not work as intended on current UNIX or Linux systems.
What Does the `+6` Indicate?
In older versions of UNIX, the `sort` command used the `+n` syntax to specify the sort field, where `n` is the field number starting from zero. So, `+6` would mean sorting starting from the 7th field (since counting starts at zero).

Fields in `ps -ef` Output: The `ps -ef` command outputs several columns, typically:
- UID: User ID
- PID: Process ID
- PPID: Parent Process ID
- C: CPU utilization
- STIME: Start time
- TTY: Terminal type
- TIME: Cumulative CPU time
- CMD: Command name and arguments
Using `+6`: The `+6` tells `sort` to begin sorting from the 7th field (`TIME`), effectively sorting the processes based on their CPU time.

Why Is It Incorrect Now?

Deprecated Syntax: The `+n` syntax has been deprecated in favor of the `-k` option in modern versions of `sort`.
Incompatibility: Using `+6` may result in errors or unexpected behavior because newer `sort` implementations do not recognize this syntax.

Correct Syntax with `-k` Option
You should replace `sort +6` with `sort -k7`. The `-k` option specifies the sort key field, and fields are counted starting from 1 in this syntax.
Updated Command:

ps -ef | grep ora | sort -k7 | tail

Explanation:

`-k7` tells `sort` to use the 7th field (`TIME`) as the sort key.
This syntax is compatible with modern `sort` versions and is the recommended way to specify sort keys.

Understanding the Full Command

`ps -ef`: Lists all running processes in a full-format listing.
`grep ora`: Filters the list to include only lines containing "ora" (commonly associated with Oracle processes).
`sort -k7`: Sorts the filtered list based on the 7th field (`TIME` column).
`tail`: Displays the last 10 lines of the sorted output.

Example
Assuming the output of `ps -ef | grep ora` is:

oracle   1234     1  0 08:00 ?        00:00:05 ora_pmon_db1
oracle   1235     1  0 08:05 ?        00:00:02 ora_dbw0_db1
oracle   1236     1  0 08:10 ?        00:00:03 ora_lgwr_db1

Using `sort -k7` will sort the processes based on the `TIME` column (CPU time), allowing you to see which Oracle processes have been consuming the most CPU time.
Why Use the `-k` Option?

Standardization: The `-k` option is part of the POSIX standard and is supported across different UNIX and Linux distributions.
Clarity: It clearly specifies the sort key and is more readable.
Flexibility: Allows for complex sorting by specifying start and end positions of the sort key.

Additional Tips

Check Your `sort` Version: You can check the version of `sort` by running `sort --version`. This can help you determine if the deprecated syntax is supported.
Consult the Manual: Use `man sort` to read the manual and understand the available options for your system's version of `sort`.

Conclusion

Replace Deprecated Syntax: Update `sort +6` to `sort -k7` to ensure compatibility and correct functionality.
Understanding Fields: Remember that in `-k` syntax, fields start counting from 1, not 0.
Ensure Correctness: Always test your commands to make sure they produce the expected output, especially when dealing with different system versions.

By updating your command, you ensure that it works correctly on modern systems and aligns with current best practices.

[1] spinning: Spinning means that the process continues to look for the availability of the latch after fixed intervals of time, during which it sleeps.