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.

---