使用 EXPLAIN PLAN 获取SQL语句执行计划
来源：互联网 发布：淘宝上买书可靠吗 编辑：程序博客网 时间：2024/05/21 08:45
SQL查询语句的性能从一定程度上影响整个数据库的性能。很多情况下，数据库性能的低下差不多都是不良SQL语句所引起。而SQL语句的执行
计划则决定了SQL语句将会采用何种方式从数据库提取数据并返回给客户端，本文描述的将是如何通过EXPLAIN PLAN 获取SQL语句执行计划来获
取SQL语句的执行计划。
一、获取SQL语句执行计划的方式
1. 使用explain plan 将执行计划加载到表plan_table，然后查询该表来获取预估的执行计划
2. 查询动态性能视图v$sql_plan,v$sql_plan_statistics,v$sql_workarea 等来获取已缓存到库缓存中的真实执行计划
3. 查询自动工作量资料库(Automatic Workload Repository)或查询Statspack，即从资料库中获取执行计划
4. 启用执行计划跟踪功能，即autotrace功能
5. 使用PL/SQL Developer提供的获取执行计划方法
6. 使用Toad工具来获取执行计划
下面主要讨论使用explain plan获取执行计划的方法
二、explain plan工作实质、前提及操作方法
1. 工作实质
将SQL语句预估的执行计划加载到表plan_table,是对表plan_table 执行了DML操作，故不会执行隐式提交
可以对select,insert,update,merge,delete,create table, create index,alter index等加载执行计划到plan_table
2. 前提条件
需要先创建plan_table，创建方法：@?/rdbms/admin/utlxplan
对当前的SQL语句有执行权限以及对依赖的对象有相应操作的权限
3. 使用方法：
explain plan for select * from scott.emp where ename='SCOTT'; --未设置标记位
explain plan set statement_id='TEST' for select * from scott.emp where ename='SCOTT' --设置标记位为TEST
三、实战演习
1.环境
scott@ORCL> select * from v$version; BANNER ----------------------------------------------------------------Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
2.创建测试表演示获取执行计划
scott@ORCL> create table t as select * from all_objects where rownum<=1000; Table created. --加载创建表的执行计划(DDL 执行计划) scott@ORCL> explain plan set statement_id='T1' for create table t1 as select * from t; Explained. --使用下面的语句从plan_table 获取执行计划 col OPERATION format a25 col OPTIONS format a25 col OBJECT_NAME format a25 SELECT lpad(' ', 2 * (LEVEL - 1)) || operation operation ,options ,object_name ,position pos ,bytes ,cost FROM plan_table START WITH id = 0 AND statement_id =upper( '&input_statement_id') CONNECT BY PRIOR id = parent_id; Enter value for input_statement_id: T1 old 9: AND statement_id =upper( '&input_statement_id') new 9: AND statement_id =upper( 'T1') OPERATION OPTIONS OBJECT_NAME POS BYTES COST ------------------------- ------------------------- ------------------------- ---------- ---------- ---------- CREATE TABLE STATEMENT 8 79000 8 LOAD AS SELECT T1 1 TABLE ACCESS FULL T 1 79000 5 --创建测试表t1并收集统计信息 scott@ORCL> create table t1 nologging as select * from t; scott@ORCL> exec dbms_stats.gather_table_stats('SCOTT','T1'); --使用explain plan加载创建索引的执行计划 scott@ORCL> explain plan set statement_id='IDX' for create index i_t1 on t1(object_id); Explained. scott@ORCL> @Get_Plan Enter value for input_statement_id: IDX old 9: AND statement_id =upper( '&input_statement_id') new 9: AND statement_id =upper( 'IDX') OPERATION OPTIONS OBJECT_NAME POS BYTES COST ------------------------- ------------------------- ------------------------- ---------- ---------- ---------- CREATE INDEX STATEMENT 6 4000 6 INDEX BUILD NON UNIQUE I_T1 1 SORT CREATE INDEX 1 4000 TABLE ACCESS FULL T1 1 4000 5 scott@ORCL> CREATE INDEX i_t1 ON t1 (object_id); scott@ORCL> delete from plan_table; 3.使用自顶向下的读取方法获取执行计划
--使用explain plan加载重建索引的执行计划 scott@ORCL> explain plan set statement_id='A_IDX' for alter index i_t1 rebuild; Explained. --执行下面的语句来获的A_IDX的执行计划，其结果是从上至下来读，从最内侧往最外侧读。 SELECT LPAD(' ', 2 * (LEVEL - 1)) || LEVEL || '.' || NVL(POSITION, 0) || ' ' || OPERATION || ' ' || OPTIONS || ' ' || OBJECT_NAME || ' ' || OBJECT_TYPE || ' ' || DECODE(ID, 0, STATEMENT_ID || ' Cost = ' || POSITION) || COST || ' ' || OBJECT_NODE "Query Plan" FROM PLAN_TABLE START WITH ID = 0 AND STATEMENT_ID = UPPER('&input_statement_id') CONNECT BY PRIOR ID = PARENT_ID AND STATEMENT_ID = UPPER('&input_statement_id'); Enter value for input_statement_id: A_IDX old 8: AND STATEMENT_ID = UPPER('&input_statement_id') new 8: AND STATEMENT_ID = UPPER('A_IDX') Enter value for input_statement_id: A_IDX old 10: AND STATEMENT_ID = UPPER('&input_statement_id') new 10: AND STATEMENT_ID = UPPER('A_IDX') Query Plan --------------------------------------------------------------------------------------------- 1.2 ALTER INDEX STATEMENT A_IDX Cost = 22 2.1 INDEX BUILD NON UNIQUE I_T1 3.1 SORT CREATE INDEX 4.1 INDEX FAST FULL SCAN I_T1 INDEX 2 --使用explain plan加载查询语句的执行计划 scott@ORCL> explain plan set statement_id='QUERY' for 2 select ename,dname 3 from emp join dept 4 on emp.deptno=dept.deptno 5 where dept.deptno=30; Explained. scott@ORCL> @Get_Plan2 Query Plan -------------------------------------------------------------------------------------------- 1.4 SELECT STATEMENT QUERY Cost = 44 2.1 NESTED LOOPS 4 3.1 TABLE ACCESS BY INDEX ROWID DEPT TABLE 1 4.1 INDEX UNIQUE SCAN PK_DEPT INDEX (UNIQUE) 0 3.2 TABLE ACCESS FULL EMP TABLE 3 上面的例子的读取方法：
执行4.1的索引唯一扫描
将4.1的结果集返回给3.1
执行3.2的全表扫描
将3.1和3.2步骤的结果集返回给2.1
执行2.1的嵌套循环
返回最终结果集
注意嵌套循环的查询方法
Oracle 从第一个行源中读取第一行，然后和第二个行源中的所有记录行进行比对，所有匹配的记录放在结果集中，然后Oracle 将读第一
个行源中的下一行。依次类推，直到第一行源中的所有行处理完毕。
4.使用构建树方式查看执行计划
scott@ORCL> delete from plan_table; --使用explian plan加载SQL查询执行计划 scott@ORCL> explain plan set statement_id='QUERY2' for 2 select ename,dname 3 from emp join dept 4 on emp.deptno=dept.deptno 5 where emp.empno=7788; Explained. --使用下面的SQl查询来生成构建树 col operation format a30 col options format a20 col "OBJECT NAME" format a25 col order format a10 col opt format a15 SELECT LPAD(' ', 2 * (LEVEL - 1)) || operation "OPERATION", options "OPTIONS", DECODE(TO_CHAR(id), '0', 'COST = ' || NVL(TO_CHAR(position), 'n/a'), object_name) "OBJECT NAME", id || '-' || NVL(parent_id, 0) || '-' || NVL(position, 0) "ORDER", SUBSTR(optimizer, 1, 6) "OPT" FROM plan_table START WITH id = 0 AND statement_id = UPPER('&input_statement_id') CONNECT BY PRIOR id = parent_id AND statement_id = UPPER('&input_statement_id'); OPERATION OPTIONS OBJECT NAME ORDER OPT ------------------------------ -------------------- ------------------------- ---------- --------------- SELECT STATEMENT COST = 2 0-0-2 ALL_RO NESTED LOOPS 1-0-1 TABLE ACCESS BY INDEX ROWID EMP 2-1-1 ANALYZ INDEX UNIQUE SCAN PK_EMP 3-2-1 ANALYZ TABLE ACCESS BY INDEX ROWID DEPT 4-1-2 ANALYZ INDEX UNIQUE SCAN PK_DEPT 5-4-1 ANALYZ
查询结果中的order列与opt列
order
order列的指名了ID，父ID，以及执行计划中这一步骤的位置。
ID列标识了这个步骤，但并没有说明执行的顺序
父ID表明了这个步骤中的父步骤
位置信息说明了父ID相同的子操作的执行顺序
opt
说明当前优化器使用的模式
分析
首先会从步骤3开始执行，步骤3通过索引唯一扫描PK_EMP将得到的结果集返回给父步骤2
步骤2根据上一子步骤3得到的rowid访问表EMP并将结果集返回给父步骤1
对于步骤2检索到的每一行数据，步骤1会将deptno传递给步骤5
步骤5根据得到的deptno执行索引唯一扫描并将结果集返回给步骤4
步骤4根据步骤5得到的rowid 访问表dept，并将结果集返回给父步骤1
对于步骤3中剩余的行依次按上述方式将所有结果集返回给步骤1
步骤1将获得的最终结果集返回给步骤0，SQL完成查询
根据查询返回的结果来构建执行计划树
从ID为1的列开始，作为根节点
寻找所有父ID为1的所有子ID，如本例为2和4，将其纳入树中
分别寻找以2和4为父ID的所有子ID，将其纳入树中
如此循环直到所有的ID没有父ID
---------------
NESTED LOOP (1)
---------------
- -
- -
- -
--------- ----------
EMP (2) DEPT(4)
--------- ----------
- -
- -
--------- ----------
PK_EMP(3) PK_DEPT(5)
--------- ----------
5.通过Oracle 自带的SQL语句执行计划
可以通过Oracle提供的SQl语句来获得当前会话最后一条SQL语句的执行计划
utlxpls.sql -->用于查看串行执行计划
utlxplp.sql -->用于查看并行执行计划
scott@ORCL> @?/rdbms/admin/utlxpls.sql --获得当前session plan_table 最后一条SQL语句的执行计划 PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------------------------- Plan hash value: 2385808155 ---------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ---------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 28 | 2 (0)| 00:00:01 | | 1 | NESTED LOOPS | | 1 | 28 | 2 (0)| 00:00:01 | |* 2 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 14 | 1 (0)| 00:00:01 | |* 3 | INDEX UNIQUE SCAN | PK_EMP | 1 | | 0 (0)| 00:00:01 | | 4 | TABLE ACCESS BY INDEX ROWID| DEPT | 4 | 56 | 1 (0)| 00:00:01 | |* 5 | INDEX UNIQUE SCAN | PK_DEPT | 1 | | 0 (0)| 00:00:01 | ---------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - filter("EMP"."DEPTNO" IS NOT NULL) 3 - access("EMP"."EMPNO"=7788) 5 - access("EMP"."DEPTNO"="DEPT"."DEPTNO") 19 rows selected. --加载并行SQL的执行计划 scott@ORCL> explain plan for select /*+ parallel(t4,2) */ * from t4; Explained. scott@ORCL> @?/rdbms/admin/utlxplp.sql PLAN_TABLE_OUTPUT ---------------------------------------------------------------------------------------------------------------- Plan hash value: 128826497 -------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib | -------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 400K| 7817K| 183 (4)| 00:00:03 | | | | | 1 | PX COORDINATOR | | | | | | | | | | 2 | PX SEND QC (RANDOM)| :TQ10000 | 400K| 7817K| 183 (4)| 00:00:03 | Q1,00 | P->S | QC (RAND) | | 3 | PX BLOCK ITERATOR | | 400K| 7817K| 183 (4)| 00:00:03 | Q1,00 | PCWC | | | 4 | TABLE ACCESS FULL| T4 | 400K| 7817K| 183 (4)| 00:00:03 | Q1,00 | PCWP | | -------------------------------------------------------------------------------------------------------------- - 使用 EXPLAIN PLAN 获取SQL语句执行计划
- 使用 EXPLAIN PLAN 获取SQL语句执行计划
- 使用EXPLAIN PLAN 获取SQL语句执行计划
- 使用 EXPLAIN PLAN 获取SQL语句执行计划
- 使用 EXPLAIN PLAN 获取SQL语句执行计划
- 使用 EXPLAIN PLAN 获取SQL语句执行计划
- explain plan获取sql的执行计划
- 【Explain Plan】查看SQL的执行计划
- 【Explain Plan】查看SQL的执行计划
- 使用EXPLAIN PLAN获取SQL语句…
- SAP HANA SQL执行计划(SAP HANA SQL explain plan)
- SAP HANA SQL执行计划(SAP HANA SQL explain plan)
- Oracle Explain Plan 执行计划
- TOAD中查看SQL的执行计划（Explain Plan）
- 用 pl/sql 安装 oracle explain plan(执行计划)
- Oracle 执行计划（Explain Plan） 说明
- Oracle 执行计划（Explain Plan） 说明
- oracle 执行计划（explain plan）说明
- 用MySQL实现组内随机采样
- 搭建docker+swoole+php7 的环境
- 【常见问题】web开发中浏览器缓存问题
- java异常处理
- 关于Error:Execution failed for task ':Disentanglement:transformNative_libsWithStripDebugSymbolForDebug
- 使用 EXPLAIN PLAN 获取SQL语句执行计划
- Maven构建项目报错解决办法
- python3 编码与解码问题
- 用python测试连接rabiitmq的简单通讯
- 高通Camera 驱动调试要点（一）
- 每日一个linux命令（5）：lsof命令
- 【Python】Python 中if (__name__ == '__main__') 如何正确理解?
- Vue + i18n实现国际化
- U盘无法识别读取
