Previous Table of Contents Next


SQL Tuning

SQL tuning involves the use of the explain-plan table, or the use of Oracle or third-party tools that display the contents of the explain-plan table. Oracle tools include Oracle Enterprise Manager Top Session, TKPROF (examines trace files), and the EXPLAIN command combined with a SQL statement to display the results. This section will discuss how to interpret explain plan results and list coding techniques that will help avoid problems when coding SQL statements.

The explain plan is a necessity for tuning SQL statements for both the rule-based and cost-based optimizers. Listing 24.14 shows how to load the plan table and query the results. This plan table can be set up for any user by running the $ORACLE_HOME/rdbms/admin/utlxplan.sql script from SQL*Plus. Some tools display important cost-based optimizer statistics (such as Platinum Technology SQL Station, Figure 24.8) that greatly aid in the tuning process of SQL statements. Please note that the plan table results of Listing 24.14 to that of Platinum Technology’s SQL Station in Figure 24.8 are from the same SQL statement. The largest performance gains will be had by tuning the top four or five steps of the explain plan. It would be difficult at best to determine the order of processing of any of the steps in Listing 24.14. Also note the numbers in parentheses just to the right of each explain step in Figure 24.8. These figures are the cost-based cost, the number of rows affected, and the number of bytes affected by this step. Step 1 in Figure 24.8, Table Access [FULL], should usually be avoided; reviewing the row count and bytes affected, it would cause a performance degradation to resolve this full table access.


Figure 24.8.  SQL station explain plan display.

Listing 24.14. Explain plan table and results.

EXPLAIN PLAN INTO PLAN _TABLE FOR
select lname, fname, sal, cd.description, dept.description
from employees e, sal_history s, job_history j,
     job_codes cd, dept_history d, departments dept
where e.emp_seq = s.emp_seq
and e.emp_seq = j.emp_seq
and e.emp_seq = d.emp_seq
and j.job_seq = cd.job_seq
and d.dept_seq = dept.dept_seq
and s.effective_date = (select max(effective_date)
                      from sal_history s1
                      where e.emp_seq = s1.emp_seq
                      and effective_date <= sysdate)
and j.effective_date = (select max(effective_date)
                      from job_history j1
                      where e.emp_seq = j1.emp_seq
                      and effective_date <= sysdate)
and d.effective_date = (select max(effective_date)
                      from dept_history d1
                      where e.emp_seq = d1.emp_seq
                      and effective_date <= sysdate);
select COST, OPERATION, OPTIONS, OBJECT_NAME
from PLAN_TABLE;
COST        OPERATION                     OPTIONS            OBJECT_NAME
---------- ------------------------       -------------      --------------
        26 SELECT STATEMENT
           FILTER
        26 NESTED LOOPS
        25 NESTED LOOPS
        24 NESTED LOOPS
        22 NESTED LOOPS
        18 NESTED LOOPS
         3 TABLE ACCESS                   FULL               EMPLOYEES
           TABLE ACCESS                   CLUSTER            SAL_HISTORY
           TABLE ACCESS                   CLUSTER            JOB_HISTORY
           TABLE ACCESS                   CLUSTER            DEPT_HISTORY
         1 TABLE ACCESS                   BY ROWID           JOB_CODES
           INDEX                          UNIQUE SCAN        I_JOBS
           TABLE ACCESS                   BY ROWID           DEPARTMENTS
           INDEX                          UNIQUE SCAN        I_DEPTS
           SORT                           AGGREGATE
         1 TABLE ACCESS                   CLUSTER            DEPT_HISTORY
           INDEX                          UNIQUE SCAN        I_EMP_EMPNO
           SORT                           AGGREGATE
         1 TABLE ACCESS                   CLUSTER            JOB_HISTORY
           INDEX                          UNIQUE SCAN        I_EMP_EMPNO
           SORT                           AGGREGATE
         1 TABLE ACCESS                   CLUSTER            SAL_HISTORY
           INDEX                          UNIQUE SCAN        I_EMP_EMPNO
24 rows selected. 


Note:  
Oracle’s TKPROF process is the only process I am aware of from Oracle Corporation that displays the cost, row, and byte information per explain step.


Previous Table of Contents Next