SQL Tuning   «Prev  Next»

Lesson 1

Oracle SQL Statements and SQL Tuning

This module is designed to give you an overview of the internal functions and tuning of Oracle SQL statements. Each of the subjects introduced in this module will be examined in much greater detail during the course.
Topics in this module include:
  1. Oracle extensions to ANSI standard SQL
  2. Basic SQL tuning tools
  3. Using the EXPLAIN PLAN utility
  4. The SQL optimizer modes
  5. The rule-based SQL optimizer
  6. The cost-based SQL optimizer
  7. Tuning SQL with hints

SQL functions are built into the Oracle Database and are available for use in various SQL statements. Do not confuse SQL functions with user-defined functions written in PL/SQL. If you call a SQL function with an argument of a datatype other than the datatype expected by the SQL function, then Oracle attempts to convert the argument to the expected datatype before performing the SQL function. If you call a SQL function with a null argument, then the SQL function automatically returns null. The only SQL functions that do not necessarily follow this behavior are CONCAT, NVL, REPLACE, and REGEXP_REPLACE.

SQL tuning is the iterative process of improving SQL statement performance to meet specific, measurable, and achievable goals. SQL tuning implies fixing problems in deployed applications. In contrast, application design sets the security and performance goals before deploying an application.

Purpose of SQL Tuning

A SQL statement becomes a problem when it fails to perform according to a predetermined and measurable standard.After you have identified the problem, a typical tuning session has one of the following goals:
  1. Reduce user response time, which means decreasing the time between when a user issues a statement and receives a response
  2. Improve throughput, which means using the least amount of resources necessary to process all rows accessed by a statement
For a response time problem, consider an online book seller application that hangs for three minutes after a customer updates the shopping cart. Contrast with a three minute parallel query in a data warehouse that consumes all of the database host CPU, preventing other queries from running. In each case, the user response time is three minutes, but the cause of the problem is different, and so is the tuning goal.