【sql调优之执行计划】获取执行计划

从sql开发进阶到开发高效的sql需要对oracle对sql的解析执行有一些了解。先看看如何获得执行计划。

 

要使用执行计划需要先执行脚本：$ORACLE_HOME/rdbms/admin/utlxplan.sql

这个脚本会创建一个plan_table表，简单看看这个表的结构：

SQL> desc plan_table

Name            Type          Nullable Default Comments

--------------- ------------- -------- ------- --------

STATEMENT_ID    VARCHAR2(30)  Y               --语句id

TIMESTAMP       DATE          Y                 --时间戳

REMARKS         VARCHAR2(80)  Y                  

OPERATION       VARCHAR2(30)  Y               --操作名称         

OPTIONS         VARCHAR2(30)  Y                --选项

OBJECT_NODE     VARCHAR2(128) Y               --对象节点   

OBJECT_OWNER    VARCHAR2(30)  Y               --对象所有者

OBJECT_NAME     VARCHAR2(30)  Y               --对象名称

OBJECT_INSTANCE INTEGER       Y               --对象实例

OBJECT_TYPE     VARCHAR2(30)  Y               --对象类型

OPTIMIZER       VARCHAR2(255) Y               --优化器模式

SEARCH_COLUMNS  NUMBER        Y                --查询列

ID              INTEGER       Y                  --当前id

PARENT_ID       INTEGER       Y                 --父id

POSITION        INTEGER       Y                  --位置id

COST            INTEGER       Y                   --开销

CARDINALITY     INTEGER       Y                 -- 基数

BYTES           INTEGER       Y                  --字节数

OTHER_TAG       VARCHAR2(255) Y                 

PARTITION_START VARCHAR2(255) Y               --分区开始          

PARTITION_STOP  VARCHAR2(255) Y               --分区结束

PARTITION_ID    INTEGER       Y                 --分区id

OTHER           LONG          Y                        

DISTRIBUTION    VARCHAR2(30)  Y                -- 分发       

CPU_COST        INTEGER       Y                  --cpu开销

IO_COST         INTEGER       Y                  --io开销

TEMP_SPACE      INTEGER       Y

 

看看几个重要字段的具体值：

 

SQL> select a.operation,

  2         a.object_name,

  3         a.cost,

  4         a.cardinality cd,

  5         a.bytes bt,

  6         a.io_cost io,

  7         a.cpu_cost cpu

  8    from plan_table a

  9   where a.statement_id is null;

 

OPERATION                      OBJECT_NAME                          COST

------------------------------ ------------------------------ ----------

        CD         BT         IO        CPU

---------- ---------- ---------- ----------

SELECT STATEMENT                                                       3

         1         20          3

 

NESTED LOOPS                                                           3

         1         20          3

 

MERGE JOIN                                                             2

         3         42          2

 

 

OPERATION                      OBJECT_NAME                          COST

------------------------------ ------------------------------ ----------

        CD         BT         IO        CPU

---------- ---------- ---------- ----------

TABLE ACCESS                   T_GROUP_POLICY_PRODUCT                  1

         2         24          1

 

INDEX                          UNI_GROUP_POLICY_PRODUCT                3

         3                     3

 

BUFFER                                                                 1

         2          4          1

 

 

OPERATION                      OBJECT_NAME                          COST

------------------------------ ------------------------------ ----------

        CD         BT         IO        CPU

---------- ---------- ---------- ----------

INDEX                          PK_T_PERIOD_TYPE                        1

         2          4          1

 

TABLE ACCESS                   T_PRODUCT_LIFE                          1

         1          6          1

 

INDEX                          PK_T_PRODUCT_LIFE

         1

 

 

9 rows selected.

 

 

运行脚本$ORACLE_HOME/sqlplus/admin/plustrce.sql 可以创建plustrace角色便于管理使用执行计划的用户的权限，可以使用grant plustrace role to xxx来赋予用户相关的使用权限。

 

 

可以使用explain plan for 来获得执行计划，然后查询plan_table（如前面的查询语句）来查看执行计划，或者使用dbms_xplan包的display方法，例如：

Select * from table(dbms_xplan.display);

 

也可以在sqlplus下使用set autotrace on/traceonly等语句，除了或者执行计划以外，还可以查看到执行统计信息，同时也可以在trace文件中查找相关的执行计划（结合使用tkprof，后续详述）。

 

可以通过查询系统视图来获得执行计划，例如：

SQL> select a.ADDRESS,a.HASH_VALUE,a.OPERATION,a.COST from v$sql_plan a where rownum = 1;

 

ADDRESS          HASH_VALUE OPERATION              COST

---------------- ---------- -----------------------------

07000004BFF2D0D0 3606577152 UPDATE STATEMENT       1

 

可以使用alter session/system set sql_trace=true/false;来打开关闭sql追踪，而在trace文件中获得执行计划。例如：

 

SQL> alter session set sql_trace=true;

 

Session altered.

 

SQL> alter session set sql_trace=false;

 

Session altered.

 

SQL>

 

也可以从statspack等工具的使用来获得。（后续详述）

 

或者使用一些工具，例如toad，plsql dev来获得，其原理还是查询相关的表或者系统视图。

 

 

 

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/16179598/viewspace-671082/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/16179598/viewspace-671082/