Oracle SQL优化①——查看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一般为0consistent 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查看执行计划

用法如下

explainplanforSQL语句;select*fromtable(dbms_xplan.display);-查看高级（ADVANCED）执行计划：
explainplanforSQL语句;select*fromtable(dbms_xplan.display(NULL,NULL,'advanced -projection'));

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

altersessionset statistics_level =all;selectcount(*)from test;select*fromtable(dbms_xplan.display_cursor(NULL,NULL,'allstats last'));

或者在SQL语句中添加hint:/*+ gather_plan_statistics */select/*+ gather_plan_statistics */count(*)from test where owner='SYS';select*fromtable(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*fromtable(dbms_xplan.display_cursor('sql_id',child_number));-在一个会话中执行如下SQL。
selectcount(*)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_text
  from v$session a, v$sql b
 where a.sql_address = b.address
   and a.sql_hash_value = b.hash_value
   and a.sql_child_number = b.child_number
 orderby1desc;-接下来我们将 SQL_ID 和 CHILD_NUMBER 代入以下SQL。
select*fromtable(dbms_xplan.display_cursor('czr9jwxv0xra6',0));