Focus On Oracle

Installing, Backup & Recovery, Performance Tuning,
Troubleshooting, Upgrading, Patching

Oracle Engineered System


当前位置: 首页 » 技术文章 » 性能优化

如何查看SQL的执行顺序?

在计算机科学中,二叉树是每个节点最多有两个子树的树结构。通常子树被称作左子树(left subtree)和右子树(right subtree),二叉树的每个结点至多只有二棵子树(不存在度大于2的结点),二叉树的子树有左右之分,次序不能颠倒。一棵深度为k,且有2^k-1个节点称之为满二叉树;深度为k,有n个节点的二叉树,当且仅当其每一个节点都与深度为k的满二叉树中,序号为1至n的节点对应时,称之为完全二叉树。其实Oracle中SQL的执行顺序,也是二叉树。理解SQL的执行计划、执行顺序,在SQL调优方面很有帮助。


我们可以通过下面的方法查询SQL的执行顺序
1.Enterprise Manager,Grid Control,Cloud Control

2.SQLT工具(需要安装,这个工具非常不错)

3.其他一些第三方脚本(个人觉得比较好用的是Adrian Billington写的,无需创建对象,可从https://github.com/oracle-developer/xplan 获取)


Oracle SQL的执行计划其实也是二叉树,下面将通过创建一个临时表,来获取SQL的执行顺序。

创建ohs_plan_table临时表
[oracle@ohs1 admin]$ sqlplus scott/oracle
SQL*Plus: Release 12.1.0.2.0 Production on Tue Jan 10 23:16:13 2017

Copyright (c) 1982, 2014, Oracle.  All rights reserved.
Last Successful login time: Tue Jan 10 2017 23:15:57 -05:00
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Advanced Analytics and Real Application Testing options

SQL> create global temporary table ohs_plan_table
     (
        operation          varchar2(30),
        options            varchar2(255),
        object_owner       varchar2(128),
        object_name        varchar2(128),
        object_type        varchar2(30),
        id                 numeric,
        parent_id          numeric,
        depth              numeric,
        position           numeric,
        exec_order         numeric
) on commit preserve rows
/  3    4    5    6    7    8    9   10   11   12   13   14

Table created.

SQL> desc ohs_plan_table
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 OPERATION                                          VARCHAR2(30)
 OPTIONS                                            VARCHAR2(255)
 OBJECT_OWNER                                       VARCHAR2(128)
 OBJECT_NAME                                        VARCHAR2(128)
 OBJECT_TYPE                                        VARCHAR2(30)
 ID                                                 NUMBER(38)
 PARENT_ID                                          NUMBER(38)
 DEPTH                                              NUMBER(38)
 POSITION                                           NUMBER(38)
 EXEC_ORDER                                         NUMBER(38)
SQL>
explain一个语句
explain plan for select ename,sal,dname,loc from emp a,dept b where a.deptno=b.deptno;
SQL> explain plan for select ename,sal,dname,loc from emp a,dept b where a.deptno=b.deptno;

Explained.

SQL>
将执行计划信息从plan_table抽取到建的测试临时表ohs_plan_table
insert into ohs_plan_table
  (operation,
   options,
   object_owner,
   object_name,
   object_type,
   id,
   parent_id,
   depth,
   position)
  select operation,
         options,
         object_owner,
         object_name,
         object_type,
         id,
         parent_id,
         depth,
         position
  from plan_table;


SQL> insert into ohs_plan_table
  (operation,
   options,
   object_owner,
   object_name,
   object_type,
   id,
   parent_id,
   depth,
   position)
  select operation,
         options,
         object_owner,
         object_name,
         object_type,
         id,
         parent_id,
         depth,
         position
  from plan_table;

6 rows created.

SQL>
通过ohs_plan_table临时表生成执行顺序
set serveroutput on
declare
  procedure execution_order is
    l_exec_order number;
    procedure assign_execution_order(p_id in number) is
    begin
      for j in (select id, position
                  from ohs_plan_table
                 where parent_id = p_id
                 order by position) loop
        --dbms_output.put_line(rpad('A.j Id:'||j.id,10,'*')||lpad('Pid:'||p_id,10,'*')||lpad('position '||j.position,15,'*')||lpad('order '||l_exec_order,15,'*'));      
        assign_execution_order(p_id => j.id);
        dbms_output.put_line(rpad('B.j Id:' || j.id, 10, '*') ||
                             lpad('Pid:' || p_id, 10, '*') ||
                             lpad('position ' || j.position, 15, '*') ||
                             lpad('order ' || l_exec_order, 15, '*'));
      end loop;
      l_exec_order := l_exec_order + 1;
      --dbms_output.put_line(rpad('Update Id:'||p_id,20,'*')||lpad(l_exec_order,15,'*'));
      update ohs_plan_table set exec_order = l_exec_order where id = p_id;
    end assign_execution_order;
  begin
    for i in (select id, parent_id, position
                from ohs_plan_table
               where parent_id is null) loop
      l_exec_order := 0;
      --dbms_output.put_line(rpad('C.i Id:'||i.id,10,'*')||lpad('Pid:'||nvl(i.parent_id,''),10,'*')||lpad('position '||i.position,15,'*')||lpad('order '||l_exec_order,15,'*'));      
      assign_execution_order(p_id => i.id);
      dbms_output.put_line(rpad('D.i Id:' || i.id, 10, '*') ||
                           lpad('Pid:' || nvl(i.parent_id, ''), 10, '*') ||
                           lpad('position ' || i.position, 15, '*') ||
                           lpad('order ' || l_exec_order, 15, '*'));
    end loop;
    commit;
  exception
    when others then
      dbms_output.put_line('execution_order: ' || sqlerrm);
  end;
begin
  execution_order;
end;
/ 

B.j Id:3*******Pid:2*****position 1********order 1
B.j Id:2*******Pid:1*****position 1********order 2
B.j Id:5*******Pid:4*****position 1********order 3
B.j Id:4*******Pid:1*****position 2********order 4
B.j Id:1*******Pid:0*****position 1********order 5
D.i Id:0********Pid:*****position 6********order 6

PL/SQL procedure successfully completed.

SQL>
注意:通过声明一个存储过程,存储过程中用了递归,有兴趣调试的,可以将上面代码中的--去掉,看看具体的执行情况。
查询ohs_plan_table临时表获取sql的执行顺序
col operation format a20
col options format a20
col object_name  format a30
col id  format 999
col plan for a60
set lines 156 pages 1000
select id,parent_id,exec_order,lpad(' ',2*(level-1))||operation||' '||options||' '||object_name||' ' ||decode(id,0,'Cost = '||position) plan from ohs_plan_table
start with id = 0 connect by prior id = parent_id;

  ID  PARENT_ID EXEC_ORDER PLAN
---- ---------- ---------- ------------------------------------------------------------
   0                     6 SELECT STATEMENT   Cost = 6
   1          0          5   MERGE JOIN
   2          1          2     TABLE ACCESS BY INDEX ROWID DEPT
   3          2          1       INDEX FULL SCAN PK_DEPT
   4          1          4     SORT JOIN
   5          4          3       TABLE ACCESS FULL EMP

6 rows selected.

SQL>


直接查询plan_table获取执行顺序

SQL> @/home/oracle/xplan.sql
old   3:         from   &v_xp_plan_table
new   3:         from   PLAN_TABLE
old   4:         where  plan_id = &v_xp_plan_id
new   4:         where  plan_id =         18
old  30:         from   table(dbms_xplan.display('&v_xp_plan_table','&v_xp_stmt_id','&v_xp_format')) x
new  30:         from   table(dbms_xplan.display('PLAN_TABLE','','typical')) x
old  73:                                          '  - XPlan v&v_xp_version by Adrian Billington (http://www.oracle-developer.net)'
new  73:                                          '  - XPlan v1.2 by Adrian Billington (http://www.oracle-developer.net)'

通过比对,这两种方式生成的SQL执行顺序是一致的


用二叉树表示为

从图中可以看到,这颗树遍历后,我们得到执行顺序为3,2,5,4,1,0(从左到右,从下到上的顺序)。

SQL:select ename,sal,dname,loc from emp a,dept b where a.deptno=b.deptno;


10g之后如何重建plan_table

- 'PLAN_TABLE' is old version,如果plan_table是老版本,10g之后可以通过catplan.sql脚本重建plan_table。
sqlplus / as sysdba
SQL> drop table plan_table$;
SQL> @?/rdbms/admin/catplan.sql


Reference
http://baike.baidu.com/item/%E4%BA%8C%E5%8F%89%E6%A0%91
http://www.oracle-developer.net/utilities.php

https://github.com/oracle-developer/xplan


关键词:execution_plan optim sql 

相关文章

Oracle SqlDeveloper很强悍
如何查看SQL的执行顺序?
如何做数据库优化
如何在Windows和Linux上启用Large page
如何使用SQL*Plus定制HTML报表格式
Top
Cell186-9589-1286
QQ 375349564
E-mail ohsdba@qq.com