Oracle执行计划

---

Oracle执行计划简介

• 执行计划：一条查询语句在ORACLE中的执行过程或访问路径的描述。即就是对一个查询任务，做出一份怎样去完成任务的详细方案。
• 如果要分析某条SQL的性能问题，通常我们要先看SQL的执行计划，看看SQL的每一步执行是否存在问题。 看懂执行计划也就成了SQL优化的先决条件。 通过执行计划定位性能问题，定位后就通过建立索引、修改sql等解决问题。

一、查看Oracle执行计划

1.1、方法一：explain plan for SQL

执行下面的SQL：

explain plan for select * from info;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY());

1.2、方法二：AUTOTRACE命令

这个命令必须在sql*plus中运行，在PL/SQL中会报错

1.2.1、开启AUTOTRACE功能

SET AUTOTRACE ON

1.2.2、执行sql语句会显示执行计划

然后执行SQL语句就可以把该语句的执行计划显示出来了：

1.2.3、AUTOTRACE故障处理（SP2-0618、SP2-0611）

AUTOTRACE故障处理可参考下面的博文：
Oracle AUTOTRACE故障处理：SP2-0618、SP2-0611

二、Oracle中查看已执行sql的执行计划

2.1、dbms_xplan.display_cursor()函数来获取执行过的sql的执行计划

2.1.1、dbms_xplan.display_cursor()函数语法

  -- display from V$SQL_PLAN (or V$SQL_PLAN_STATISTICS_ALL)
  function display_cursor(sql_id           varchar2 default  null,
                          cursor_child_no  integer  default  0,
                          format           varchar2 default  'TYPICAL')
  return dbms_xplan_type_table
  pipelined;

由上可知，我们至少需要找到执行过sql的sql_id，该参数可以从v$sql视图中找到。
如果我们想获取该语句的实际执行计划，通过下列步骤：

2.1.2、查询v$sql视图，找到该语句的sql_id(注意哟，必须要确保你要查询的sql语句还在shared pool)

SELECT COUNT(1) FROM info;
SELECT * FROM v$sql WHERE LOWER(sql_text) LIKE '% from info%' ORDER BY last_active_time DESC;

2.1.3、调用dbms_xplan包，查看该语句执行时的实际执行计划

select * from table(dbms_xplan.display_cursor('3nwy7gp6h8ffh'));

2.1.4、事实上dbms_xplan.display_cursor也非常灵活，如果执行的统计信息也被收集的话，还可以显示出每一步实际的花费时间等信息

例如：

SELECT /*+ gather_plan_statistics */ COUNT(1) FROM info;
SELECT * -- sql_id 
  FROM v$sql 
 WHERE sql_text = 'SELECT /*+ gather_plan_statistics */ COUNT(1) FROM info ' 
 ORDER BY last_active_time DESC;

select * from table(dbms_xplan.display_cursor('5jtb40d5d8pcr'));

2.2、dba_hist_sql_plan视图

select SQL_ID,
       PLAN_HASH_VALUE,
       ID,
       OPERATION,
       OBJECT_OWNER,
       OBJECT_NAME,
       DEPTH,
       COST,
       TIMESTAMP
  from dba_hist_sql_plan
 where 1=1
   AND sql_id = '5jtb40d5d8pcr'
   and TIMESTAMP >= date '2023-08-15'
   and TIMESTAMP <= date '2023-08-27'
 order by TIMESTAMP;

2.3、Oracle AWR报告

2.3.1、用sqlplus以sysdba身份登录到Oracle数据库

以管理员权限打开命令窗口，输入：

sqlplus / as sysdba

以sysdba身份登录到数据库。

2.3.2、手工创建快照dbms_workload_repository.create_snapshot()

select dbms_workload_repository.create_snapshot() from dual;

2.3.3、使用awrrpt.sql生成awr报告

执行下面的命令：

@D:\oracle_database\11.2.0\dbhome_1\RDBMS\ADMIN\awrsqrpt.sql

2.3.3.1、输入 report_type 的值

默认html

2.3.3.2、输入 num_days 的值:

自己选，本博文演示选1天

2.3.3.3、输入想要抓取的时间范围所对应的开始和结束的snap_id

2.3.3.4、查找snap_id里面包含的sql_id

执行下面sql查询：

-- 查询快照SNAP_ID对应的sql_id，及sql_id里面的sql_text内容
SELECT ids.sql_id
      ,s.SQL_TEXT
      ,snp.snap_id
      ,snp.startup_time
      ,snp.begin_interval_time
      ,snp.end_interval_time
  FROM dba_hist_snapshot snp
 INNER JOIN dba_hist_sqlstat ids
    ON (ids.snap_id = snp.snap_id)
  LEFT JOIN v$sql s
    ON s.SQL_ID = ids.sql_id
 WHERE LOWER(s.SQL_TEXT) LIKE '%from info%';

或者用dba_hist_sqltext表拿SQL文本内容：

SELECT ids.sql_id
      ,to_char(s.sql_text) AS sql_text
      ,snp.snap_id
      ,snp.startup_time
      ,snp.begin_interval_time
      ,snp.end_interval_time
  FROM dba_hist_snapshot snp
 INNER JOIN dba_hist_sqlstat ids
    ON (ids.snap_id = snp.snap_id)
  LEFT JOIN dba_hist_sqltext s
    ON s.sql_id = ids.sql_id
 WHERE lower(s.sql_text) LIKE '%from info%';

得到sql_id为：b6qr4gqd7x57t

2.3.3.5、输入sql_id

2.3.3.6、输入 report_name 的值

本博文演示，使用默认值 awrsqlrpt_1_800_811.html

等待报告生成完毕：

2.3.3.7、查看报告

报告生成在cmd命令窗口目录：C:\Windows\System32

可以看到sql_id只有一个执行计划。

三、Oracle执行计划相关视图

3.1、dba_hist_snapshot：快照历史表

select t.snap_id,t.dbid,instance_number
      ,to_char(t.startup_time,'yyyy-mm-dd hh24:mi:ss') AS startup_time
      ,to_char(t.begin_interval_time,'yyyy-mm-dd hh24:mi:ss') AS begin_interval_time
      ,to_char(t.end_interval_time,'yyyy-mm-dd hh24:mi:ss') AS end_interval_time
      ,t.*
  from dba_hist_snapshot t
 ORDER BY 1 DESC;

3.2、dba_hist_sqlstat：SQL状态历史表

SELECT * FROM dba_hist_sqlstat WHERE sql_id IN ('b6qr4gqd7x57t');

3.3、dba_hist_sql_plan：SQL执行计划历史表

SELECT * FROM DBA_HIST_SQL_PLAN WHERE sql_id IN ('b6qr4gqd7x57t');

3.4、dba_hist_sqltext：SQL文本内容历史表

根据SQL_ID获取SQL语句内容：

-- 根据SQL_ID获取SQL语句内容
SELECT command_type
      ,sql_text
  FROM dba_hist_sqltext
 WHERE sql_id = 'b6qr4gqd7x57t';