https://www.cnblogs.com/lijiaman/p/11488979.html
目录：
(一)六种执行计划
（1）explain plan for
（2）set autotrace on
（3）statistics_level=all
（4）dbms_xplan.display_cursor获取
（5）事件10046 trace跟踪
（6）awrsqrpt.sql
(二)如何选择
(一)六种执行计划
Oracle提供了6种执行计划获取方法，各种方法侧重点不同。
（1）explain plan for
例子：
SQL> show user
USER 为 "HR"
SQL> set linesize 1000
SQL> set pagesize 2000
SQL> explain plan for
2 select *
3 from employees,jobs
4 where employees.job_id=jobs.job_id
5 and employees.department_id=50;
已解释。
SQL> select * from table(dbms_xplan.display());
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 303035560
------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 45 | 4590 | 6 (17)| 00:00:01 |
| 1 | MERGE JOIN | | 45 | 4590 | 6 (17)| 00:00:01 |
| 2 | TABLE ACCESS BY INDEX ROWID| JOBS | 19 | 627 | 2 (0)| 00:00:01 |
| 3 | INDEX FULL SCAN | JOB_ID_PK | 19 | | 1 (0)| 00:00:01 |
|* 4 | SORT JOIN | | 45 | 3105 | 4 (25)| 00:00:01 |
|* 5 | TABLE ACCESS FULL | EMPLOYEES | 45 | 3105 | 3 (0)| 00:00:01 |
------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("EMPLOYEES"."JOB_ID"="JOBS"."JOB_ID")
filter("EMPLOYEES"."JOB_ID"="JOBS"."JOB_ID")
5 - filter("EMPLOYEES"."DEPARTMENT_ID"=50)
已选择19行。
/*
优点：无需真正执行，快捷方便；
缺点：1.没有输出相关统计信息，例如产生了多少逻辑读，多少次物理读，多少次递归调用的情况；
2.无法判断处理了多少行；
3.无法判断表执行了多少次
*/
（2）set autotrace on
用法：
| 命令 | 作用 |
| SET AUTOT[RACE] OFF | 停止AutoTrace |
| SET AUTOT[RACE] ON | 开启AutoTrace，显示AUTOTRACE信息和SQL执行结果 |
| SET AUTOT[RACE] TRACEONLY | 开启AutoTrace，仅显示AUTOTRACE信息 |
| SET AUTOT[RACE] ON EXPLAIN | 开启AutoTrace，仅显示AUTOTRACE的EXPLAIN信息 |
| SET AUTOT[RACE] ON STATISTICS | 开启AutoTrace，仅显示AUTOTRACE的STATISTICS信息 |
例子：
SQL> set autotrace on
SQL> select * from employees,jobs where employees.job_id=jobs.job_id and employees.department_id=50;
--输出结果（略）
-- ...
已选择45行。
执行计划
----------------------------------------------------------
Plan hash value: 303035560
------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 45 | 4590 | 6 (17)| 00:00:01 |
| 1 | MERGE JOIN | | 45 | 4590 | 6 (17)| 00:00:01 |
| 2 | TABLE ACCESS BY INDEX ROWID| JOBS | 19 | 627 | 2 (0)| 00:00:01 |
| 3 | INDEX FULL SCAN | JOB_ID_PK | 19 | | 1 (0)| 00:00:01 |
|* 4 | SORT JOIN | | 45 | 3105 | 4 (25)| 00:00:01 |
|* 5 | TABLE ACCESS FULL | EMPLOYEES | 45 | 3105 | 3 (0)| 00:00:01 |
------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("EMPLOYEES"."JOB_ID"="JOBS"."JOB_ID")
filter("EMPLOYEES"."JOB_ID"="JOBS"."JOB_ID")
5 - filter("EMPLOYEES"."DEPARTMENT_ID"=50)
统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
13 consistent gets
0 physical reads
0 redo size
5040 bytes sent via SQL*Net to client
433 bytes received via SQL*Net from client
4 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
45 rows processed
/*
优点：1.可以输出运行时的相关统计信息（产生多少逻辑读、多少次递归调用、多少次物理读等）；
2.虽然要等语句执行完才能输出执行计划，但是可以有traceonly开关来控制返回结果不打屏输出；
缺点：1.必须要等SQL语句执行完，才出结果；
2.无法看到表被访问了多少次；
*/
（3）statistics_level=all
步骤一：ALTER SESSION SET STATISTICS_LEVEL=ALL;
步骤二：执行待分析的SQL
步骤三：select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));
注释：
1.第3步还有1种方法，select * from table(dbms_xplan.display_cursor(null,null,'advanced')); --得到的信息更详细
例子：
SQL> alter session set statistics_level=all;
SQL> select * from employees,jobs where employees.job_id=jobs.job_id and employees.department_id=50;
--输出结果
--...
已选择45行。
SQL> set linesize 1000
SQL> select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------------------------------
SQL_ID d8jzhcdwmd9ut, child number 0
-------------------------------------
select * from employees,jobs where employees.job_id=jobs.job_id and
employees.department_id=50
Plan hash value: 303035560
----------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads | OMem | 1Mem | Used-Mem |
----------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 45 |00:00:00.01 | 13 | 8 | | | |
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------------------------------------------
| 1 | MERGE JOIN | | 1 | 45 | 45 |00:00:00.01 | 13 | 8 | | | |
| 2 | TABLE ACCESS BY INDEX ROWID| JOBS | 1 | 19 | 19 |00:00:00.01 | 6 | 2 | | | |
| 3 | INDEX FULL SCAN | JOB_ID_PK | 1 | 19 | 19 |00:00:00.01 | 3 | 1 | | | |
|* 4 | SORT JOIN | | 19 | 45 | 45 |00:00:00.01 | 7 | 6 | 6144 | 6144 | 6144 (0)|
|* 5 | TABLE ACCESS FULL | EMPLOYEES | 1 | 45 | 45 |00:00:00.01 | 7 | 6 | | | |
----------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("EMPLOYEES"."JOB_ID"="JOBS"."JOB_ID")
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------------------------
filter("EMPLOYEES"."JOB_ID"="JOBS"."JOB_ID")
5 - filter("EMPLOYEES"."DEPARTMENT_ID"=50)
已选择25行。
/*
关键字解读：
1.starts：SQL执行的次数；
2.E-Rows：执行计划预计返回的行数；
3.R-Rows：执行计划实际返回的行数；
4.A-Time：每一步执行的时间(HH:MM:SS.FF)，根据这一行可知SQL耗时在哪些地方；
5.Buffers：每一步实际执行的逻辑读或一致性读；
6.Reads：物理读；
7.OMem：OMem为最优执行模式所需的内存评估值， 这个数据是由优化器统计数据以及前一次执行的性能数据估算得出的；
8.1Mem：1Mem为one-pass模式所需的内存评估值，当工作区大小无法满足操作所需的大小时，需要将部分数据写入临时磁盘空间中(如果仅需要写入一次就可以完成操作，就称一次通过，One-Pass;否则为多次通过，Multi-Pass).该列数据为语句最后一次执行中，单次写磁盘所需要的内存大小，这个由优化器统计数据以及前一次执行的性能数据估算得出的
9.Used_Mem：Used-Mem则为当前操作实际执行时消耗的内存，括号里面为(发生磁盘交换的次数,1次即为One-Pass,大于1次则为Multi_Pass,如果没有使用磁盘，则显示0)
*/
/*
优点：1.可以清晰的从starts得出表被访问多少次；
2.可以从E-Rows和A-Rows得到预测的行数和真实的行数，从而可以准确判断Oracle评估是否准确；
3.虽然没有准确的输出运行时的相关统计信息，但是执行计划中的Buffers就是真实的逻辑读的数值；
缺点：1.必须要等执行完后才能输出结果；
2.无法控制结果打屏输出，不像autotrace可以设置traceonly保证不输出结果；
3.看不出递归调用，看不出物理读的数值
*/
（4）dbms_xplan.display_cursor获取
/*
步骤1：select * from table( dbms_xplan.display_cursor('&sql_id') ); --该方法是从共享池得到，如果SQL已被age out出share pool，则查找不到
注释：
1.还有1种方法，select * from table( dbms_xplan.display_awr('&sql_id') ); --该方法是从awr性能视图里面获取
2.如果有多个执行计划，可用以下方法查出：
select * from table(dbms_xplan.display_cursor('&sql_id',0));
select * from table(dbms_xplan.display_cursor('&s ql_id',1));
*/
例子：
SQL> select * from table(dbms_xplan.display_cursor('5hkd01f03y43d'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID 5hkd01f03y43d, child number 0
-------------------------------------
select * from test where table_name = 'LOG$'
Plan hash value: 2408911181
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 2 (100)|
| 1 | TABLE ACCESS BY INDEX ROWID| TEST | 1 | 241 | 2 (0)|
|* 2 | INDEX RANGE SCAN | IDX_TEST_1 | 1 | | 1 (0)|
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("TABLE_NAME"='LOG$')
19 rows selected
注释：如何查看1个sql语句的sql_id，可直接查看v$sql
/*
优点：1.知道sql_id即可得到执行计划，与explain plan for一样无需执行；
2.可得到真实的执行计划
缺点：1.没有输出运行的统计相关信息；
2.无法判断处理了多少行；
3.无法判断表被访问了多少次；
*/
（5）事件10046 trace跟踪
/*
步骤1：alter session set events '10046 trace name context forever,level 12'; --开启追踪
步骤2：执行sql语句；
步骤3：alter session set events '10046 trace name context off'; --关闭追踪
步骤4：select tracefile from v$process where addr=(select paddr from v$session where sid=(select sid from v$mystat where rownum<=1)); --找到跟踪后产生的文件
步骤5：tkprof trc文件 目标文件 sys=no sort=prsela,exeela,fchela --格式化命令
*/
例子：
见：使用10046追踪执行计划demo---紧跟https地址
-- https://www.cnblogs.com/lijiaman/p/11489121.html
使用10046追踪执行计划demo
（一）开启10046追踪
SQL> alter session set events '10046 trace name context forever,level 12';
（二）执行sql语句
SELECT J.JOB_ID,
J.JOB_TITLE,
J.MIN_SALARY,
J.MAX_SALARY,
E.EMPLOYEE_ID,
E.FIRST_NAME || E.LAST_NAME AS NAME,
E.EMAIL,
E.PHONE_NUMBER
FROM JOBS J, EMPLOYEES E
WHERE J.JOB_ID = E.JOB_ID;
（三）关闭追踪
SQL> alter session set events '10046 trace name context off';
（四）在查看alert目录里面找到新生成的文件，执行分析
(4.1)找到文件
[oracle@rac1 trace]$ pwd /u01/app/oracle/diag/rdbms/rac/rac1/trace [oracle@rac1 trace]$ ls -lrt ... ... -rw-r----- 1 oracle asmadmin 196 Jan 14 15:41 rac1_ora_27998.trm -rw-r----- 1 oracle asmadmin 9530 Jan 14 15:41 rac1_ora_27998.trc
[oracle@rac1 trace]$ tkprof rac1_ora_27998.trc lijiaman_10046.txt sys=no sort=prsela,exeela,fchela
（4.3）查看解析后的文件
[oracle@rac1 trace]$ ls -lrt
...
-rw-r----- 1 oracle asmadmin 112680 Jan 14 15:39 rac1_ora_12468.trc
-rw-r----- 1 oracle asmadmin 196 Jan 14 15:41 rac1_ora_27998.trm
-rw-r----- 1 oracle asmadmin 9530 Jan 14 15:41 rac1_ora_27998.trc
-rw-r--r-- 1 oracle oinstall 8587 Jan 14 15:43 lijiaman_10046.txt
/*
优点：1.可以看出sql语句对应的等待事件；
2.如果函数中有sql调用，函数中有包含sql，将会被列出，无处遁形；
3.可以方便的看处理的行数，产生的逻辑物理读；
4.可以方便的看解析时间和执行时间；
5.可以跟踪整个程序包
缺点：1.步骤繁琐；
2.无法判断表被访问了多少次；
3.执行计划中的谓词部分不能清晰的展现出来
*/
（6）awrsqrpt.sql
/*
步骤1：@?/rdbms/admin/awrsqrpt.sql
步骤2：选择你要的断点（begin snap和end snap）
步骤3：输入要查看的sql_id
*/
例子：
https://www.cnblogs.com/lijiaman/p/11489225.html
SQL> @?/rdbms/admin/awrsqrpt.sql
（二）如何选择
选择时一般遵循以下规则：
1.如果sql执行很长时间才出结果或返回不了结果，用方法1：explain plan for
2.跟踪某条sql最简单的方法是方法1：explain plan for，其次是方法2：set autotrace on
3.如果相关查询某个sql多个执行计划的情况，只能用方法4：dbms_xplan.display_cursor或方法6：awrsqrpt.sql
4.如果sql中含有函数，函数中有含有sql，即存在多层调用，想准确分析只能用方法5：10046追踪
5.想法看到真实的执行计划，不能用方法1：explain plan for和方法2：set autotrace on
6.想要获取表被访问的次数，只能用方法3：statistics_level = all
SQL执行计划解读
recursive calls ---递归调用，硬解析次数
db block gets ---多少块发生变化，一般dml才会产生块变化;或者有延迟块清除的时候或者SQL语句中调用了返回CLOB的函数
consistent gets ---逻辑读，单位是块；通常逻辑读越小，性能越好
如果SQL的逻辑读远远大于SQL语句中所有表的段大小之和（假设所有表走全表扫描，关联方式hash join 那么这个SQL 就存在较大的优化空间）
physical reads 表示从磁盘读取了多少数据块，物理读;如果表已经缓存到buffer cache 中，物理读就是0
redo size 表示产生了多少字节的重做日志，一般情况只有DML才能产生redo；或者有延迟块清除，查询语句也会产生redo
bytes sent via SQL*Net to client 从数据库服务器发送多少字节到客户端
bytes received via SQL*Net from client 客户端发送多少字节到服务器
SQL*Net roundtripd to/from client 客户端于数据库端交互次数，可以通过设置 arraysize 减少交互次数
sorts (memory) 内存排序
sorts (disk) 磁盘排序
rows processed 表示SQL 一共返回多少行数据；
可以根据SQL返回的行数判断整个SQL应该走HASH 还是走嵌套循环
如果，rows processed 很大，一般走HASH ；如果rows processed很小，走嵌套循环