Oracle 查看执行计划

川长思鸟来 2022-06-15 00:12 492阅读 0赞

查看执行计划的方法：  
利用第三方工具：  
1.在PL/SQL 下按F5查看执行计划  
2.在Toad 下，按Ctrl+E 查看执行计划

通过sqlplus  
**1.预估执行计划 —explain plan**  
Explain plan以SQL语句作为输入，得到这条SQL语句的执行计划，并将执行计划输出存储到计划表中。  
首先，在你要执行的SQL语句前加explain plan for，此时将生成的执行计划存储到计划表中，语句如下：  
explain plan for SQL语句  
然后，在计划表中查询刚刚生成的执行计划，语句如下：  
select \* from table(dbms\_xplan.display);  
**具体操作如下：**  
SQL> explain plan for select \* from bsuser.goodsdoc; ---要解析的SQL语句

Explained.

SQL> select \* from table(dbms\_xplan.display);

PLAN\_TABLE\_OUTPUT  
\--------------------------------------------------------------------------------  
Plan hash value: 4226420140

\------------------------------------------------------------------------------  
| Id  | Operation   | Name     | Rows  | Bytes | Cost (%CPU)| Time     |  
\------------------------------------------------------------------------------  
|   0 | SELECT STATEMENT  |      | 10503 | 3159K|  136   (0)| 00:00:02 |  
|   1 |  TABLE ACCESS FULL| GOODSDOC | 10503 | 3159K|  136   (0)| 00:00:02 |  
\------------------------------------------------------------------------------

8 rows selected.  
**注意：Explain plan只生成执行计划，并不会真正执行SQL语句，因此产生的执行计划有可能不准，因为：  
1）当前的环境可能和执行计划生成时的环境不同；  
2）不会考虑绑定变量的数据类型；  
3）不进行变量窥视。**

**2.查询内存中缓存的执行计划 （dbms\_xplan.display\_cursor）**  
如果你想获取正在执行的或刚执行结束的SQL语句真实的执行计划（即获取library cache中的执行计划），可以到动态性能视图里查询。方法如下  
1）获取SQL语句的游标  
游标分为父游标和子游标，父游标由sql\_id(或联合address和hash\_value)字段表示，子游标由child\_number字段表示。  
如果SQL语句正在运行，可以从v$session中获得它的游标信息，如：  
select status, sql\_id, sql\_child\_number from v$session where status='ACTIVE' and ....  
如果知道SQL语句包含某些关键字，可以从v$sql视图中获得它的游标信息，如：  
select sql\_id, child\_number, sql\_text from v$sql where sql\_text like '%关键字%‘  
2）获取库缓存中的执行计划  
为了获取缓存库中的执行计划，可以直接查询动态性能视图v$sql\_plan和v$sql\_plan\_statistics\_all等，但更方便的方法是以sql\_id和子游标为参数，执行如下语句：  
select \* from table(dbms\_xplan.display\_cursor('sql\_id',child\_number));  
3）获取前一次的执行计划  
set serveroutput off  
select \* from table(dbms\_xplan.display\_cursor(null,null,'ALLSTATS LAST'));  
**3.查询历史执行计划查询历史执行计划（dbms\_xplan.display\_awr）**  
AWR会定时把动态性能视图中的执行计划保存到dba\_hist\_sql\_plan视图中，如果你想要查看历史执行计划，可以采用如下方法查询：  
select \* from table(dbms\_xplan.display\_awr('sql\_id');  
  
**4.autotrace**  
set autotrace是sqlplus工具的一个功能，只能在通过sqlplus连接的session中使用，它非常适合在开发时测试SQL语句的性能，有以下几种参数可供选择：  
SET AUTOTRACE OFF ---------------- 不显示执行计划和统计信息，这是缺省模式  
SET AUTOTRACE ON EXPLAIN ------ 只显示优化器执行计划  
SET AUTOTRACE ON STATISTICS -- 只显示统计信息  
SET AUTOTRACE ON ----------------- 执行计划和统计信息同时显示  
SET AUTOTRACE TRACEONLY ------ 不真正执行，只显示预期的执行计划，同explain plan  
set autotrace  
**具体操作如下：**  
SQL> set autotrace on  ---执行完语句后，会显示explain plan 与 统计信息。  
SQL> select \* from dual;

D  
\-  
X

Execution Plan  
\----------------------------------------------------------  
Plan hash value: 272002086

\--------------------------------------------------------------------------  
| Id  | Operation   | Name | Rows  | Bytes | Cost (%CPU)| Time  |  
\--------------------------------------------------------------------------  
|   0 | SELECT STATEMENT  |  |     1 |     2 |     2   (0)| 00:00:01 |  
|   1 |  TABLE ACCESS FULL| DUAL |     1 |     2 |     2   (0)| 00:00:01 |  
\--------------------------------------------------------------------------

Statistics  
\----------------------------------------------------------  
   2  recursive calls  
   0  db block gets  
   5  consistent gets  
   0  physical reads  
   0  redo size  
 522  bytes sent via SQL\*Net to client  
 523  bytes received via SQL\*Net from client  
   2  SQL\*Net roundtrips to/from client  
   0  sorts (memory)  
   0  sorts (disk)  
   1  rows processed

SQL>

SQL> set autotrace traceonly --只会列出执行计划，而不会真正的执行语句  
SQL> select \* from dual;

Execution Plan  
\----------------------------------------------------------  
Plan hash value: 272002086

Statistics  
\----------------------------------------------------------  
   0  recursive calls  
   0  db block gets  
   2  consistent gets  
   0  physical reads  
   0  redo size  
 522  bytes sent via SQL\*Net to client  
 523  bytes received via SQL\*Net from client  
   2  SQL\*Net roundtrips to/from client  
   0  sorts (memory)  
   0  sorts (disk)  
   1  rows processed

SQL>

**5.生成Trace文件查询详细的执行计划 （SQL\_Trace, 10046）**  
SQL\_TRACE作为初始化参数可以在实例级别启用，也可以只在会话级别启用，在实例级别启用SQL\_TRACE会导致所有进程的活动被跟踪，包括后台进程及所有用户进程，这通常会导致比较严重的性能问题，所以在一般情况下，我们使用sql\_trace跟踪当前进程，方法如下：  
SQL>alter session set sql\_trace=true;  
...被跟踪的SQL语句...  
SQL>alter session set sql\_trace=false;  
如果要跟踪其它进程，可以通过Oracle提供的系统包DBMS\_SYSTEM. SET\_SQL\_TRACE\_IN\_SESSION来实现，例如：  
SQL> exec dbms\_system.set\_sql\_trace\_in\_session(sid,serial\#,true) --开始跟踪  
SQL> exec dbms\_system.set\_sql\_trace\_in\_session(sid,serial\#,false) --结束跟踪

生成trace文件后，再用tkprof 工具将sql trace 生成的跟踪文件转换成易读的格式，语法如下：  
tkprof inputfile outputfile  
10046事件是SQL\_TRACE的一个升级版，它也是追踪会话，生成Trace文件，只是它里面的内容更详细.

**具体操作如下：**  
下面用例子来跟踪一个sql语句的执行情况：  
SQL>  create table t as select rownum as id,object\_name from dba\_objects;

Table created.

SQL>  create index t\_ind on t(id);

Index created.

SQL> alter session set tracefile\_identifier='mysession';

Session altered.

SQL> alter session set sql\_trace=true;

Session altered.

SQL>  select \* from t where id=123;

ID  
\----------  
OBJECT\_NAME  
\--------------------------------------------------------------------------------  
       123  
SYS\_IL0000000123C00003$$

SQL> alter session set sql\_trace=false;

Session altered.  
生成的trace文件的路径是/u01/app/oracle/diag/rdbms/orcl/orcl/trace  
orcl:/u01/app/oracle/diag/rdbms/orcl/orcl/trace@oracle>ll \*my\*  
\-rw-r----- 1 oracle dba 6158 5月  18 15:14 orcl\_ora\_27485\_mysession.trc  
\-rw-r----- 1 oracle dba   94 5月  18 15:14 orcl\_ora\_27485\_mysession.trm  
orcl:/u01/app/oracle/diag/rdbms/orcl/orcl/trace@oracle>  
一般来讲生成的trace文件比较难读,可以用tkprof来生成一个更可读的文件.  
注意tkprof是Oracle带的一个命令行工具,不是SQLPLUS命令  
orcl:/u01/app/oracle/diag/rdbms/orcl/orcl/trace@oracle>tkprof orcl\_ora\_27485\_mysession.trc orcl\_ora\_27485\_mysession.text

对于每一条SQL语句,都包含3个步骤:  
Parse:SQL的分析阶段.  
Execute:SQL的执行阶段.  
Fetch:数据提取阶段.(对于一次SQL操作,Fetch可能多次)  
横向的标题头为:  
count:当前的操作执行的次数.  
cpu:当前操作消耗的CPU时间(秒).  
elapsed:当前操作消耗的时间(CPU时间加等待时间).  
disk:磁盘的IO次数.  
query:当前操作的一致性读取的数据块数.  
current:前操作的current读取的数据块数(通常在update操作时发生).  
rows:处理的记录行数.

Misses in library cache during parse是指是不是重用了执行计划,如果同一条SQL语句第二次执行,  
其值为零,这里是1,表示在共享池里没有这一条SQL的执行计划,发生了一次硬解析.  
Optimizer:优化器模式.  
Parsing user id:分析的用户ID.  
Row Source Operation部分包含的实际消耗的资源.  
cr:一致性读取的数据块,相当于Fetch的query.  
pr:物理读, 相当于Fetch的disk.  
pw:物理写.  
time:当前操作的执行时间.