Loading Consent Dialog

SQL Tuning   «Prev 

Oracle SQL Tuning Module Summary

The following concepts were covered in this module:
  1. Oracle provides a standard ANSI SQL for database access, and has provided a wealth of built-in functions to improve SQL productivity.
  2. SQL tuning is one of the single most important tuning techniques that can be used to tune an Oracle database.
  3. Oracle provides a set of tools to assist in tuning SQL, including EXPLAIN PLAN, TKPROG and SQL Analyzer.
  4. Oracle has two optimizer modes. Rule-based optimization is the oldest method and relies on heuristics to determine access paths. The cost-based optimizer is a later development and relies on table and index statistics to formulate access plans to data.
  5. The EXPLAIN PLAN utility is the primary method for viewing the underlying access path to Oracle tables and indexes.
  6. Oracle provides hints that can be added to standard SQL to change the access path to data.

left table: In an outer join, the table specified on the left side of the OUTER JOIN keywords (in ANSI SQL syntax).
right table: In an outer join, the table specified on the right side of the OUTER JOIN keywords (in ANSI SQL syntax).


Tuning a single SQL query is an enormously important topic. Before going into production virtually every system would expose some statements that require tuning. In this article, we'll explore several Oracle 9.2 improvements that make life of performance analyst easier.

Access and Filter Predicates

Syntactically, SQL query consists of three fundamental parts:
  1. a list of columns,
  2. a list of tables, and
  3. a "where" clause.
The "where" clause is a logical formula that can be further decomposed into predicates connected by Boolean connectives.
For example, the "where" clause of: 
select empno, sal from emp e, dept d
where e.deptno = d.deptno and dname = 'ACCOUNTING'

Is a conjunction of dname = 'ACCOUNTING' single table predicate and e.deptno = d.deptno join predicate.
In my opinion, predicate handling is the heart of SQL optimization: predicates could be transitively added, rewritten using Boolean Algebra laws, and moved around at SQL Execution Plan. In our simplistic example, the single table predicate is
applied either to index or table scan plan nodes, while join predicate could also be applied to the join node. Unfortunately, despite their significance, Oracle Execution Plan facility didn't show predicates until version 9.2.