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 || ...
Get Oracle Database Administration: The Essential Refe now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.