Question:  Can you give me some tips on how to measure all of the sub-complements of Oracle response time?   I know that Oracle is only one part of my application but my end-users want to measure end-to-end-response time for their transactions.  How to I isolate the components of response time within Oracle?

Answer:  Measuring end-to-end Oracle response time involves many steps.  Before we explore the components of Oracle response time, please review these important notes on Oracle response time monitoring.

At the system-level, many shops with service-level agreements (SLA) that mandate that 99% of all transactions complete with sub-second response time.  However, there are some serious problems with measuring the "real" response time of any given transaction.

During a response time emergency, you can run response time diagnostic scripts.

If you are not a response time expert, it's often cheaper and more reliable to hire an Oracle expert to perform an Oracle response time health check.

Problems measuring "real" oracle response time

Oracle does not have complete user exits:  It is difficult to completely instrument all aspects of any Oracle transaction.

• Oracle is only one component of response time:  Oracle constantly communicates with the server RAM and CPU via "background processes".  These processes must be dispatched by the OS to receive machine cycles and resources.
  
• Network latency may vary widely:  This is especially important for Web systems.  In SQL*Forms it is possible to "instrument" a transaction with end-to-end response time, but in most applications the only 100% reliable way to measure a response time SLA involves.

Shops with strict SLA's will "plant" fully instrumented "dummy" terminals throughout the network that measure and retain end-to-end response time.

Remember, Oracle is NOT a science and there are no equations or formulas that can help predict Oracle response time.  Oracle knows this and gives us tools for empirical testing of SQL workloads without the needs to create artificial test cases and fake workloads to test response time.

You can also fire-off representative transactions to replicate the actions of a system user.  These use real-world SQL workloads using a "SQL replay" method, similar to RAT.

Formally, an Oracle transaction consists of one or more SQL statements that fulfill a single business function.  For example, a screen that displays order details from a customer might consists of a single SQL statement that accesses eight tables, or eight SQL statements that access a single table.

Tip:  Minimizing trips to Oracle (minimizing context switches) with the PL/SQL FORALL and BULK COLLECT operators can dramatically improve insert and read response time when performing array processing and DML.

For each SQL statement sent to the Oracle database, each SQL statement consists of the following steps:

• RESPONSE TIME STEP 1: Inbound SQL statements is transmitted to Oracle (network time).
• RESPONSE TIME STEP 2: Oracle receives and prepares the SQL statement for execution.
• RESPONSE TIME STEP 3: Execute SQL statement.
• RESPONSE TIME STEP 4: SQL sorting/aggregation.
• RESPONSE TIME STEP 5: Rowset transmission to application (network time)

The 10053 trace (SQL*Trace, TKPROF) is a good way to expose the internal machinations for any given SQL statement, but examining a single SQL statement is a busy system is like examining an elephant using a microscope. 
 
RESPONSE TIME STEP 1: Inbound SQL statements is transmitted to Oracle (network time)

RESPONSE TIME STEP 2: Oracle receives and prepares the SQL statement for execution:

if sql_hash = found
then
   load SQL executable
else
   check SQL syntax
   check SQL semantics (I/O against data dictionary files)
   evaluate optimal execution plan (I/O against CBO statistics)
   generate SQL executable (native file I/O calls)

RESPONSE TIME STEP 3: Execute SQL statement

if degree=1
then
   send native I/O request
   fetch rows from data files (I/O against disk array)
   transmit rowset to Oracle (network time)
else
   spawn parallel query coordinator
   for each spawn parallel query slave processes
      send native I/O request
      fetch rows from data files (I/O against disk array)
   wait for last slave to complete fetch
   transmit rowset to Oracle (network time)

RESPONSE TIME STEP 4: SQL sorting/aggregation

if sorting_or_aggregation_required
   if no_space_in_RAM
   then
     send native I/O requests against TEMP tablespace
     transmit sorted rowset to Oracle (network time)
else
   sort and/or aggregate in RAM

 
RESPONSE TIME STEP 5: Rowset transmission to application (network time)

Measuring system-wide response time

At the system-level many shops with service-level agreements (SLA) that mandate that 99% of all transactions complete with sub-second response time.  However, there are some serious problems with measuring the "real" response time of any given transaction:
Oracle does not have complete user exits:  It is difficult to completely instrument all aspects of any Oracle transaction.

Remember, you can fire you can fire-off representative transactions A a SQL tuning set)to replicate the actions of a system user.  These use real-world SQL workloads using a "SQL replay" method, similar to RAT, as seen in the Oracle SQL Performance Analyzer.

Oracle External Factors

In each of the above steps Oracle must interact with the external operating system.  Oracle background processes must interact with the OS dispatcher

•  Internal RAM operations:  These require CPU cycles as the Oracle background processes communicate with the SGA and PGA regions.
  
• I/O request operations:  All background processes that write data must issue native block I/O requests against the data files.

In sum, measuring Oracle response time is both complex and tedious involving many layers of interactions within the components of Oracle.