oracle对SQL语句进行分析和优化

re2212

安装和查看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