合 Oracle执行计划介绍
简介
执行计划指示Oracle如何获取和过滤数据、产生最终结果集,这是影响SQL语句执行性能的关键因素。在深入了解执行计划之前,首先需要知道执行计划是在什么时候产生的,以及如何让SQL引擎为语句生成执行计划。
在Oracle中,任何一条语句在解析过程中都会生成一个唯一的数值标识,即SQL_ID。而同一条语句,在解析过程中,可能会因为执行环境的改变(例如某些优化参数被改变)而生成多个版本的游标,不同的游标会有不同的执行计划。每个游标都会按顺序赋予一个序列号,即CHILD_NUMBER,一条语句生成的第一个游标的CHILD_NUMBER为0;相应的,Oracle会为每个执行计划生成一个HASH值以作区分。而多个不同版本的游标,其执行计划可能会相同,也可能不同。
获取执行计划有哪几种方法?
一般来说,有如下几种获取执行计划的方式:
1、AUTOTRACE方式
AUTOTRACE是Oracle自带的客户端工具SQL*Plus
的一个特性。启用AUTOTRACE后,SQL*Plus
会自动收集执行过的SQL语句的执行计划、性能统计数据等,并在语句执行结束后显示在SQL*Plus
中。
DBA用户可以直接使用AUTOTRACE功能,但是如果用户没有DBA权限,那么需要在SYS用户下执行plustrce.sql脚本,自动创建PLUSTRACE角色,再把PLUSTRACE权限赋给普通用户即可。
1 2 | $ORACLE_HOME/sqlplus/admin/plustrce.sql GRANT PLUSTRACE TO USER_LHR; |
另外,若启用AUTOTRACE报“SP2-0611”的错误,则可以执行utlxplan.sql脚本来创建表PLAN_TABLE,如下所示:
1 2 3 4 | SQL> set autot on SP2-0613: 无法验证 PLAN_TABLE 格式或实体 SP2-0611: 启用EXPLAIN报告时出错 SQL> @?/rdbms/admin/utlxplan.sql |
在执行如下脚本后,每个用户(包括以后新建的用户)都可以使用AUTOTRACE命令:
1 2 3 4 5 | @?/rdbms/admin/utlxplan.sql CREATE PUBLIC SYNONYM PLAN_TABLE FOR PLAN_TABLE; GRANT ALL ON PLAN_TABLE TO PUBLIC; @?/sqlplus/admin/plustrce.sql GRANT PLUSTRACE TO PUBLIC; |
AUTOTRACE的语法如下所示:
1 | SET AUTOTRACE {OFF|ON|TRACEONLY} [EXPLAIN] [STATISTICS] |
其中,AUTOTRACE可简写为AUTOT,TRACEONLY可简写为TRACE,EXPLAIN可简写为EXP,STATISTICS可简写为STAT。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 | SQL> SET AUTOT ON SQL> SELECT COUNT(*) FROM PLAN_TABLE; COUNT(*) ---------- 68 Execution Plan ---------------------------------------------------------- Plan hash value: 1751138260 -------------------------------------------------------------------------- | Id | Operation | Name | Rows | Cost (%CPU)| Time | -------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 3 (0)| 00:00:01 | | 1 | SORT AGGREGATE | | 1 | | | | 2 | TABLE ACCESS FULL| PLAN_TABLE$ | 68 | 3 (0)| 00:00:01 | -------------------------------------------------------------------------- Note ----- - dynamic sampling used for this statement Statistics ---------------------------------------------------------- 27 recursive calls 0 db block gets 15 consistent gets 0 physical reads 0 redo size 515 bytes sent via SQL*Net to client 487 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed |
AUTOTRACE STATISTICS含义见下表:
序号 | 列名 | 解释 |
---|---|---|
1 | recursive calls | 递归调用,表示执行SQL的时候的产生的递归调用的次数。Oracle在执行SQL的时候,有时候会生成很多额外的SQL语句,这个就称为递归调用。这个参数和访问数据字典的次数有很大的关系,一般来说,这个参数值不会很大。 |
2 | db block gets | DB块取,表示当前读。在发生INSERT、DELETE、UPDATE和SELECT FOR UPDATE的时候,数据库缓冲区中的数据库块的个数。在SELECT语句中一般为0。 |
3 | consistent gets | 一致性读,表示除了SELECT FOR UPDATE的时候,从数据库缓冲区中读取的数据块的个数(注意,实际上并不是块的个数),可能会读取回滚段的信息,一般来说,逻辑读(Logical Reads) = 当前读(db block gets) + 一致性读(consistent gets)。 |
4 | physical reads | 物理读,在执行SQL的过程中,从硬盘上读取的数据块个数。 |
5 | redo size | SQL语句在执行过程中产生的Redo的字节数。 |
6 | bytes sent via SQL*Net to client | 服务器利用SQL*Net发送到客户端的字节数。 |
7 | bytes received via SQL*Net from client | 服务器利用SQL*Net从客户端接收的字节数。 |
8 | SQL*Net roundtrips to/from client | 从客户端发送和接收的SQL*Net消息的总数,包括从多行的结果集中提取的往返消息。 |
9 | sorts (memory) | 在内存执行的排序次数。 |
10 | sorts (disk) | 在磁盘上执行的排序次数,如果内存空间不足,那么会使用磁盘空间。 |
11 | rows processed | 更改或选择返回的行数。 |
2、EXPLAIN PLAN FOR方式
1 2 3 4 5 6 7 8 9 10 11 | SQL> EXPLAIN PLAN FOR SELECT * FROM T017_LHRO; SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY); PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------- Plan hash value: 3200443156 ------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1363 | 177K| 9 (0)| 00:00:01 | | 1 | TABLE ACCESS FULL| T017_LHRO | 1363 | 177K| 9 (0)| 00:00:01 | ------------------------------------------------------------------------------- |
3、DBMS_XPLAN.DISPLAY_CURSOR方式
1 2 3 4 5 6 7 8 9 10 11 | SYS@RAC2LHR1> SELECT * FROM V$VERSION WHERE ROWNUM<2; BANNER -------------------------------------------------------------------------------- Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production SYS@RAC2LHR1> SELECT ENAME,DNAME,LOC 2 FROM SCOTT.EMP E,SCOTT.DEPT D 3 WHERE E.DEPTNO = D.DEPTNO 4 AND E.EMPNO = 7788; ENAME DNAME LOC ---------- -------------- ------------- SCOTT RESEARCH DALLAS |
如果不传递任何参数给DISPLAY_CURSOR函数,那么默认显示当前会话最后一条SQL语句的执行计划,如下所示:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 | SYS@RAC2LHR1> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL)); PLAN_TABLE_OUTPUT ----------------------------------------------------------------------------- SQL_ID 315xan8zgvtbm, child number 0 ------------------------------------- SELECT ENAME,DNAME,LOC FROM SCOTT.EMP E,SCOTT.DEPT D WHERE E.DEPTNO = D.DEPTNO AND E.EMPNO = 7788 Plan hash value: 1674520956 ---------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ---------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 2 (100)| | | 1 | NESTED LOOPS | | 1 | 33 | 2 (0)| 00:00:01 | | 2 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 13 | 1 (0)| 00:00:01 | |* 3 | INDEX UNIQUE SCAN | PK_EMP | 1 | | 0 (0)| | | 4 | TABLE ACCESS BY INDEX ROWID| DEPT | 1 | 20 | 1 (0)| 00:00:01 | |* 5 | INDEX UNIQUE SCAN | PK_DEPT | 1 | | 0 (0)| | ---------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 3 - access("E"."EMPNO"=7788) 5 - access("E"."DEPTNO"="D"."DEPTNO") 24 rows selected. |
传递SQL_ID以及FORMAT参数给DISPLAY_CURSOR函数,并配合修饰符控制执行计划的输出,如下所示: