對SQL語句進行分析和優化

安裝和查看ORACLE執行計劃
ORACLE在執行SQL語句時使用的步驟的集合叫做執行計劃

前起條件：
    在目錄：$ORACLE_HOME/RDBMS/ADMIN目錄下的執行utlxplan.sql

查看執行計劃：
    EXPLAN PLAN FOR <SQL語句>
    
    CREDIT @ORCL>explain plan for select * from creditcard;

Explained.

看SQL執行計劃的信息
CREDIT @ORCL>select a.operation,options,object_name,object_type,id,parent_id from plan_table a order by id;

更直觀：
CREDIT @ORCL>select lpad(' ',2*(level-1)) || operation || ' ' || options || ' ' || object_name || ' ' || decode(id,0,'cost='||position) "Query Plan" from plan_table connect by prior id=parent_id;

Query Plan
------------------------------------------------------------------------------------------------------------------------
TABLE ACCESSFULLCREDITCARD
TABLE ACCESSFULLCREDITCARD
SELECT STATEMENTcost=3
TABLE ACCESSFULLCREDITCARD
TABLE ACCESSFULLCREDITCARD
SELECT STATEMENTcost=3
TABLE ACCESSFULLCREDITCARD
TABLE ACCESSFULLCREDITCARD
這個也可以查詢：
CREDIT @ORCL>select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
Plan hash value: 2658862924

--------------------------------------------------------------------------------
| Id  | Operation      | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |           |     9 |  1332 |     3     (0)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| CREDITCARD |     9 |  1332 |     3     (0)| 00:00:01 |
--------------------------------------------------------------------------------

Note
-----
   - dynamic sampling used for this statement (level=2)

打開自動跟蹤功能：
    set autotrace on

通過ROWID訪問表的執行計劃：
    SYS AS SYSDBA@ORCL>explain plan for
  2  select * from hr.departments where rowid='AAAR5QAAFAAAACvAAa';

Explained.

Elapsed: 00:00:00.05
SYS AS SYSDBA@ORCL>select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 313428322

------------------------------------------------------------------------------------------
| Id  | Operation           | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |         |     1 |    21 |     1   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY USER ROWID| DEPARTMENTS |     1 |    21 |     1   (0)| 00:00:01 |
------------------------------------------------------------------------------------------

8 rows selected.


連接查詢的執行計劃：
    

優化案例分析:
    提高GROUP BY 語句的效率：
    select cardno,sum(amount) from consume group by cardno having cardno='9555xxxx3' or cardno='9555xxxx8';
    -------------------------------------------------------------------------------
| Id  | Operation        | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |          |   114K|  4475K|   175    (3)| 00:00:03 |
|*  1 |  FILTER         |          |       |       |        |          |
|   2 |   HASH GROUP BY     |          |   114K|  4475K|   175    (3)| 00:00:03 |
|   3 |    TABLE ACCESS FULL| CONSUME |   114K|  4475K|   171    (1)| 00:00:03 |
-------------------------------------------------------------------------------
    1. 進行全表掃描TABLE ACCESS FULL
    2.執行分組統計HASH GROUP BY
    3.執行過濾操作FILTER
    分析：過濾操作在分組統計之后，所有分組統計處理的數據量比較大
    優化后語句：
    select cardno,sum(amount) from consume where "CARDNO"='9555xxxx3' OR "CARDNO"='9555xxxx8' group by cardno;
    


    使用EXISTS代替IN關鍵字
    






++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
方法1：運行以下腳本，生成plan_table表

SQL> @/u01/app/oracle/product/10.2/db_1/rdbms/admin/utlxplan.sql

Table created.


SQL> explain plan for
  2  select deptno from scott.dept group by deptno;

Explained.

SQL> select id,operation,options,object_name,position from plan_table;

  ID OPERATION            OPTIONS         OBJECT_NAME                 POSITION
---- -------------------- --------------- ------------------------- ----------
   0 SELECT STATEMENT                                                        1
   1 SORT                 GROUP BY NOSORT                                    1
   2 INDEX                FULL SCAN       PK_DEPT                            1

方法2：oracle提供v$sql_plan來

SQL> select  id,options,operation,object_name,cost
  2   from v$sql_plan
  3   where object_owner='SCOTT';

no rows selected－－沒有數據的原因是：剛剛的explain plan for命令只產生執行計劃，而不是真正執行語句

SQL> select deptno from scott.dept group by deptno;

    DEPTNO
----------
        10
        20
        30
        40



SQL> select id,operation,options,object_name,position from plan_table;

  ID OPERATION            OPTIONS              OBJECT_NAME            POSITION
---- -------------------- -------------------- -------------------- ----------
   0 SELECT STATEMENT                                                        1
   1 SORT                 GROUP BY NOSORT                                    1
   2 INDEX                FULL SCAN            PK_DEPT                       1