第2章：Oracle里的执行计划

1 什么是执行计划

为执行sql，Oracle内部必须按顺序执行很多步骤，最后将其执行结果作为目标sql的执行结果返回给用户，Oacle用来执行目标sql语句的这些步骤的组合称为执行计划

1.1 执行计划的三部分

--准备工作
create table t1(col1 number, col2 varchar2(1));
create table t2(col2 varchar2(1), col3 varchar2(2));
select /*+real_exp_example1*/
 t1.col1, t2.col2, t2.col3
  from t1, t2
 where t1.col2 = t2.col2;

1.1.1 SQL正文，SQL ID，执行计划对应的PLAN HASH VALUE

1	SQL_ID  bdfsuk5az1fvs, child number 0
2	-------------------------------------
3	select /*+real_exp_example1*/  t1.col1, t2.col2, t2.col3   from t1, t2  
4	where t1.col2 = t2.col2
5	 
6	Plan hash value: 1838229974

1.1.2 执行计划主体

8	---------------------------------------------------------------------------
9	| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
10	---------------------------------------------------------------------------
11	|   0 | SELECT STATEMENT   |      |       |       |     4 (100)|          |
12	|*  1 |  HASH JOIN         |      |     1 |    20 |     4   (0)| 00:00:01 |
13	|   2 |   TABLE ACCESS FULL| T1   |     1 |    15 |     2   (0)| 00:00:01 |
14	|   3 |   TABLE ACCESS FULL| T2   |     1 |     5 |     2   (0)| 00:00:01 |
15	---------------------------------------------------------------------------
16	 
17	Query Block Name / Object Alias (identified by operation id):
18	-------------------------------------------------------------
19	 
20	   1 - SEL$1
21	   2 - SEL$1 / T1@SEL$1
22	   3 - SEL$1 / T2@SEL$1
23	 
24	Outline Data
25	-------------
26	 
27	  /*+
28	      BEGIN_OUTLINE_DATA
29	      IGNORE_OPTIM_EMBEDDED_HINTS
30	      OPTIMIZER_FEATURES_ENABLE('11.2.0.4')
31	      DB_VERSION('11.2.0.4')
32	      ALL_ROWS
33	      OUTLINE_LEAF(@"SEL$1")
34	      FULL(@"SEL$1" "T1"@"SEL$1")
35	      FULL(@"SEL$1" "T2"@"SEL$1")
36	      LEADING(@"SEL$1" "T1"@"SEL$1" "T2"@"SEL$1")
37	      USE_HASH(@"SEL$1" "T2"@"SEL$1")
38	      END_OUTLINE_DATA
39	  */
40	 
41	Predicate Information (identified by operation id):
42	---------------------------------------------------
43	 
44	   1 - access("T1"."COL2"="T2"."COL2")
45	 
46	Column Projection Information (identified by operation id):
47	-----------------------------------------------------------
48	 
49	   1 - (#keys=1) "T2"."COL2"[VARCHAR2,1], "T1"."COL1"[NUMBER,22], 
50	       "T2"."COL3"[VARCHAR2,2]
51	   2 - "T1"."COL1"[NUMBER,22], "T1"."COL2"[VARCHAR2,1]
52	   3 - "T2"."COL2"[VARCHAR2,1], "T2"."COL3"[VARCHAR2,2]

1. Outline Data：表示当前语句内部执行计划，可以将这部分内容摘出来加到目标SQL中来固定其执行计划
2. “* 1”：表示该执行步骤有对应的驱动或者过滤查询条件，这个星号对应的具体内容可以从Predicate Information (identified by operation id)中查到

1.1.3 执行计划额外补充信息：Note部分

1. 动态采样

Note
-----
   - dynamic sampling used for this statement

2. 使用Cardinality Feedback

Note
-----
   - cardinality feedback used for this statement

3. 使用SQL Profile

Note
-----
   - SQL profile SYS_SQLPROF_01339cce6e980001 used for this statement

2 如何查看执行计划

2.1 explain plan命令

PL/SQL Developer中的F5就是执行下面两句得到的结果

--将执行计划写入PLAN_TABLE$，且这个表是session级别临时表，所以各个session只能看见自己执行的SQL所产生的执行计划，且各session写入PLAN_TABLE$的过程互不干扰
explain plan for +目标SQL
--将PLAN_TABLE$中的数据以格式化的方式显示出来
select * from table(dbms_xplan.display);

2.2 DBMS_XPLAN包

以下方法可以在语句上使用/+ gather_plan_statistics/hint，并将advanced改为allstats last，这样可以得到实际行数等详细信息。注意该hint不要加在insert中，会无法获取具体信息

2.2.1 方法1

需要与explain plan命令配合使用

select * from table(dbms_xplan.display);

2.2.2 方法2

用于查看刚执行过的SQL的执行计划，其中第三个参数也可以额是all，但all显示的执行计划会比advanced少Outline Data部分(hint部分)

select * from table(dbms_xplan.display_cursor(null,null,'advanced'));

2.2.3 方法3

用于查看指定的sql_id/hash_value对应的SQL的执行计划，只要SQL执行计划还在Shared Pool中(没有被aged out出Shared Poll)就可以查到

--如果已知具体语句内容，下方语句所使用参数，可以通过v$sql表查到
select t.SQL_TEXT,t.SQL_ID,t.HASH_VALUE,t.CHILD_NUMBER from v$sql t where t.SQL_TEXT like '%select empno,ename%';
select * from table(dbms_xplan.display_cursor('sql_id/hash_value','child_cursor_number','advanced'));

2.2.4 方法4

可以查找Shared Pool中不存在，但被采集到AWR Repository中的执行计划。这种方法在执行计划中看不到谓词条件

select * from table(dbms_xplan.display_awr('sql_id'));
--手工采集AWR报告
exec dbms_workload_repository.create_snapshot();
--清空Shared Pool(缓存)
alter system flush shared_pool;
--判断Shared Pool是否还有该执行计划
select * from v$sqlarea/v$sql;

2.3 AUTOTRACE开关

2.3.1 使用流程

1. SQLPLUS中设置AUTOTRACE

--1.on：执行结果+执行计划+资源消耗
--2.off：执行结果，为默认值
--3.traceonly：执行结果条数+执行计划+资源消耗
--4.traceonly explain：执行计划
--5.traceonly statistics：执行结果条数，资源消耗
set autotrace {off|on|traceonly} [explain] [statistics];

2. 在当前session中执行SQL语句

2.3.2 资源消耗视图

--资源消耗
Statistics
----------------------------------------------------------
	235  recursive calls
	  0  db block gets
	616  consistent gets
	 32  physical reads
	  0  redo size
       2261  bytes sent via SQL*Net to client
	512  bytes received via SQL*Net from client
	  1  SQL*Net roundtrips to/from client
	 95  sorts (memory)
	  0  sorts (disk)
	  0  rows processed

2.4 10046事件与tkprof命令

这种方式得到的执行计划中明确显示目标SQL实际执行计划中每一个执行步骤所消耗的逻辑读、物理读、花费时间，与GATHER_PLAN_STATISTICS Hint配合DBMX_XPLAN包一起使用有类似的效果

2.4.1 使用流程

1. 在当前Session中激活10046事件

--1.如果想使用oradebug命令激活10046，需要先执行下面代码。但oradebug命令只能在sys用户下执行
oradebug setmypid;
--2.激活的两种方法：level后的值可以更改表示不同的详细程度
--a.方法一：Oracle会将执行计划和明细资源写到参数USER_DUMP_DEST所代表的目录下生成名为"实例名_ora_当前Session的spid.trc"的文件
alter session set events '10046 trace name context forever,level 12';
--b.方法二：推荐，因为该方法可以在激活10046事件后执行oradebug tracefile_name命令，得到当前Session对应的trace文件文件的具体路径与名称。
oradebug event 10046 trace name context forever,level 12;

2. 在此Session中执行目标SQL

select * from tf_mdm_ac_rel;
--查看生成trace文件路径
oradebug tracefile_name;

3. 在Session中关闭10046事件

--关闭的两种方法
alter session set events '10046 trace name context off';
oradebug event 10046 trace name context off;

4.使用tkprof命令翻译.trc文件

--10046产生的原始trace文件一般被称为裸trace文件，其内容不直观，为了更直观的显示，可以使用tkprof格式化该裸文件
tkprof 源文件 新文件
cr:逻辑读(consistent reads)
pr:物理读(physical reads)
time:耗费时间(单位为微妙，1/1000/1000秒)
card:实际返回结果行数(Cardinality)