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报表格式

联系我们

韩朝阳



    Phone:186-9589-1286

    E-mail:ohsdba@qq.com

    微  信: ohsdba


电话(186-9589-1286)

QQ(375349564)

微信(ohsdba)


常用链接

Oracle
eDelivery
My Oracle Support

Exadata 12.1 Document

Exadata 12.2 Document

Oracle 11gR2 Document

Oracle 12cR1 Document

Oracle 12cR2 Document

GoldenGate 12c

Oracle Fusion Middleware

Mysql Document

PostgreSQL

Python

github

sourceforge



Top
Cell186-9589-1286
QQ 375349564
E-mail ohsdba@qq.com