一、如何查看执行计划
前四种方法使用得比较普遍
1.1 explain plan 命令
? ? ? 习惯使用PL/SQL Developer的人都知道,按下快捷键F5后就可以显示目标SQL的执行计划,实际上,PL/SQL Developer就调用了explain plan命令,快捷键F5只不过是在explain plan命令上的一层封装而已。
? ? ? explain plan命令的语法是依次执行如下两条命令:
? ? ? 先使用explain plan命令对目标SQL做explain,再使用“select * from table(dbms_xplan.display)”查看上述使用explain plan命令后得到的执行计划。
explain plan for select empno,ename,dname fromscott.emp,scott.dept where emp.deptno=dept.deptno;
select * from table(dbms_xplan.display);
? ? ? explain plan 命令到底做了什么事情呢?在Oracle 10g 及其以上的版本里,如果我们对目标SQL执行explain plan 命令,则Oracle就将解析目标SQL所产生的执行计划的具体执行步骤写入PLAN_TABLE$,随后执行“select * from table(dbms_xplan.display)”只是从PLAN_TABLE$中将这些具体执行步骤以格式化的方式显示出来。PLAN_TABLES$是一个ON COMMIT PRESERVE ROWS的GLOBALTEMPORARY TABLE,所以这里Oracle可以做到各个的Session只能看到自己执行的SQL所产生的执行计划,并且各个Session往PLAN_TABLE$写入执行计划的过程互不干扰。
1.2 DBMS_XPLAN包
使用DBMS_XPLAN包中的方法是在Oracle数据库中得到目标SQL的执行计划的第二种方法。针对不同的应用场景,你可以选择如下四种方法中的一种:
方法1是需要与explain plan命令配合使用,上节已经介绍过。
方法2用于在SQLPLUS中查看刚刚执行过的SQL的执行计划。这里针对方法DBMS_XPLAN.DISPLAY_CURSOR所传入的第一个和第二个参数的值均为null,第三个参数值是“advanced”,第三个输入参数的值也可以是“all”,只不过用“advanced”后的显示结果会比“all”显示的结果更详细一些。
set linesize 800 pagesize 900
col plan_table_output for a200
select empno,ename,dname from scott.emp,scott.deptwhere emp.deptno=dept.deptno;
select * fromtable(dbms_xplan.display_cursor(null,null,'advanced'));
select empno,ename,dname from scott.emp,scott.deptwhere emp.deptno=dept.deptno;
select * fromtable(dbms_xplan.display_cursor(null,null,'all'));
“all”得到的结果与“advanced”的显示结果相比,少???“Outline Data”部分的内容。
方法3用于查看指定SQL的执行计划。这里针对方法DBMS_XPLAN.DISPLAY_CURSOR所传入的第一个参数的值是指定SQL的SQL ID或者SQL HASH VALUE,第二个参数的值是要查看的执行计划所在的Chile Cursor Number,第三个参数已经在介绍方法2时已经提到过,这个参数值一般都用“advanced”。
selectsql_text,sql_id,hash_value,child_number from v$sql where sql_text like 'selectempno,ename%';
SQL_TEXT? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ?SQL_ID? ? ? ? ? ? HASH_VALUE CHILD_NUMBER
-------------------------------------------------------------------------------? ------------- ---------- -------------
selectempno,ename,dname from scott.emp,scott.dept where emp.deptno=dept.deptno ? ? 3yfu3wh150aqt? ? 38808281? ? ? ? ? 0
sql_id为3yfu3wh150aqt,SQL HASH_VALUE为38808281,对应的ChildCursor Number为0。
本质上SQL ID和SQL HASH_VALUE是一回事,它们是可以互相转换的,这也是方法DBMS_XPLAN.DISPLAY_CURSOR所传入的第一个参数的值可以是SQL ID,也可以是SQLHASH_VALUE的原因。
selectlower(trim('3yfu3wh150aqt')) sql_id,
trunc(mod(sum((instr('0123456789abcdfghjkmnpqrstuvwxyz',
substr(lower(trim('3yfu3wh150aqt')),level,1))-1)*power(32,length(trim('3yfu3wh150aqt'))-level)),
power(2,32)))hash_value
fromdual
connectby level<=length(trim('3yfu3wh150aqt'));
?
SQL_ID? ? ? ? ? ? ? ? HASH_VALUE
-------------------------
3yfu3wh150aqt? ? ? ? ? 38808281
从上述结果可以看到,目标SQL的SQL ID(3yfu3wh150aqt)经过运算后得到的值就是该SQL的SQL HASH VALUE(38808281)。
只要目标SQL的执行计划所在的Child Cursor还没有被age out出Shard Pool,就可以使用方法3查看该SQL的执行计划
select* from table(dbms_xplan.display_cursor('3yfu3wh150aqt',0,'advanced'));
方法4 用于查看指定SQL的所有历史执行计划。
? ? ? 使用方法2、3能够显示目标SQL执行计划的前提条件是该SQL的执行计划还在Shared Pool中,而如果该SQL的执行计划已经被age out出SharedPool,那么只要该SQL的执行计划被Oracle采集到AWR Repository中,就可以使用方法4来查看该SQL的所有历史执行计划。
? ? ? 用DBMS_XPLAN.DISPLAY_AWR和之前用DBMS_XPLAN.DISPLAY_CURSOR显示的执行计划相比,有一个非常不好的地方——就是用DISPLAY_AWR显示的执行计划中看不到执行步骤对应的谓词条件。根本的原因是Oracl