第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*/
from t1, t2
l2 = 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 l2 = t2.col2
5
oracle 时间转换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_TAB LE$的过程互不⼲扰
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_ate_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的"的⽂件
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)
3：如何得到真实的执⾏计划
判断执⾏计划是否准确，就是看⽬标SQL是否被真正执⾏，真正执⾏过的准确，反之不准
3.1 explain plan
不准确，没有实际执⾏SQL，尤其在开启绑定变量窥探的情况下，包含绑定变量的SQL的执⾏计划会随变量不同有所调整，所以不准3.2 DBMX_XPLAN
除了第⼀种：select * from table(dbms_xplan.display)；其他都准确
3.2 AUTOTRACE开关：
1. set autotrace on：SQL实际执⾏
2. set autotrace traceonly:SQL实际执⾏
3. set autotrace traceonly explain：查询语句时，不会实际执⾏，DML语句，实际执⾏
4. 虽然上述情况SQL会被实际执⾏，但是执⾏计划依然不准确，因为使⽤set autotrace命令所显⽰的执⾏计划都来源于调⽤explain
plan命令
4 查看执⾏计划的执⾏顺序
1. 先从最开头⼀直连续往右看，直到看到最右边并列的地⽅；对于不并列的，靠右的先执⾏；如果见到并列的，靠上的先执⾏
发表评论