Section 6.1. Understanding Your Application | Optimizing Oracle Performance

6.1 Understanding Your Application

As you learned in Chapter 3, you want to be able to trace exactly the actions motivated by a carefully specified user or batch program for a carefully specified time interval. As you'll see later in this chapter, the Oracle release 7, 8, and 9 kernels give you the ability to activate and deactivate extended SQL tracing only at the Oracle session level. Being able to control tracing only at the session level creates varying degrees of hardship for the data collection process, depending upon the architecture of your application. Before you can trace your application, unfortunately , you must understand its architecture.

The most difficult part of diagnosing performance problems in Oracle release 7, 8, or 9 applications is the collection of properly scoped diagnostic data. Once you collect properly scoped data, the diagnosis process runs very smoothly.

Let's begin with some definitions. A user action is a functional unit of work that some human being executes. A user action is the thing whose performance some user finds interesting (and therefore you find it interesting too). This action requires the execution of code that may exist on any or all of several architectural tiers (such as a client's browser, an application server, a database server, or various network devices).

The database server host is the tier on which this book focuses, because most performance problems can be diagnosed most efficiently by observing instrumentation produced by this tier . A user action may involve zero or many processes (or even threads) on the database server host. A process is an operating system object that is an instantiation of some executable program. You can identify an OS process by its unique OS process ID (PID), and you can monitor it with operating system tools. For example, the following Linux ps (report process status) output shows four processes (8233, 8325, 8326, and 8327) which are using only three different programs ( ksh , ps , and two copies of t ):

 $  ps  PID TTY          TIME CMD  8233 pts/4    00:00:00 ksh  8325 pts/4    00:00:00 t  8326 pts/4    00:00:00 t  8327 pts/4    00:00:00 ps

You will be interested primarily in two types of OS processes on your database server host. First and foremost, you will be interested in the Oracle server processes that share memory, access your Oracle database files, and do most of the work on most Oracle systems. These processes usually contain the string "oracle" in their names . The following Linux command produces a listing of all processes that contain the string "oracle" in the process table, but not the string "grep":

 $  ps -ef  grep oracle  grep -v grep  oracle     756     1  0 Feb04 ?        00:00:19 ora_pmon_V816 oracle     758     1  0 Feb04 ?        00:00:04 ora_dbw0_V816 oracle     760     1  0 Feb04 ?        00:00:03 ora_lgwr_V816 oracle     762     1  0 Feb04 ?        00:00:43 ora_ckpt_V816 oracle     764     1  0 Feb04 ?        00:00:01 ora_smon_V816 oracle     766     1  0 Feb04 ?        00:00:00 ora_reco_V816 oracle    8834  8833  0 16:12 ?        00:00:00 oracleV816 (DESCRIPTION=(LO oracle    8859  8858  0 16:13 ?        00:00:00 oracleV816 (DESCRIPTION=(LO

Note that this command has also displayed all of my system's Oracle background processes as well (because they're owned by the user called oracle ).

You might hear server processes called many names, including:

Server processes

Shadow processes

Kernel processes

Foreground processes

The second interesting type of OS process that exists on your server host is any client process that makes database connections. For example, it is common to run database call- intensive application programs such as reports or batch uploads on the database server host itself. This configuration decision makes excellent sense for any client program that consumes most of its total elapsed time waiting for database calls. In such a case, the cost of executing the client program's CPU instructions on the server more than compensates for the cost of flooding a network with masses of SQL*Net chit-chat between the client and oracle server processes.

Examples of Oracle application client programs include:

sqlplus (SQL*Plus)

f60run (Oracle*Forms)

FNDLIBR (Oracle Financials Concurrent Manager program)

PYUGEN (An Oracle Human Resources program)

An Oracle session (or, in this book, simply a session ) is a specific sequence of database calls that flow through a connection between a user process and an Oracle instance. You can identify a session by its unique identifier, the concatenation of V$SESSION.SID and V$SESSION.SERIAL# . For example, the following SQL*Plus output shows nine Oracle sessions:

 SQL>  select sid, serial#, username, type from v$session;  SID    SERIAL# USERNAME                       TYPE ---------- ---------- ------------------------------ ----------          1          1                                BACKGROUND          2          1                                BACKGROUND          3          1                                BACKGROUND          4          1                                BACKGROUND          5          1                                BACKGROUND          6          1                                BACKGROUND          7         13 SYSTEM                         USER          8         11 SYSTEM                         USER          9        337 CVM                            USER     9 rows selected.

Data collection is simple when a user action uses exactly one client process, one Oracle server process, and one Oracle session. Fortunately, this is what happens in many performance problem situations, such as long-running reports and batch jobs. Complexity in data collection grows when a user action involves the participation of more Oracle processes or more Oracle sessions. For example:

Oracle Multithreaded Server (MTS): In an MTS configuration, several client processes share a smaller number of Oracle server processes. This configuration reduces the number of process instantiations required to run an application with a large number of constantly connected but mostly idle users.
Connection pooling: In a connection pooling configuration, a single OS process (called a service ) on the middle tier creates a single Oracle connection and establishes a single Oracle session on a single Oracle server process. The service then makes database calls on behalf of many users within its single session. This type of configuration permits even greater scalability for large user counts than MTS configurations.

My colleagues and I see mind-bogglingly complex combinations of these technologies and more in the field, especially in environments where a single user action requires work from services that are distributed across databases. As I mentioned previously, collecting properly scoped diagnostic data is usually the most difficult part of problem diagnosis methods today. The good news is that once you figure out how to do it for a given architecture, executing further data collection tasks for that architecture becomes much easier. Furthermore, I expect that the architectural changes planned for Oracle release 10 simplify the process of collecting properly scoped data for an individual user action.

The key to successful extended SQL trace data collection is to understand how to identify the right Oracle sessions. For connection pooling architectures, the key is to identify which database calls and wait events map to the user action that you're diagnosing.

Top