Oracle SQL优化：查看SQL执行计划的几种方法

Oracle SQL优化：查看SQL执行计划的几种方法

引用

CSDN

1.

https://blog.csdn.net/m0_74824661/article/details/145271638

在数据库管理与开发工作中，SQL优化是一项核心技能。而查看SQL执行计划是进行SQL优化的前提。本文将详细介绍几种查看Oracle SQL执行计划的方法，帮助DBA和开发人员更好地理解和优化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_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
 order by 1 desc;

- 接下来将 SQL_ID 和 CHILD_NUMBER 代入以下SQL。
select * from table(dbms_xplan.display_cursor('czr9jwxv0xra6',0));