Oracle Database Administration: The Essential Refe

Query processing

Chapter 8, describes how Oracle creates a plan for a particular SQL statement. Oracle currently uses one of two methods for determining how to execute a SQL statement:

Rule-based method

Applies a standard, inflexible (but often efficient) set of rules to the statement

Cost-based method

Considers the available statistical information about the objects referenced by a SQL statement (along with available indexes) and creates a plan based on those statistics

The keys to tuning a SQL statement are understanding how the Oracle query optimizers work and knowing how to change Oracle’s behavior so it will process the statement more efficiently.

Of course, before you can tune a SQL statement, you must know what it is doing and how. There are many tools on the market today that will help with this task, and one of the most useful (if not the flashiest) is the EXPLAIN PLAN command available in SQL*Plus. By creating a plan table (usually known as PLAN_TABLE) and examining the result of an EXPLAIN PLAN statement, you’ll easily see how Oracle executes a particular statement. For example, the SQL statement:

SELECT ename,loc,sal,hiredate
FROM   scott.emp, scott.dept
WHERE  emp.deptno=dept.deptno;

can be explained with the following command:

EXPLAIN PLAN SET STATEMENT_ID='DEMO' FOR
SELECT ename,loc,sal,hiredate
FROM   scott.emp, scott.dept
WHERE  emp.deptno=dept.deptno;

The results stored in PLAN_TABLE can be selected using a simple query:

SELECT LPAD(' ',2*level) || operation || '' || options || ...