设为首页收藏本站
开启辅助访问 切换到窄版

重庆思庄Oracle、Redhat认证学习论坛

 找回密码
 注册

QQ登录

只需一步，快速开始

搜索
重庆思庄Oracle、Redhat认证学习论坛»首页 › 数据库 › Oracle › 查看ORACLE SQL语句的执行计划有哪些方法？
返回列表 发新帖
查看: 5382|回复: 8
打印 上一主题 下一主题

[讨论] 查看ORACLE SQL语句的执行计划有哪些方法？

[复制链接]
郑全
跳转到指定楼层
楼主
发表于 2020-3-10 22:39:45 | 只看该作者 回帖奖励 |倒序浏览 |阅读模式
我们在执行一条SQL语句时，发现语句很慢，这个语句到底是怎么执行的呢？
有什么办法看到这个语句的执行路径呢，也即SQL语句的执行计划？

比如：
      select employee_id,last_name,d.department_id,d.department_name
          from employees e ,departments d
          where e.department_id=d.department_id
          and d.department_id=80;

如何看它的执行计划呢 ？

分享到:  QQ好友和群QQ好友和群 QQ空间QQ空间 腾讯微博腾讯微博 腾讯朋友腾讯朋友
收藏收藏 支持支持 反对反对
回复

使用道具 举报

郑全
沙发
 楼主| 发表于 2020-3-11 00:38:43 | 只看该作者
第一个获取执行计划的方法是 我们平时上课用的比较多的一个方法:
   1.先用 EXPLAIN PLAN FOR sql statement,往PLAN_TABLE里面插入计划数据
   2.再用SELECT * FROM TABLE(dbms_xplan.display()),提取

   这种方法SQL语句并不真实执行，
一般适用于

上线前的SQL预审，尤其对DML语句，由于SQL不执行，不用担心对生产数据造成影响
。这种方法查看的执行计划

有Predicate Information，无Statistics，查看到的执行计划不一定真实。

具体执行：
SQL>  explain plan
  2     for
  3      select e.employee_id,e.last_name,d.department_name
  4         from employees e, departments d
  5            where e.department_id=d.department_id
  6          and d.department_id=20;
Explained.




SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1475904561
----------------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name              | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |                   |     2 |    62 |     2   (0)| 00:00:01 |
|   1 |  NESTED LOOPS                        |                   |     2 |    62 |     2   (0)| 00:00:01 |
|   2 |   TABLE ACCESS BY INDEX ROWID        | DEPARTMENTS       |     1 |    16 |     1   (0)| 00:00:01 |
|*  3 |    INDEX UNIQUE SCAN                 | DEPT_ID_PK        |     1 |       |     0   (0)| 00:00:01 |
|   4 |   TABLE ACCESS BY INDEX ROWID BATCHED| EMPLOYEES         |     2 |    30 |     1   (0)| 00:00:01 |
|*  5 |    INDEX RANGE SCAN                  | EMP_DEPARTMENT_IX |     2 |       |     0   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   3 - access("D"."DEPARTMENT_ID"=20)
   5 - access("DEPARTMENT_ID"=20)
18 rows selected.

回复 支持 反对

使用道具 举报

郑全
板凳
 楼主| 发表于 2020-3-11 00:44:03 | 只看该作者
第二种，通过PLSQL DEVELOPER 的按F5 提取执行计划，原理和第一种一样，并不真正执行SQL


回复 支持 反对

使用道具 举报

郑全
地板
 楼主| 发表于 2020-3-11 01:03:23 | 只看该作者
本帖最后由 郑全 于 2020-3-11 01:06 编辑

第三种，使用 set autotrace on 方式，该方式，SQL语句会执行。

关于Autotrace几个常用选项的说明:

　　SET AUTOTRACE OFF ---------------- 不生成AUTOTRACE 报告，这是缺省模式

　　SET AUTOTRACE ON EXPLAIN ------ AUTOTRACE只显示优化器执行路径报告

　　SET AUTOTRACE ON STATISTICS -- 只显示执行统计信息

　　SET AUTOTRACE ON ----------------- 包含执行计划和统计信息

　　SET AUTOTRACE TRACEONLY ------ 同set autotrace on，但是不显示查询



执行步骤
如果是普通用户需要按以下方式执行：

一.创建基础表
  1.创建PLAN_TABLE表
    sql>connect / as sysdba;
    @?/rdbms/admin/utlxplan;
  2.为了使多个用户都可以共享同一个PLAN_TABLE,为它创建一个公共同义词,并授权给PUBLIC
   create public synonym plan_table for plan_table;
   grant all on plan_table to public;
二.创建PLUSTRACE角色
  1. @?/sqlplus/admin/plustrce
  2.把PLUSTRACE角色授予PUBLIC,这样所有用户都拥有PLUSTRACE角色得权限.
    grant plustrace to public;

三.使用AUTOTRACE


SQL> conn hr/hr
Connected.

SQL> set autotrace on
SQL>
SQL> select e.employee_id,e.last_name,d.department_name
  2         from employees e, departments d
  3            where e.department_id=d.department_id
  4          and d.department_id=20;
EMPLOYEE_ID LAST_NAME                 DEPARTMENT_NAME
----------- ------------------------- ------------------------------
        201 Hartstein                 Marketing
        202 Fay                       Marketing

Execution Plan
----------------------------------------------------------
Plan hash value: 1475904561
----------------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name              | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |                   |     2 |    62 |     2   (0)| 00:00:01 |
|   1 |  NESTED LOOPS                        |                   |     2 |    62 |     2   (0)| 00:00:01 |
|   2 |   TABLE ACCESS BY INDEX ROWID        | DEPARTMENTS       |     1 |    16 |     1   (0)| 00:00:01 |
|*  3 |    INDEX UNIQUE SCAN                 | DEPT_ID_PK        |     1 |       |     0   (0)| 00:00:01 |
|   4 |   TABLE ACCESS BY INDEX ROWID BATCHED| EMPLOYEES         |     2 |    30 |     1   (0)| 00:00:01 |
|*  5 |    INDEX RANGE SCAN                  | EMP_DEPARTMENT_IX |     2 |       |     0   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   3 - access("D"."DEPARTMENT_ID"=20)
   5 - access("DEPARTMENT_ID"=20)

Statistics
----------------------------------------------------------
        511  recursive calls
         14  db block gets
        733  consistent gets
         11  physical reads
       2780  redo size
        783  bytes sent via SQL*Net to client
        552  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
         42  sorts (memory)
          0  sorts (disk)
          2  rows processed

普通用户，显得有点繁琐。

贴图：
回复 支持 反对

使用道具 举报

郑全
5#
 楼主| 发表于 2020-3-11 01:18:37 | 只看该作者
本帖最后由 郑全 于 2020-3-11 01:22 编辑

第四种方法，直接取当前执行过的SQL 的执行计划,该种执行计划是实际执行计划，可以用于分析统计信息未收集之类导致执行计划不正确的问题：


SQL> conn / as sysdba
Connected.
SQL>  alter session set statistics_level=all;
Session altered.
SQL> alter session set current_schema=hr;
Session altered.
SQL> select e.employee_id,e.last_name,d.department_name
  2         from employees e, departments d
  3            where e.department_id=d.department_id
  4          and d.department_id=20;
EMPLOYEE_ID LAST_NAME                 DEPARTMENT_NAME
----------- ------------------------- ------------------------------
        201 Hartstein                 Marketing
        202 Fay                       Marketing
SQL> select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  3gyjdgp8txt2r, child number 0
-------------------------------------
select e.employee_id,e.last_name,d.department_name        from
employees e, departments d           where
e.department_id=d.department_id         and d.department_id=20
Plan hash value: 1475904561
-----------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name              | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |
-----------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |                   |      1 |        |      2 |00:00:00.01 |       6 |      4 |
|   1 |  NESTED LOOPS                        |                   |      1 |      2 |      2 |00:00:00.01 |       6 |      4 |
|   2 |   TABLE ACCESS BY INDEX ROWID        | DEPARTMENTS       |      1 |      1 |      1 |00:00:00.01 |       2 |      2 |
|*  3 |    INDEX UNIQUE SCAN                 | DEPT_ID_PK        |      1 |      1 |      1 |00:00:00.01 |       1 |      1 |
|   4 |   TABLE ACCESS BY INDEX ROWID BATCHED| EMPLOYEES         |      1 |      2 |      2 |00:00:00.01 |       4 |      2 |
|*  5 |    INDEX RANGE SCAN                  | EMP_DEPARTMENT_IX |      1 |      2 |      2 |00:00:00.01 |       2 |      1 |
-----------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   3 - access("D"."DEPARTMENT_ID"=20)
   5 - access("E"."DEPARTMENT_ID"=20)

25 rows selected.
SQL>

回复 支持 反对

使用道具 举报

郑全
6#
 楼主| 发表于 2020-3-11 01:32:17 | 只看该作者
本帖最后由 郑全 于 2020-3-11 01:35 编辑

第五种方法，看指定SQL_id 语句的执行计划

SQL> conn / as sysdba
Connected.


SQL>  select sql_id, sql_text from v$sql where sql_text like '%select e.employee_id,e.last_name,d.department_name%';
SQL_ID
-------------
SQL_TEXT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------


2uffs4gnmtyy2
select sql_id, sql_text from v$sql where sql_text like '%select e.employee_id,e.last_name,d.department_name%'
dbc75f5zcq217
select e.employee_id,e.last_name,d.department_name        from employees e, departments d           where e.department_id=d.department_id         and d.department_id=20



6 rows selected.
SQL> select * from table(dbms_xplan.display_cursor('&sql_id',null,'advanced'));
Enter value for sql_id: dbc75f5zcq217
old   1: select * from table(dbms_xplan.display_cursor('&sql_id',null,'advanced'))
new   1: select * from table(dbms_xplan.display_cursor('dbc75f5zcq217',null,'advanced'))
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  dbc75f5zcq217, child number 0
-------------------------------------
select e.employee_id,e.last_name,d.department_name        from
employees e, departments d           where
e.department_id=d.department_id         and d.department_id=20
Plan hash value: 1475904561
----------------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name              | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |                   |       |       |     2 (100)|          |
|   1 |  NESTED LOOPS                        |                   |     2 |    62 |     2   (0)| 00:00:01 |
|   2 |   TABLE ACCESS BY INDEX ROWID        | DEPARTMENTS       |     1 |    16 |     1   (0)| 00:00:01 |
|*  3 |    INDEX UNIQUE SCAN                 | DEPT_ID_PK        |     1 |       |     0   (0)|          |
|   4 |   TABLE ACCESS BY INDEX ROWID BATCHED| EMPLOYEES         |     2 |    30 |     1   (0)| 00:00:01 |
|*  5 |    INDEX RANGE SCAN                  | EMP_DEPARTMENT_IX |     2 |       |     0   (0)|          |
----------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
   1 - SEL$F5BB74E1
   2 - SEL$F5BB74E1 / D@SEL$1
   3 - SEL$F5BB74E1 / D@SEL$1
   4 - SEL$F5BB74E1 / EMPLOYEES@SEL$2
   5 - SEL$F5BB74E1 / EMPLOYEES@SEL$2
Outline Data
-------------
  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('12.1.0.2')
      DB_VERSION('12.1.0.2')
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$F5BB74E1")
      MERGE(@"SEL$2")
      OUTLINE(@"SEL$1")
      OUTLINE(@"SEL$2")
      INDEX_RS_ASC(@"SEL$F5BB74E1" "D"@"SEL$1" ("DEPARTMENTS"."DEPARTMENT_ID"))
      INDEX_RS_ASC(@"SEL$F5BB74E1" "EMPLOYEES"@"SEL$2" ("EMPLOYEES"."DEPARTMENT_ID"))
      BATCH_TABLE_ACCESS_BY_ROWID(@"SEL$F5BB74E1" "EMPLOYEES"@"SEL$2")
      LEADING(@"SEL$F5BB74E1" "D"@"SEL$1" "EMPLOYEES"@"SEL$2")
      USE_NL(@"SEL$F5BB74E1" "EMPLOYEES"@"SEL$2")
      END_OUTLINE_DATA
  */
Predicate Information (identified by operation id):
---------------------------------------------------
   3 - access("D"."DEPARTMENT_ID"=20)
   5 - access("DEPARTMENT_ID"=20)
Column Projection Information (identified by operation id):
-----------------------------------------------------------
   1 - "D"."DEPARTMENT_NAME"[VARCHAR2,30], "EMPLOYEE_ID"[NUMBER,22], "LAST_NAME"[VARCHAR2,25]
   2 - "D"."DEPARTMENT_NAME"[VARCHAR2,30]
   3 - "D".ROWID[ROWID,10]
   4 - "EMPLOYEE_ID"[NUMBER,22], "LAST_NAME"[VARCHAR2,25]
   5 - "EMPLOYEES".ROWID[ROWID,10]

64 rows selected.
SQL>

回复 支持 反对

使用道具 举报

郑全
7#
 楼主| 发表于 2020-3-11 02:02:36 | 只看该作者
第六种，使用 dbms_xplan.display_awr('&sql_id') 提取AWR中的SQL执行计划，注意，如果该条SQL没有抓到AWR中，也查不到结果。
   
select * from table(dbms_xplan.display_awr('&sql_id'));











回复 支持 反对

使用道具 举报

郑全
8#
 楼主| 发表于 2020-3-11 02:15:37 | 只看该作者
郑全 发表于 2020-3-11 02:02
第六种，使用 dbms_xplan.display_awr('&sql_id') 提取AWR中的SQL执行计划，注意，如果该条SQL没有抓到AWR ...

第七种，通过awrsqrpt 来获取
   @？/rdbms/admin/awrsqrpt.sql



回复 支持 反对

使用道具 举报

郑全
9#
 楼主| 发表于 2020-3-11 02:22:01 | 只看该作者
本帖最后由 郑全 于 2020-3-11 02:28 编辑

第八种，使用SQL MONITOR来实现

1.加HINT
   SQL>  select /*+ monitor */ e.employee_id,e.last_name,d.department_name
  2         from employees e, departments d
  3            where e.department_id=d.department_id
  4          and d.department_id=20;
EMPLOYEE_ID LAST_NAME                 DEPARTMENT_NAME
----------- ------------------------- ------------------------------
        201 Hartstein                 Marketing
        202 Fay                       Marketing

2.产生报告
  SQL> select dbms_sqltune.report_sql_monitor from dual;

REPORT_SQL_MONITOR
--------------------------------------------------------------------------------
SQL Monitoring Report
SQL Text
------------------------------
select /*+ monitor */ e.employee_id,e.last_name,d.department_name from employees
e, departments d where e.department_id=d.department_id and d.department_id=20
Global Information
------------------------------
Status              :  DONE (ALL ROWS)
Instance ID         :  1
REPORT_SQL_MONITOR
--------------------------------------------------------------------------------
Session             :  SYS (371:39225)
SQL ID              :  3b53n1fb4rf26
SQL Execution ID    :  16777216
Execution Started   :  03/11/2020 02:17:58
First Refresh Time  :  03/11/2020 02:17:58
Last Refresh Time   :  03/11/2020 02:17:59
Duration            :  1s
Module/Action       :  sqlplus@dbserver.com (TNS V1-V3)/-
Service             :  SYS$USERS
Program             :  sqlplus@dbserver.com (TNS V1-V3)
Fetch Calls         :  2
REPORT_SQL_MONITOR
--------------------------------------------------------------------------------
Global Stats
=======================================
| Elapsed |  Other   | Fetch | Buffer |
| Time(s) | Waits(s) | Calls |  Gets  |
=======================================
|    0.00 |     0.00 |     2 |      6 |
=======================================
SQL Plan Monitoring Details (Plan Hash Value=1475904561)
================================================================================
REPORT_SQL_MONITOR
--------------------------------------------------------------------------------
=========================================================================
| Id |               Operation                |       Name        |  Rows   | Co
st |   Time    | Start  | Execs |   Rows   | Activity | Activity Detail |
|    |                                        |                   | (Estim) |
   | Active(s) | Active |       | (Actual) |   (%)    |   (# samples)   |
================================================================================
=========================================================================
|  0 | SELECT STATEMENT                       |                   |         |
   |           |        |       |          |          |                 |
|  1 |   NESTED LOOPS                         |                   |       2 |
2 |           |        |       |          |          |                 |
REPORT_SQL_MONITOR
--------------------------------------------------------------------------------
|  2 |    TABLE ACCESS BY INDEX ROWID         | DEPARTMENTS       |       1 |
1 |           |        |       |          |          |                 |
|  3 |     INDEX UNIQUE SCAN                  | DEPT_ID_PK        |       1 |
   |           |        |       |          |          |                 |
|  4 |    TABLE ACCESS BY INDEX ROWID BATCHED | EMPLOYEES         |       2 |
1 |           |        |       |          |          |                 |
|  5 |     INDEX RANGE SCAN                   | EMP_DEPARTMENT_IX |       2 |
   |           |        |       |          |          |                 |
================================================================================
=========================================================================

SQL>

回复 支持 反对

使用道具 举报

返回列表 发新帖
高级模式
B Color Image Link Quote Code Smilies
您需要登录后才可以回帖 登录 | 注册

本版积分规则

QQ|手机版|小黑屋|重庆思庄Oracle、Redhat认证学习论坛 ( 渝ICP备12004239号-4 )

GMT+8, 2024-4-29 17:33 , Processed in 0.105995 second(s), 22 queries .

重庆思庄学习中心论坛-重庆思庄科技有限公司论坛

© 2001-2020

快速回复 返回顶部 返回列表