Oracle数据库查看SQL执行计划的几种方法

前言

在日常的运维工作中，SQL优化是DBA的进阶技能，SQL优化的前提是要看SQL的执行计划是否正确，下面分享几种查看执行计划的方法，每一种方法都各有各的好处，可以根据特定场景选择某种方法。

一.使用AUTOTRACE查看执行计划

我们利用SQLPLUS中自带的AUTOTRACE工具查看执行计划。AUTOTRACE用法如下。

set autot on:			该命令会运行SQL并且显示运行结果，执行计划和统计信息。
set autot trace：		该命令会运行SQL，但不显示运行结果，会显示执行计划和统计信息。
set autot trace exp:	运行该命令查询语句不执行，DML语句会执行，只显示执行计划。
set autot trace stat：	该命令会运行 SQL，只显示统计信息。
set autot off：			关闭 AUTOTRACE。

在这里插入图片描述

-执行计划中的各个参数解释
recursive calls 			表示递归调用的次数，一个SQL第一次执行就会发生硬解析，在硬解析的时候，优化器会隐含地调用一些内部SQL，因此当一个SQL第一次执行，recursive calls会大于0，第二次执行的时候不需要递归调用，recursive calls就会等于0，如果SQL语句中有自定义函数，recursive calls永远不会等于0，自定义函数被调用了多少次，recursive calls就会显示为多少次
db block gets				表示有多少块发生变化，一般情况下只有DML语句才会导致块发生变化，所以查询语句中的db block gets一般为0
consistent gets				表示逻辑读，单位是块。在SQL优化的时候我们应该想方设法减少逻辑读的个数，通常情况下逻辑读越小，性能越好。需要注意的是，逻辑读并不是衡量SQL执行快慢的唯一标准，需要结合I/O
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 roundtrips to/from client					表示客户端与数据库服务端交互次数，我们可以通过设置arraysize减少交互次数
sorts (memory)				内存排序的次数
sorts (disk)				磁盘排序的次数
rows processed				表示SQL一共返回多少行数据。我们在做SQL优化的时候最关心这部分数据，因为可以根据SQL返回的行数判断整个SQL应该是走HASH连接还是走嵌套循环。如果rows processed很大，一般走HASH连接，如果rows processed很小，一般走嵌套循环。

二.使用EXPLAIN PLAN FOR查看执行计划

用法如下

explain plan for SQL语句;
select * from table(dbms_xplan.display);-查看高级（ADVANCED）执行计划：
explain plan for SQL语句;
select * from table(dbms_xplan.display(NULL, NULL,'advanced -projection'));

三.查看带有A-TIME的执行计划

alter session set statistics_level = all;
select count(*) from test;
select * from table(dbms_xplan.display_cursor(NULL, NULL,'allstats last'));或者在SQL语句中添加hint:/*+ gather_plan_statistics */
select /*+ gather_plan_statistics */ count(*) from test where owner='SYS';
select * from table(dbms_xplan.display_cursor(NULL, NULL,'allstats last'));

在这里插入图片描述

Starts			表示这个操作执行的次数。
E-Rows			表示优化器估算的行数，就是普通执行计划中的Rows。
A-Rows			表示真实的行数。
A-Time			表示累加的总时间。与普通执行计划不同的是，普通执行计划中的Time是假的，而A-Time是真实的。
Buffers			表示累加的逻辑读。
Reads			表示累加的物理读。

上面介绍了3种方法查看执行计划。
使用AUTOTRACE或者EXPLAIN PLAN FOR获取的执行计划来自于PLAN_TABLE。
PLAN_TABLE是一个会话级的临时表，里面的执行计划并不是SQL真实的执行计划，它只是优化器估算出来的。
真实的执行计划不应该是估算的，应该是真正执行过的。SQL执行过的执行计划存在于共享池中，具体存在于数据字典V$SQL_PLAN中。
带有A-Time的执行计划来自于V$SQL_PLAN,是真实的执行计划，而通过AUTOTRACE、通过EXPLAIN PLAN FOR获取的执行计划只是优化器估算获得的执行计划。

四.查看正在执行的SQL的执行计划

有时需要抓取正在运行的SQL的执行计划，这时我们需要获取SQL的SQL_ID以及SQ的CHILD_NUMEBR,然后将其代入下面SQL,就能获取正在运行的SQL的执行计划。

select * from table(dbms_xplan.display_cursor('sql_id',child_number));-在一个会话中执行如下SQL。
select count(*) from a,b where a.owner=b.owner;-在另外一个会话中执行如下SQL  找出sql的sql_id和child_number
select a.sid, a.event, a.sql_id, a.sql_child_number, b.sql_textfrom v$session a, v$sql bwhere a.sql_address = b.addressand a.sql_hash_value = b.hash_valueand a.sql_child_number = b.child_numberorder by 1 desc;-接下来我们将 SQL_ID 和 CHILD_NUMBER 代入以下SQL。
select * from table(dbms_xplan.display_cursor('czr9jwxv0xra6',0));