「这是我参与2022首次更文挑战的第7天，活动详情查看：2022首次更文挑战」

前言

执行计划：一条查询语句在ORACLE中的执行过程或访问路径的描述。即就是对一个查询任务，做出一份怎样去完成任务的详细方案。

如果要分析某条SQL的性能问题，通常我们要先看SQL的执行计划，看看SQL的每一步执行是否存在问题。看懂执行计划也就成了SQL优化的先决条件。通过执行计划定位性能问题，定位后就通过建立索引、修改sql等解决问题。

执行计划的6中查看方式

1-explain plan

先使用explain plan命令对目标SQL做explain，再使用"select * from table(dbms_xplan.display)"查看上述使用explain plan命令后得到的执行计划。 PL/SQL Developer中的快捷键F5就是在explain plan命令上的一层封装。

set line 200
set pagesize 500
explain plan for + SQL
select * from table(dbms_xplan.display)`

优点：无需真正执行，快捷方便

缺点：没有输出运行时的相关统计信息（产生逻辑读，递归，物理读等情况）无法判断是处理了多少行无法判断表被访问了多少次

2-SQLPLUS中的AUTOTRACE

SET AUTOTRACE OFF 此为默认值，即关闭Autotrace

SET AUTOTRACE ON 产生结果集和解释计划并列出统计

SET AUTOTRACE ON EXPLAIN 显示结果集和解释计划不显示统计

SET AUTOTRACE TRACEONLY 显示解释计划和统计，尽管执行该语句但您将看不到执行结果集

SET AUTOTRACE TRACEONLY STATISTICS 只显示统计

Autotrace Statistics 常用列解释

db block gets 从buffer cache中读取的block的数量

consistent gets 从buffer cache中读取的undo数据的block的数量

physical reads 从磁盘读取的block的数量

redo size DML生成的redo的大小

sorts (memory) 在内存执行的排序量

sorts (disk) 在磁盘上执行的排序量

优点：可以输出运行时相关统计信息（逻辑读、递归、物理读等情况）虽然必须要等语句执行完毕才能输出执行计划，但是可以有traceonly开关来控制返回结果不输出

缺点：必须等语句真正执行完毕，才能输出结果无法看到表被访问了多少次

3-statistics_level=all

Step1：alter session set statistics_level=all;
Step2：执行sql
Step3：select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));

a. 如果用 /*+ gather_plan_statistics */的方法，可以省略步骤1，直接步骤2,3。

b. 关键字解读（其中OMem、1Mem和User-Mem在后续的课程中会陆续见到）：

Starts：该sql执行的次数。

E-Rows：执行计划预计的行数。

A-Rows：实际返回的行数。A-Rows跟E-Rows做比较，就可以确定哪一步执行计划出了问题。

A-Time：每一步实际执行的时间（HH：MM：SS.FF），根据这一行可以知道该sql耗时在了哪个地方。

Buffers：每一步实际执行的逻辑读或一致性读。

Reads：物理读。

OMem：当前操作完成所有内存工作区(Work Aera)操作所总共使用私有内存(PGA)中工作区的大小，这个数据是由优化器统计数据以及前一次执行的性能数据估算得出的

1Mem：当工作区大小无法满足操作所需的大小时，需要将部分数据写入临时磁盘空间中(如果仅需要写入一次就可以完成操作，就称一次通过，One-Pass;否则为多次通过，Multi_Pass).该列数据为语句最后一次执行中，单次写磁盘所需要的内存大小，这个由优化器统计数据以及前一次执行的性能数据估算得出的

User-Mem：语句最后一次执行中，当前操作所使用的内存工作区大小，括号里面为(发生磁盘交换的次数,1次即为One-Pass,大于1次则为Multi_Pass,如果没有使用磁盘，则显示OPTIMAL)

OMem、1Mem为执行所需的内存评估值，0Mem为最优执行模式所需内存的评估值，1Mem为one-pass模式所需内存的评估值。0/1/M 为最优/one-pass/multipass执行的次数。Used-Mem耗的内存

优点：可以清晰的从start得出表被访问多少;可以清晰的从e-rows和a-rows中得到预测的行数和真实的行数，从而可以准确判断oracle评估是否准确;虽然没有专门的输出运行时的相关统计信息，但是执行计划中的buffers就是真实的逻辑读

缺点：必须等语句执行完毕后才出结果;无法控制记录输屏输出，不想autotrace有traceonly可以控制不将结果输出;看不出递归调用的次数，看不出物理读

4-通过dbms_xplan.display_cursor输入sql_id参数直接获取

SQL>select * from table(dbms_xplan.display_cursor('&sq_id')); ----#从共享池获取

SQL>select * from table(dbms_xplan.display_awr('&sq_id')); 从awr性能视图获取

查看多个sql的执行计划

select * from table(dbms_xplan.display_cursor('cyzznbykb509s',0));
select * from table(dbms_xplan.display_cursor('cyzznbykb509s',1));

优点:知道sql_id立即可得到执行计划，和explain plan for 一样无需执行；可以得到真实的执行计划。

缺点:没有输出运行时的相关统计信息（产生多少逻辑读，多少次递归调用，多少次物理读的情况）；无法判断是处理了多少行；无法判断表被访问了多少次

5-10046事件

与之前查看执行计划方法不同之处在于，所得到的执行计划中明确显示了目标SQL实际执行计划中每一个执行步骤所消耗的逻辑读、物理读和花费的时间。执行计划与明细资源消耗会写入此Session所对应的trace文件中，Oracle会在参数USER_DUMP_DEST所代表的目录下生成这个trace文件。

首先在当前Session中激活10046事件

接着在此Session中执行目标SQL

最后在此Seesion中关闭10046事件

#激活10046事件两种方法 alter session set events '10046 trace name context forever,level 12' oradebug event 10046 trace name context forever,level 12

#关闭10046事件两种方法

alter session set events '10046 trace name conetxt off'
oradebug event 10046 trace name context off

10046时间级别：

Lv1 - 启用标准的SQL_TRACE功能,等价于SQL_TRACE

Lv4 - Level 1 + 绑定值(bind values)

Lv8 - Level 1 + 等待事件跟踪

Lv12 - Level 1 + Level 4 + Level 8

对其他用户进行设置：

SQL> select sid,serial#,username from v$session where username='XXX';
       SID    SERIAL# USERNAME
    ------ ---------- ------------------
       127      31923 A
 SQL> exec dbms_system.set_ev(127,31923,10046,8,'A');

alter session set events '10046 trace name context forever,level 12';
执行SQL
alter session set events '10046 trace name context off';

select d.value
    || '/'
    || LOWER (RTRIM(i.INSTANCE, CHR(0)))
    || '_ora_'
    || p.spid
    || '.trc' trace_file_name
    from (select p.spid
          from v$mystat m,v$session s, v$process p
          where  m.statistic#=1 and s.sid=m.sid and p.addr=s.paddr) p,
         (select t.INSTANCE
          FROM v$thread t,v$parameter v
          WHERE v.name='thread'
          AND(v.VALUE=0 OR t.thread#=to_number(v.value))) i,
          (select value
          from v$parameter
          where name='user_dump_dest') d;    ---查看刚才跟踪生成的文件

tkprof trc文件目标文件 sys=no sort=prsela,exeela,fchela （格式化命令）

优点

可以看出SQL语句对应的等待事件

如果SQL语句中有函数调用，SQL中有SQL，将会都被列出，无处遁形。

可以方便的看出处理的行数，产生的物理逻辑读。

可以方便的看出解析时间和执行时间。

可以跟踪整个程序包

缺陷:

步骤繁琐，比较麻烦

无法判断表被访问了多少次。

执行计划中的谓词部分不能清晰的展现出来。

6-awrsqlrpt.sql

PL/SQL 工具—>首选项—>窗口类型—>计划窗口—>根据需要配置要显示在执行计划中的列

执行计划的常用列字段解释：

-基数（Rows）：Oracle估计的当前操作的返回结果集行数

字节（Bytes）：执行该步骤后返回的字节数

耗费（COST）、CPU耗费：Oracle估计的该步骤的执行成本，用于说明SQL执行的代价，理论上越小越好（该值可能与实际有出入）

时间（Time）：Oracle估计的当前操作所需的时间

总结：

1.如果某SQL执行非常长时间才会出结果，甚至慢到返回不了结果，这时候看执行计划就只能用方法1；
2.跟踪某条SQL最简单的方法是方法1，其次就是方法2；
3.如果想观察到某条SQL有多条执行计划的情况，只能用方法4和方法6；
4.如果SQL中含有多函数，函数中套有SQL等多层递归调用，想准确分析，只能使用方法5；
5.要想确保看到真实的执行计划，不能用方法1和方法2；
6.要想获取表被访问的次数，只能使用方法3；

每天一个dba小知识之执行计划

前言