你的位置:首页 > 数据库

[数据库]Oracle_SQL执行计划


获取SQL执行计划的常见几种方法

一、获取库缓冲区中的执行计划

1. 查询v$sql动态性能视图,找到要查询的SQL语句的sql_id

2. 调用dbms_xplan包display_cursor方法,查看该语句执行时的执行计划。

例如:

SELECT dname FROM emp, dept WHERE emp.deptno=dept.deptno;

SELECT sql_id FROM v$sql WHERE sql_text='SELECT dname FROM emp, dept WHERE emp.deptno=dept.deptno';

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('00gyc848k0q4v'));

直接调用display_cursor,不指定sql_id,就可以将刚刚当前会话执行的SQL命令执行计划从library cache中查询出来。

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR());

注意:display_cursor支持format参数,可以进行详细执行计划信息的抽取。

只能在sqlplus或者sqlplusw上使用,如果是TOAD、PL/SQL develop等其它第三方工具,可能调用程序包dbms_xplan不能正常使用。

二、获取SQL预执行的执行计划

Explain plan for 命令在Oracle中,可以对后面的SQL语句进行直接的解析,将执行计划保存在一个plan_table的中间表中,之后通过dbms_xplan包display方法进行获取。

例如:

EXPLAIN PLAN FOR SELECT dname FROM emp, dept WHERE emp.deptno=dept.deptno;

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY());

通过指定参数,Explain plan for命令可以显示更详细的信息。

例如:

EXPLAIN PLAN FOR SELECT dname FROM emp, dept WHERE emp.deptno=dept.deptno;

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY(null, null, 'advanced'));

使用EXPLAIN PLAN FOR 需要注意的是:

explain plan for是单纯对SQL语句进行优化器分析,获取并产生到的执行计划。

这个过程中,并没有真正执行。

所以,生成的执行计划有时候会可能有问题,而且进行统计的信息情况没有autotrace的准确度高。

三、使用autotrace工具分析SQL的执行计划

1.配置autotrace工具

配置AUTOTRACE 的方法不止一种,以下是其中一种的方式:

(1)cd [ORACLE_HOME]/sqlplus/admin;
(2)作为SYS 或SYSDBA 登录SQL*Plus;
(3)运行@plustrce.sql;
(4)运行GRANT PLUSTRACE TO PUBLIC。

注:ORACLE_HOME是指Oracle的安装路径,如果是在Windows系统下,可以进入到相应路径再运行脚本。

如果愿意,可以把GRANT TO PUBLIC 中的PUBLIC 替换为某个用户。
通过将PLAN_TABLE置为public,任何人都可以使用SQL*Plus 进行跟踪。

这么一来,就不需要每个用户都安装自己的计划表。

以下是脚本plustrce.sql中的内容:

set echo on

drop role plustrace;
create role plustrace;

grant select on v_$sesstat to plustrace;
grant select on v_$statname to plustrace;
grant select on v_$mystat to plustrace;
grant plustrace to dba with admin option;

set echo off

大概意思如下:

创建角色plustrace。

将在动态性能视图v_$sesstat,v_$statname,v_$mystat上的SELECT权限授予该角色。

把该角色授权给DBA用户,并使其有权限将该角色授予其它用户。

注:v$_开头的动态性能视图是以v_$开头的视图的同义词。

可以通过查询视图dba_objects确认相关对象的类型。

例如: 

select object_type from dba_objects where object_name=upper('v_$sesstat');

select object_type from dba_objects where object_name=upper('v$sesstat');

2. 关于如何使用autotrace工具

用法1:查看执行计划统计信息并且返回sql结果集

SET AUTOTRACE ON

用法2:查看执行计划统计信息不返回sql结果集

SET AUTOTRACE TRACEONLY

用法3:查看执行计划不返回统计信息、不返回sql结果集:

SET AUTOTRACE TRACEONLY EXPLAIN

用法4:查看统计信息不返回执行计划、不返回sql结果集:

SET AUTOTRACE TRACEONLY STATISTICS

以上是查看SQL执行计划的几种方式。

关于如何分析Oracle SQL执行计划

举例说明一下:

explain plan for  SELECT dname, ename FROM emp, dept WHERE emp.deptno=dept.deptno;

select * from table(dbms_xplan.display()); 

Plan hash value: 351108634

----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 15 | 330 | 4 (0)| 00:00:01 |
| 1 |  NESTED LOOPS | | 15 | 330 | 4 (0)| 00:00:01 |
| 2 |   TABLE ACCESS FULL | EMP | 15 | 135 | 3 (0)| 00:00:01 |
| 3 |   TABLE ACCESS BY INDEX ROWID| DEPT | 1 | 13 | 1 (0)| 00:00:01 |
|* 4 |   INDEX UNIQUE SCAN | PK_DEPT | 1 | | 0 (0)| 00:00:01 |
----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

4 - access("EMP"."DEPTNO"="DEPT"."DEPTNO")

Statistics
-----------------------------------------------------------
195 recursive calls
11 db block gets
2 consistent gets
0 physical reads
716 redo size
594 bytes sent via SQL*Net to client
381 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)

一、通过执行计划查看SQL语句执行先后顺序

看执行计划时,首先从缩进最大的行读取,它是最先被执行的步骤。在执行计划中:id=4是最先被执行的,

|* 4 |   INDEX UNIQUE SCAN | PK_DEPT | 1 | | 0 (0)| 00:00:01 |

两行缩进一样的,最上面的最先被执行,在这里就是id=2

| 2 |   TABLE ACCESS FULL | EMP | 15 | 135 | 3 (0)| 00:00:01 |

然后是id=3

| 3 |   TABLE ACCESS BY INDEX ROWID| DEPT | 1 | 13 | 1 (0)| 00:00:01 |

选择次之缩进的行数id=1

| 1 |  NESTED LOOPS | | 15 | 330 | 4 (0)| 00:00:01 |

最后是id=0

| 0 | SELECT STATEMENT | | 15 | 330 | 4 (0)| 00:00:01 |

二、执行计划中字段解释:

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |

Id: 一个序号,但不是执行的先后顺序。

Operation:当前操作的内容。

Name :涉及的对象名称。

Rows :前操作的Cardinality(基数),Oracle估计当前操作的返回结果集。

Bytes :操作涉及内容的大小。

Cost (%CPU):Oracle计算出来的一个数值(代价),用于说明sql执行的代价。

Time: Oracle估算当前操作的时间。

三、谓词说明:

Access: 表示这个谓词条件的值将会影响数据的访问路劲(表还是索引)。 

Filter:表示谓词条件的值不会影响数据的访问路劲,只起过滤的作用。