Oracle 生成和显示执行计划的方法
Oracle 生成和显示执行计划的方法
生成执行计划
一条合法的语句在执行之后，就会在内存中至少产生一条执行计划，可以从视图v$sql_plan查询。每一条执行计划对于一个游标。一条语句生产的第一个游标的CHILD_NUMBER（即v$sql_plan中的CHILD_NUMBER）为0，同一条sql语句可能因为环境或版本等其他因素不同而产生不同的执行计划，也就是说一条sql可能有多个CHILD_NUMBER。
除执行过的sql会在内存中生成执行计划外，还可以通过explain plan让优化器对sql语句进行解析，生成查询计划。执行explain plan命令后，oracle会将解释生成的执行计划插入sys.plan_table$（10g之前，表名为plan_table；10g之后，通过公共同义词plan_table指向sys.plan_table$）中。explain plan产生的执行计划不会在语句执行时重用，而是以类似explain plan for<SQL>的形式在缓存中。
显示执行计划
通过DBMS_XPLAN包显示执行计划（推荐）
DBMS_XPLAN含有5个函数用于输出格式化的执行计划，如下：
display：显示explain plan命令解释的执行计划
display_cursor：显示内存中的执行计划
display_awr：awr历史数据中的执行计划
display_sqlset：SQL优化集中语句的执行计划
display_sql_plan_baseline：SQL执行计划基线
display的用法
语法：
DBMS_XPLAN.DISPLAY( table_name IN VARCHAR2 DEFAULT 'PLAN_TABLE', statement_id IN VARCHAR2 DEFAULT NULL, format IN VARCHAR2 DEFAULT 'TYPICAL', filter_preds IN VARCHAR2 DEFAULT NULL);参数：
Parameter Description
table_name
存储查询计划的表名，默认值为PLAN_TABLE
statement_id
要显示查询计划的SQL语句的ID，如果为空，就会获取最近一条被解释的语句
format
输出格式，有以下几种预定义的格式可选择，还可以自定义（这里不多说）:
BASIC:基本格式，内容最少.
TYPICAL: 典型格式，通常够用，默认格式
SERIAL: 串行执行格式，这种格式和典型格式输出内容基本一致，只是对于并行查询，不会输出相关的并行内容
ALL: 完全格式，输出内容相对完整
filter_preds
DBMS_XPLAN.DISPLAY_CURSOR( sql_id IN VARCHAR2 DEFAULT NULL, child_number IN NUMBER DEFAULT NULL, format IN VARCHAR2 DEFAULT 'TYPICAL');参数：
Parameter Description
sql_id
要显示执行计划的sql语句的ID
child_number
子游标序号
format
输出格式
同于display输出格式
display_cursor 默认是获得由本session执行的之后一条语句的执行计划:SELECT ename FROM emp e, dept d WHERE e.deptno = d.deptno AND e.empno=7369;ENAME----------SMITHSET PAGESIZE 0SELECT * FROM table(DBMS_XPLAN.DISPLAY_CURSOR);输出结果如下：Plan hash value: 3693697075, SQL hash value: 2096952573, child number: 0------------------------------SELECT ename FROM emp e, dept d WHERE e.deptno = d.deptnoAND e.empno=7369---------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |---------------------------------------| 0 | SELECT STATEMENT | | | | | ||* 1 | HASH JOIN | | 1 | 16 | 6 (34)| 00:00:01 ||* 2 | TABLE ACCESS FULL| EMP | 1 | 13 | 3 (34)| 00:00:01 || 3 | TABLE ACCESS FULL| DEPT | 4 | 12 | 3 (34)| 00:00:01 |---------------------------------------Predicate Information (identified by operation id):--------------------------------------------------- 1 - access("E"."DEPTNO"="D"."DEPTNO") 2 - filter("E"."EMPNO"=7369)21 rows selected.还可以用下面的方式获得SQL_ID和CHILD_NUMBERSELECT /* TOTO */ ename, dname FROM dept d join emp e USING (deptno);用上面的语句得到 sql_id和child_number:SELECT sql_id, child_numberFROM v$sql WHERE sql_text LIKE '%TOTO%';SQL_ID CHILD_NUMBER---------- -----------------------------gwp663cqh5qbf 0SELECT * FROM table(DBMS_XPLAN.DISPLAY_CURSOR(('gwp663cqh5qbf',0));Plan hash value: 3693697075, SQL ID: gwp663cqh5qbf, child number: 0--------------------SELECT /* TOTO */ ename, dname FROM dept d JOIN emp e USING (deptno);----------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |----------------------------------------| 0 | SELECT STATEMENT | | | | 7 (100)| || 1 | SORT GROUP BY | | 4 | 64 | 7 (43)| 00:00:01 ||* 2 | HASH JOIN | | 14 | 224 | 6 (34)| 00:00:01 || 3 | TABLE ACCESS FULL| DEPT | 4 | 44 | 3 (34)| 00:00:01 || 4 | TABLE ACCESS FULL| EMP | 14 | 70 | 3 (34)| 00:00:01 |----------------------------------------Predicate Information (identified by operation id):--------------------------------------------------- 2 - access("E"."DEPTNO"="D"."DEPTNO")上边的两步还可以用下面的方法一次性得到SELECT t.*FROM v$sql s, table(DBMS_XPLAN.DISPLAY_CURSOR(s.sql_id, s.child_number)) t WHERE sql_text LIKE '%TOTO%';