Call now: 252-767-6166  
Oracle Training Oracle Support Development Oracle Apps

 
 Home
 E-mail Us
 Oracle Articles
New Oracle Articles


 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog


 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Analysis
 Design
 Implementation
 Oracle Support


 SQL Tuning
 Security

 Oracle UNIX
 Oracle Linux
 Monitoring
 Remote s
upport
 Remote plans
 Remote
services
 Application Server

 Applications
 Oracle Forms
 Oracle Portal
 App Upgrades
 SQL Server
 Oracle Concepts
 Software Support

 Remote S
upport  
 Development  

 Implementation


 Consulting Staff
 Consulting Prices
 Help Wanted!

 


 Oracle Posters
 Oracle Books

 Oracle Scripts
 Ion
 Excel-DB  

Don Burleson Blog 


 

 

 


 

 

 
 

Oracle response time components

Oracle Database Tips by Burleson Consulting

January 7, 2015

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.

 
Get the Complete
Oracle SQL Tuning Information 

The landmark book "Advanced Oracle SQL Tuning  The Definitive Reference"  is filled with valuable information on Oracle SQL Tuning. This book includes scripts and tools to hypercharge Oracle 11g performance and you can buy it for 30% off directly from the publisher.

 


 

 

Burleson is the American Team

Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals.  Feel free to ask questions on our Oracle forum.

Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications.

Errata?  Oracle technology is changing and we strive to update our BC Oracle support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:  and include the URL for the page.


                    









Burleson Consulting

The Oracle of Database Support

Oracle Performance Tuning

Remote DBA Services


 

Copyright © 1996 -  2016

All rights reserved by Burleson

Oracle ® is the registered trademark of Oracle Corporation.


 

��  
 
 
Oracle Training at Sea
 
 
 
 
oracle dba poster
 

 
Follow us on Twitter 
 
Oracle performance tuning software 
 
Oracle Linux poster