Oracle SQL Tuning Overview

Oracle执行SQL语句的步骤如下：

1. 用户传送要执行的SQL语句给SQL引擎

2. SQL引擎要求查询优化器提供执行计划

3. 查询优化取得系统统计信息、SQL语句引用对象的对象统计信息、SQL概要和构成执行环境的初始化参数

4. 查询优化器分析SQL语句并产生执行计划

5. 将执行计划传递给SQL引擎

6. SQL引擎执行SQL语句

参考:Oracle Database Performance Tuning Guide, 10g Release 2 (10.2)

下载地址：http://www.oracle.com/pls/db102/homepage

（一）、Developing Efficient SQL Statements

1、Use of EXISTS versus IN for Subqueries(11-9)

      In certain circumstances, it is better to use IN rather than EXISTS. In general, if the
selective predicate is in the subquery, then use IN. If the selective predicate is in the
parent query, then use EXISTS.（在特定的情况下，使用IN可能比使用EXISTS会更好，一般，如果选择的过滤条件在子查询中，那么使用IN，如果，选择的过滤条件在父查询的SQL语句中，那么使用EXISTS）

例如：EXISTS

SELECT /* EXISTS EXAMPLE */
 E.EMPLOYEE_ID,
 E.FIRST_NAME,
 E.LAST_NAME,
 E.SALARY
  FROM EMPLOYEES E
 WHERE E.DEPARTMENT_ID = 80 /* NOTE 5 */
   AND E.JOB_ID = 'SA_REP' /* NOTE 6 */
   AND EXISTS (SELECT 1 /* NOTE 1 */
          FROM ORDERS O
         WHERE E.EMPLOYEE_ID = O.SALES_REP_ID); /* NOTE 2 */

【

    Note:
    ■ Note 1: This shows the line containing EXISTS.
    ■ Note 2: This shows the line that makes the subquery a correlated subquery.
    ■ Note 5 & 6:These are the selective predicates in the parent SQL.(代表父语句中的SQL过滤条件，在这种情况下，过滤条件出现在父SQL，所以，推荐使用EXISTS)

 】

IN

SELECT /* IN EXAMPLE */
 E.EMPLOYEE_ID,
 E.FIRST_NAME,
 E.LAST_NAME,
 E.SALARY
  FROM EMPLOYEES E
 WHERE E.EMPLOYEE_ID IN (SELECT O.SALES_REP_ID /* NOTE 4 */
                           FROM ORDERS O
                          WHERE O.CUSTOMER_ID = 144); /* NOTE 3 */

【

    Note:
    ■ Note 3: This shows the line where the correlated subqueries  include the highly selective predicate customer_id = number
    ■ Note 4: This indicates that an IN is being used. The subquery is no longer correlated, because the IN clause replaces the join in the subquery.（过滤条件在子查询中，所以选择使用IN）

】

SQL Tuning Information Views

      This section summarizes the views that you can display to review information that has
been gathered for tuning the SQL statements. You need DBA privileges to access these
views.

     ■ Advisor information views, such as DBA_ADVISOR_TASKS, DBA_ADVISOR_
FINDINGS, DBA_ADVISOR_RECOMMENDATIONS, and DBA_ADVISOR_RATIONALE
views.
     ■ SQL tuning information views, such as DBA_SQLTUNE_STATISTICS, DBA_
SQLTUNE_BINDS, and DBA_SQLTUNE_PLANS views.
     ■ SQL Tuning Set views, such as DBA_SQLSET, DBA_SQLSET_BINDS, DBA_
SQLSET_STATEMENTS, and DBA_SQLSET_REFERENCES views.
     ■ Information on captured execution plans for statements in SQL Tuning Sets are
displayed in the DBA_SQLSET_PLANS and USER_SQLSET_PLANS views.
     ■ SQL Profile information is displayed in the DBA_SQL_PROFILES view.
     ■ Advisor execution progress information is displayed in the V$ADVISOR_
PROGRESS view.
     ■ Dynamic views containing information relevant to the SQL tuning, such as V$SQL,
V$SQLAREA, V$SQLSTATS, and V$SQL_BINDS views.
      See Also: Oracle Database Reference for information on static data
dictionary and dynamic views

 hint

 hints, which are optimization instructions placed in a comment in the statement.