查看ORACLE SQL语句的执行计划有哪些方法？ - 重庆思庄Oracle、Redhat认证学习论坛

具体执行：

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

第二种，通过PLSQL DEVELOPER 的按F5 提取执行计划，原理和第一种一样，并不真正执行SQL

163215e67c3cfca304.png (69.1 KB, 下载次数: 179)

下载附件

2020-3-11 00:43 上传

作者: 郑全 时间: 2020-3-11 01:03
本帖最后由郑全于 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

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

贴图：

871605e67c92d99f26.png (40.95 KB, 下载次数: 190)

下载附件

2020-3-11 01:06 上传

作者: 郑全 时间: 2020-3-11 01:18
本帖最后由郑全于 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>

821355e67cbe9f147d.png (41.79 KB, 下载次数: 191)

下载附件

2020-3-11 01:18 上传

SQL> show user
USER is "SYS"
SQL> select SYS_CONTEXT('USERENV','CURRENT_SCHEMA') CURRENT_SCHEMA from dual;
CURRENT_SCHEMA
----------------------------------------------------------------------
HR

作者: 郑全 时间: 2020-3-11 01:32
本帖最后由郑全于 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>

321435e67cf1c765cc.png (33.79 KB, 下载次数: 190)

下载附件

2020-3-11 01:32 上传

这种方法是实际执行语句的执行计划，但该种方法的前提，是该SQL语句的游标没有刷出共享池，否则查询不到结果。

作者: 郑全 时间: 2020-3-11 02:02
第六种，使用 dbms_xplan.display_awr('&sql_id') 提取AWR中的SQL执行计划，注意，如果该条SQL没有抓到AWR中，也查不到结果。

select * from table(dbms_xplan.display_awr('&sql_id'));

作者: 郑全 时间: 2020-3-11 02:15

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

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

作者: 郑全 时间: 2020-3-11 02:22
本帖最后由郑全于 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>