C H A P T E R  6

Analyzing Operating System Performance

Solving database performance issues sometimes requires the use of operating system (OS) utilities. These tools often provide information that can help isolate database performance problems. Consider the following situations:

• You're running multiple databases and multiple applications on one server and want to use OS utilities to identify which database (and corresponding process) is consuming the most operating system resources. This approach is invaluable when one database application is consuming resources to the point of causing other databases on the box to perform poorly.
• You need to verify if the database server is adequately sized for current application workload in terms of CPU, memory, disk I/O, and network bandwidth.
• An analysis is needed to determine at what point the server will not be able to handle larger (future) workloads.
• You've used database tools to identify system bottlenecks and want to double-check the analysis via operating system tools.

In these scenarios, to effectively analyze, tune, and troubleshoot, you'll need to employ OS tools to identify resource-intensive processes. Furthermore, if you have multiple databases and applications running on one server, when troubleshooting performance issues, it's often more efficient to first determine which database and process is consuming the most resources. Operating system utilities help pinpoint whether the bottleneck is CPU, memory, disk I/O, or a network issue. In Linux/Unix environments, once you have the operating system identifier, you can then query the database to show any corresponding database processes and SQL statements.

Take a look at Figure 6-1. This flowchart details the decision-making process and the relevant Linux/Unix operating system tools that a DBA steps through when diagnosing sluggish server performance. For example, when you're dealing with performance problems, one common first task is to log on to the box and quickly check for disk space issues using OS utilities like df and du. A full mount point is a common cause of database unavailability.

Figure 6-1. Troubleshooting poor performance

After inspecting disk space issues, the next task is to use an OS utility such as vmstat, top, or ps to determine what type of bottleneck you have. For example, is sluggish performance related to a disk I/O issue, CPU, memory, or the network? After determining the type of bottleneck, the next step is to determine if a database process is causing the bottleneck.

The ps command is useful for displaying the process name and ID of the resource-consuming session. When you have multiple databases running on one box, you can determine which database is associated with the process from the process name. Once you have the process ID and associated database, you can then log on to the database and run SQL queries to determine if the process is associated with a SQL query. If the problem is SQL-related, then you can identify further details regarding the SQL query and where it might be tuned.

Figure 6-1 encapsulates the difficulty of troubleshooting performance problems. Correctly pinpointing the cause of performance issues and recommending an efficient solution is often easier said than done. When trying to resolve issues, some paths result in relatively efficient and inexpensive solutions, such as terminating a runaway operating system process or regenerating fresh statistics. Other decisions may lead you to conclude that you need to add expensive hardware or redesign the system. Your performance tuning conclusions can have long-lasting financial impact on your company and thus influence your ability to retain a job. Obviously you want to focus on the cause of a performance problem and not just address the symptom. If you can consistently identify the root cause of the performance issue and recommend an effective and inexpensive solution, this will greatly improve your employment opportunities.

The focus of this chapter is to provide detailed examples that show how to use Linux/Unix operating system utilities to identify server performance issues. These utilities are invaluable for providing extra information used to diagnose performance issues outside of tools available within the database. Operating system utilities act as an extra set of eyes to help zero in on the cause of poor database performance.

6-1. Detecting Disk Space Issues

Problem

Users are reporting that they can't connect to a database. You log on to the database server, attempt to connect to SQL*Plus, and receive this error:

You want to quickly determine if a mount point is full and where the largest files are within this mount point.

Solution

In a Linux/Unix environment, use the df command to identify disk space issues. This example uses the -h to format the output so that space is reported in megabytes or gigabytes:

Here is some sample output:

The prior output indicates that the root (/) file system is full on this server. In this situation, once a full mount point is identified, then use the find command to locate the largest files contained in a directory structure. This example navigates to the ORACLE_HOME directory and then connects the find, ls, sort, and head commands to identify the largest files beneath that directory:

If you have a full mount point, also consider looking for the following types of files that can be moved or removed:

• Deleting database trace files
• Removing large Oracle Net log files
• Moving, compressing, or deleting old archive redo log files
• Removing old installation files or binaries
• If you have datafiles with ample free space, consider resizing them to smaller sizes

Another way to identify where the disk space is being used is to find the largest space-consuming directories beneath a given directory. This example combines the du, sort, and head commands to show the ten largest directories beneath the current working directory:

The prior command is particularly useful for identifying a directory that might not necessarily have large files in it, but lots of small files consuming space (like trace files).

How It Works

When you have a database that is hung because there is little or no free disk space, you should quickly find files that can be safely removed without compromising database availability. On Linux/Unix servers, the df, find, and du commands are particularly useful.

When working with production database servers, it's highly desirable to proactively monitor disk space so that you're warned about a mount point becoming full. Listed next is a simple shell script that monitors disk space for a given set of mount points:

You'll have to modify the script to match your environment. For example, the second line of the script specifies the mount points on the box being monitored:

These mount points should match the mount points listed in the output of the df –h command. For a Solaris box that this script runs on, here's the output of df:

Also, depending on what version of Linux/Unix you're using, you'll have to modify this line as well:

The prior line of code depends on the output of the df command, which can vary somewhat depending on the operating system vendor and version. For example, on one Linux system, the output of df might span two lines and reports on Use% instead of capacity, so in this scenario, the usedSpc variable is populated as shown:

The prior code (broken into two lines to fit on the page) runs several Linux/Unix commands and places the output in the usedSpc variable. The command first runs df –h, which is piped to the awk command. The awk command takes the output and prints out the fifth column. This is piped to the grep command, which uses –v to eliminate the word Use% from the output. This is finally piped to the cut command, which cuts out the “%” character from the output.

On a Linux/Unix system, a shell script such as the prior one can easily be run from a scheduling utility such as cron. For example, if the shell script is named filesp.bsh, here is a sample cron entry:

The prior entry instructs the system to run the filesp.bsh shell script at seven minutes after the hour for every hour of the day.

6-2. Identifying System Bottlenecks (vmstat)

Problem

You want to determine if a server performance issue is specifically related to disk I/O, CPU, memory, or network.

Solution

Use vmstat to determine where the system is resource-constrained. For example, the following command reports on system resource usage every five seconds on a Linux system:

Here is some sample output:

To exit out of vmstat in this mode, press Ctrl+C. You can also have vmstat report for a specific number of runs. For example, this instructs vmstat to run every six seconds for a total of ten reports:

Here are some general heuristics you can use when interpreting the output of vmstat:

• If the wa (time waiting for I/O) column is high, this is usually an indication that the storage subsystem is overloaded. See Recipe 6-6 for identifying the sources of I/O contention.
• If b (processes sleeping) is consistently greater than 0, then you may not have enough CPU processing power. See Recipes 6-5 and 6-9 for identifying Oracle processes and SQL statements consuming the most CPU.
• If so (memory swapped out to disk) and si (memory swapped in from disk) are consistently greater than 0, you may have a memory bottleneck. See Recipe 6-5 for details on identifying Oracle processes and SQL statements consuming the most memory.

How It Works

The vmstat (virtual memory statistics) tool helps quickly identify bottlenecks on your server. Use the output of vmstat to help determine if the performance bottleneck is related to CPU, memory, or disk I/O. Table 6-1 describes the columns available in the output of vmstat. These columns may vary somewhat depending on your operating system and version.

OS WATCHER

6-3. Identifying System Bottlenecks (Solaris)

Problem

You're working on a Solaris system, and irate users are reporting the database application is slow. You have multiple databases running on this box and want to identify which processes are consuming the most CPU resources. Once the resource-consuming processes are identified at the OS, then you want to map them (if possible) to a database process.

Solution

On most Solaris systems, the prstat utility is used to identify which processes are consuming the most CPU resources. For example, you can instruct the prstat to report system statistics every five seconds:

Here is some sample output:

Type q or press Ctrl+C to exit prstat. In the prior output, process 16609 is consistently showing up as a top CPU-consuming process.

After identifying a top resource-consuming process, you can determine which database the process is associated with by using the ps command. This example reports on process information associated with the PID of 16609:

In this example, the name of the process is ora_dw00_ENGDEV and the associated database is ENGDEV.

How It Works

If you're working on a Solaris server, the top utility is oftentimes not installed. In these environments, the prstat command can be used to determine top resource-consuming processes on the system. Table 6-2 describes several of the columns displayed in the default output of prstat.

6-4. Identifying Top Server-Consuming Resources (top)

Problem

You have a Linux server that hosts multiple databases. Users are reporting sluggishness with an application that uses one of the databases. You want to identify which processes are consuming the most resources on the server and then determine if the top consuming process is associated with a database.

Solution

The top command shows a real-time display of the highest resource-consuming processes on a server. Here's the simplest way to run top:

Listed next is a fragment of the output:

Type q or press Ctrl+C to exit top. In the prior output, the first section of the output displays general system information such as how long the server has been running, number of users, CPU information, and so on. The second section shows which processes are consuming the most CPU resources (listed top to bottom). In the prior output, the process ID of 19888 is consuming a large amount of CPU. To determine which database this process is associated with, use the ps command:

Here is the associated output:

In the prior output, the fourth column displays the value of oracleO11R2. This indicates that this is an Oracle process associated with the O11R2 database. If the process continues to consume resources, you can next determine if there is a SQL statement associated with the process (see Recipe 6-9) or terminate the process (see Recipe 6-10).

How It Works

If installed, the top utility is often the first investigative tool employed by DBAs and system administrators to identify resource-intensive processes on a server. If a process is continuously consuming excessive system resources, then you should further determine if the process is associated with a database and a specific SQL statement.

By default, top will repetitively refresh (every few seconds) information regarding the most CPU-intensive processes. While top is running, you can interactively change its output. For example, if you type >, this will move the column that top is sorting one position to the right. Table 6-3 lists the most useful hot key features to alter the top display to the desired format.

Table 6-4 describes several of the columns displayed by top. Use these descriptions to help interpret the output.

6-5. Identifying CPU and Memory Bottlenecks (ps)

Problem

You want to quickly isolate which processes on the server are consuming the most CPU and memory resources.

Solution

The ps (process status) command is handy for quickly identifying top resource-consuming processes. For example, this command displays the top ten CPU-consuming resources on the box:

Here is a partial listing of the output:

In the prior output, the process named oracleO11R2 is consuming an inordinate amount of CPU resources on the server. The process name identifies this as an Oracle process associated with the O11R2 database.

Similarly, you can also display the top memory-consuming processes:

How It Works

The Linux/Unix ps command displays information about currently active processes on the server. The pcpu switch instructs the process status to report the CPU usage of each process. Similarly the pmem switch instructs ps to report on process memory usage. This gives you a quick and easy way to determine which processes are consuming the most resources.

When using multiple commands on one line (such as ps, sort, and head), it's often desirable to associate the combination of commands with a shortcut (alias). Here's an example of creating aliases:

Now instead of typing in the long line of commands, you can use the alias—for example:

Also consider establishing the aliases in a startup file (like .bashrc or .profile) so that the commands are automatically defined when you log on to the database server.

6-6. Identifying I/O Bottlenecks

Problem

You are experiencing performance problems and want to determine if the issues are related to slow disk I/O.

Solution

Use the iostat command with the -x (extended) option combined with the -d (device) option to generate I/O statistics. This next example displays extended device statistics every ten seconds:

You need a fairly wide screen to view this output; here's a partial listing:

This periodic extended output allows you to view in real time which devices are experiencing spikes in read and write activity. To exit from the previous iostat command, press Ctrl+C. The options and output may vary depending on your operating system. For example, on some Linux/Unix distributions, the iostat output may report the disk utilization as %b (percent busy).

When trying to determine whether device I/O is the bottleneck, here are some general guidelines when examining the iostat output:

• Look for devices with abnormally high blocks read or written per second.
• If any device is near 100% utilization, that's a strong indicator I/O is a bottleneck.

How It Works

The iostat command can help you determine whether disk I/O is potentially a source of performance problems. Table 6-5 describes the columns displayed in the iostat output.

You can also instruct iostat to display reports at a specified interval. The first report displayed will report averages since the last server reboot; each subsequent report shows statistics since the previously generated snapshot. The following example displays a device statistic report every three seconds:

You can also specify a finite number of reports that you want generated. This is useful for gathering metrics to be analyzed over a period of time. This example instructs iostat to report every 2 seconds for a total of 15 reports:

When working with locally attached disks, the output of the iostat command will clearly show where the I/O is occurring. However, it is not that clear-cut in environments that use external arrays for storage. What you are presented with at the file system layer is some sort of a virtual disk that might also have been configured by a volume manager. In virtualized storage environments, you'll have to work with your system administrator or storage administrator to determine exactly which disks are experiencing high I/O activity.

Once you have determined that you have a disk I/O contention issue, then you can use utilities such as AWR (if licensed), Statspack (no license required), or the V$ views to determine if your database is I/O stressed. For example, the AWR report contains an I/O statistics section with the following subsections:

• IOStat by Function summary
• IOStat by Filetype summary
• IOStat by Function/Filetype summary
• Tablespace IO Stats
• File IO Stats

You can also directly query data dictionary views such as V$SQL to determine which SQL statements are using excessive I/O—for example:

To determine which sessions are currently waiting for I/O resources, query V$SESSION:

To view objects that are waiting for I/O resources, run a query such as this:

Once you have identified queries (using the prior queries in this section), then consider the following factors, which can cause a SQL statement to consume inordinate amounts of I/O:

• Poorly written SQL
• Improper indexing
• Improper use of parallelism (which can cause excessive full table scans)

You'll have to examine each query and try to determine if one of the prior items is the cause of poor performance as it relates to I/O.

6-7. Identifying Network-Intensive Processes

Problem

You're investigating performance issues on a database server. As part of your investigation, you want to determine if there are network bottlenecks on the system.

Solution

Use the netstat (network statistics) command to display network traffic. Perhaps the most useful way to view netstat output is with the -ptc options. These options display the process ID and TCP connections, and they continuously update the output:

Press Ctrl+C to exit the previous command. Here's a partial listing of the output:

If the Send-Q (bytes not acknowledged by remote host) column has an unusually high value for a process, this may indicate an overloaded network. The useful aspect about the previous output is that you can determine the operating system process ID (PID) associated with a network connection. If you suspect the connection in question is an oracle session, you can use the techniques described in the “Solution” section of Recipe 6-9 to map an operating system PID to an Oracle process or SQL statement.

How It Works

When experiencing performance issues, usually the network is not the cause. Most likely you'll determine that bad performance is related to a poorly constructed SQL statement, inadequate disk I/O, or not enough CPU or memory resources. However, as a DBA, you need to be aware of all sources of performance bottlenecks and how to diagnose them. In today's highly interconnected world, you must possess network troubleshooting and monitoring skills. The netstat utility is a good starting place for monitoring server network connections.

6-8. Troubleshooting Database Network Connectivity

Problem

A user has reported that he or she can't connect to a database. You know there are many components involved with network connectivity and want to figure out the root cause of the problem.

Solution

Use these steps as guidelines when diagnosing Oracle database network connectivity issues:

1. Use the operating system ping utility to determine whether the remote box is accessible—for example:
   
   $ ping dwdb
dwdb is alive
   
   If ping doesn't work, work with your system or network administrator to ensure you have server-to-server connectivity in place.
2. Use telnet to see if you can connect to the remote server and port (that the listener is listening on)—for example:
   
   $ telnet ora03 1521
Trying 127.0.0.1...
Connected to ora03.
Escape character is '^]'.
   
   The prior output indicates that connectivity to a server and port is okay. If the prior command hangs, then contact your SA or network administrator for further assistance.
3. Use tnsping to determine whether Oracle Net is working. This utility will verify that an Oracle Net connection can be made to a database via the network—for example:
   
   $ tnsping dwrep
..........
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)
(HOST = dwdb1.us.farm.com)(PORT = 1521))
(CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = DWREP)))
OK (500 msec)
   
   If tnsping can't contact the remote database, verify that the remote listener and database are both up and running. On the remote box, use the lsnrctl status command to verify that the listener is up. Verify that the remote database is available by establishing a local connection as a non-SYS account (SYS can often connect to a troubled database when other schemas will not work).
4. Verify that the TNS information is correct. If the remote listener and database are working, then ensure that the mechanism for determining TNS information (like the tnsnames.ora file) contains the correct information.
   Sometimes the client machine will have multiple TNS_ADMIN locations and tnsnames.ora files. One way to verify whether a particular tnsnames.ora file is being used is to rename it and see whether you get a different error when attempting to connect to the remote database.

How It Works

Network connectivity issues can be troublesome to diagnose because there are several architectural components that have to be in place for it to work correctly. You need to have the following in place:

• A functional network
• Open ports from point to point
• Oracle Net correctly installed and configured
• Target database and listener up and running
• Correct navigational information from the client to the target database

If you're still having issues, examine the client sqlnet.log file and the remote server listener.log file. Sometimes these log files will show additional information that will pinpoint the issue.

6-9. Mapping a Resource-Intensive Process to a Database Process

Problem

It's a dark and stormy night, and the system is performing poorly. You identify an operating system–intensive process on the box. You want to map an operating system process back to a database process. If the database process is a SQL process, you want to display the user of the SQL statement and also the SQL.

Solution

In Linux/Unix environments, if you can identify the resource-intensive operating system process, then you can easily check to see if that process is associated with a database process. The process consists of the following:

1. Run an OS command to identify resource-intensive processes and associated IDs.
2. Identify the database associated with the process.
3. Extract details about the process from the database data dictionary views.
4. If it's a SQL statement, get those details.
5. Generate an execution plan for the SQL statement.

For example, suppose you identify the top CPU-consuming queries with the ps command:

Here is some sample output:

The prior output identifies one operating system process consuming an excessive amount of CPU (16.4%). The process ID is 11026 and name is oracleDWREP. From the process name, it's an Oracle process associated with the DWREP database.

You can determine what type of Oracle process this is by querying the data dictionary:

The prior script prompts you for the operating system process ID. Here is the output for this example:

The output indicates that this is a SQL*Plus process with a database SID of 410 and SERIAL# of 30653. You'll need this information if you decide to terminate the process with the ALTER SYSTEM KILL SESSION statement (see Recipe 6-10 for details).

In this example, since the process is running a SQL statement, further details about the query can be extracted by generating an execution plan:

You'll be prompted for the sql_id when you run the prior statement (in this example, the sql_id is by3c8848gyngu). Here is a partial listing of the output:

This output will help you determine the efficiency of the SQL statement and provide insight on how to tune it. Refer to Chapter 9 for details on how to manually tune a query and Chapter 11 for automated SQL tuning.

How It Works

The process described in the “Solution” section of this recipe allows you to quickly identify resource-intensive processes, then map the OS process to a database process, and subsequently map the database process to a SQL statement. Once you know which SQL statement is consuming resources, then you can generate an execution plan to further attempt to determine any possible inefficiencies.

Sometimes the resource-consuming process will not be associated with a database. In these scenarios, you'll have to work with your SA to determine what the process is and if it can be tuned or terminated.

Also, you may encounter resource-intensive processes that are database-specific but not associated with a SQL statement. For example, you might have a long-running RMAN backup process, Data Pump, or PL/SQL jobs running. In these cases, work with your DBA to identify whether these types of processes can be tuned or killed.

ORADEBUG

Use oradebug help to display all options available.

6-10. Terminating a Resource-Intensive Process

Problem

You have identified a process that is consuming inordinate amounts of system resources (see Recipe 6-9) and determined that it's a runaway SQL statement that needs to be killed.

Solution

There are three basic ways to terminate a SQL process:

• If you have access to the terminal where the SQL statement is running, you can press Ctrl+C and attempt to halt the process.
• Determine the session ID and serial number, and then use the SQL ALTER SYSTEM KILL SESSION statement.
• Determine the operating system process ID, and use the kill utility to stop the process.

If you happen to have access to the terminal from which the resource-consuming SQL statement is running, you can attempt to press Ctrl+C to terminate the process. Oftentimes you don't have access to the terminal and will have to use a SQL statement or an operating system command to terminate the process.

Using SQL to Kill a Session

If it's an Oracle process and you have identified the SID and SERIAL# (see Recipe 6-9), you can terminate a process from within SQL*Plus. Here is the general syntax:

In the prior syntax statement, integer1 is the value of the SID column and integer2 is the value from the SERIAL# column (of V$SESSION). In a RAC environment, you can optionally specify the value of the instance ID for integer3. The instance ID can be retrieved from the GV$SESSION view.

Here's an example that terminates a process with a SID of 1177 and a SERIAL# of 38583:

If successful, you should see this output:

When you kill a session, this will mark the session as terminated, roll back active transactions (within the session), and release any locks (held by the session). The session will stay in a terminated state until any dependent transactions are rolled back. If it takes a minute or more to roll back the transaction, Oracle reports the session as “marked to be terminated” and returns control to the SQL prompt. If you specify IMMEDIATE (optional), Oracle will roll back any active transactions and immediately return control back to you.

Using the OS Kill Command

If you have access to the database server and access to an operating system account that has privileges to terminate an Oracle process (such as the oracle OS account), you can also terminate a process directly with the kill command.

For example, suppose you run the ps command and have done the associated work to determine that you have a SQL statement that has been running for hours and needs to be terminated. The kill command directly terminates the operating system process. In this example, the process ID of 6254 is terminated:

How It Works

Sometimes you'll find yourself in a situation where you need to kill hung SQL processes, database jobs, or SQL statements that are consuming inordinate amounts of resources. For example, you may have a test server where a job has been running for several hours, is consuming much of the server resources, and needs to be stopped so that other jobs can continue to process in a timely manner.

Manually killing a SQL statement will cause the transaction to be rolled back. Therefore take care when doing this. Ensure that you are killing the correct process. If you erroneously terminate a critical process, this obviously will have an adverse impact on the application and associated data.