查看执行计划（一）

查看执行计划的几种方式：
1）explain plan 命令
语法是explain plan for + 目标sql
select * from table(dbms_xplan.display);
SQL> explain plan for select empno,ename,dname from scott.emp t11,scott.dept t12 where t11.deptno=t12.deptno;


已解释。
SQL> set linesize 200
SQL> select * from table(dbms_xplan.display);


PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------------------


Plan hash value: 844388907


----------------------------------------------------------------------------------------
| Id  | Operation                    | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |         |    14 |   364 |     6  (17)| 00:00:01 |
|   1 |  MERGE JOIN                  |         |    14 |   364 |     6  (17)| 00:00:01 |
|   2 |   TABLE ACCESS BY INDEX ROWID| DEPT    |     4 |    52 |     2   (0)| 00:00:01 |
|   3 |    INDEX FULL SCAN           | PK_DEPT |     4 |       |     1   (0)| 00:00:01 |
|*  4 |   SORT JOIN                  |         |    14 |   182 |     4  (25)| 00:00:01 |
|   5 |    TABLE ACCESS FULL         | EMP     |    14 |   182 |     3   (0)| 00:00:01 |


PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------------------


----------------------------------------------------------------------------------------


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


   4 - access("T11"."DEPTNO"="T12"."DEPTNO")
       filter("T11"."DEPTNO"="T12"."DEPTNO")


已选择18行。


这种方式和在PL/SQL DEVELOPER当中使用F5得到执行的执行计划一模一样。


2）DBMS_XPLAN包
SQL> select empno,ename,dname from scott.emp t11,scott.dept t12 where t11.deptno=t12.deptno;


     EMPNO ENAME      DNAME
---------- ---------- --------------
      7782 CLARK      ACCOUNTING
      7839 KING       ACCOUNTING
      7934 MILLER     ACCOUNTING
      7566 JONES      RESEARCH
      7902 FORD       RESEARCH
      7876 ADAMS      RESEARCH
      7369 SMITH      RESEARCH
      7788 SCOTT      RESEARCH
      7521 WARD       SALES
      7844 TURNER     SALES
      7499 ALLEN      SALES


     EMPNO ENAME      DNAME
---------- ---------- --------------
      7900 JAMES      SALES
      7698 BLAKE      SALES
      7654 MARTIN     SALES


已选择14行。


SQL> select * from table(dbms_xplan.display_cursor(null,null,'advanced'));


PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------


SQL_ID  fvp57hhd1vfmp, child number 0
-------------------------------------
select empno,ename,dname from scott.emp t11,scott.dept t12 where
t11.deptno=t12.deptno


Plan hash value: 844388907


----------------------------------------------------------------------------------------
| Id  | Operation                    | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |         |       |       |     6 (100)|          |


PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------


|   1 |  MERGE JOIN                  |         |    14 |   364 |     6  (17)| 00:00:01 |
|   2 |   TABLE ACCESS BY INDEX ROWID| DEPT    |     4 |    52 |     2   (0)| 00:00:01 |
|   3 |    INDEX FULL SCAN           | PK_DEPT |     4 |       |     1   (0)| 00:00:01 |
|*  4 |   SORT JOIN                  |         |    14 |   182 |     4  (25)| 00:00:01 |
|   5 |    TABLE ACCESS FULL         | EMP     |    14 |   182 |     3   (0)| 00:00:01 |
----------------------------------------------------------------------------------------


Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------


   1 - SEL$1


PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------


   2 - SEL$1 / T12@SEL$1
   3 - SEL$1 / T12@SEL$1
   5 - SEL$1 / T11@SEL$1


Outline Data
-------------


  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('11.2.0.1')


PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------


      DB_VERSION('11.2.0.1')
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$1")
      INDEX(@"SEL$1" "T12"@"SEL$1" ("DEPT"."DEPTNO"))
      FULL(@"SEL$1" "T11"@"SEL$1")
      LEADING(@"SEL$1" "T12"@"SEL$1" "T11"@"SEL$1")
      USE_MERGE(@"SEL$1" "T11"@"SEL$1")
      END_OUTLINE_DATA
  */


Predicate Information (identified by operation id):


PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------


---------------------------------------------------


   4 - access("T11"."DEPTNO"="T12"."DEPTNO")
       filter("T11"."DEPTNO"="T12"."DEPTNO")


Column Projection Information (identified by operation id):
-----------------------------------------------------------


   1 - "DNAME"[VARCHAR2,14], "EMPNO"[NUMBER,22], "ENAME"[VARCHAR2,10]
   2 - "T12"."DEPTNO"[NUMBER,22], "DNAME"[VARCHAR2,14]
   3 - "T12".ROWID[ROWID,10], "T12"."DEPTNO"[NUMBER,22]


PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------


   4 - (#keys=1) "T11"."DEPTNO"[NUMBER,22], "EMPNO"[NUMBER,22],
       "ENAME"[VARCHAR2,10]
   5 - "EMPNO"[NUMBER,22], "ENAME"[VARCHAR2,10], "T11"."DEPTNO"[NUMBER,22]




已选择59行。


--这种方法在SQLPLUS中查看刚刚执行过的SQL的执行计划。
--dbms_xplan.display_cursor传入的前两个参数的值均为null,第三个参数是"advanced"，第三个参数也可以是"all",
”all“得到的显示结果，少了"Outline data"部分的内容。




3） select * from table(dbms_xplan.display_cursor('sql_id/hash_value',child_cursor_number,'advanced');
SQL> select sql_text,sql_id,hash_value,child_number from v$sql where sql_text like '%select empno,ename%';


SQL_TEXT
-----------------------------------------------------------------------------------------------------------


SQL_ID        HASH_VALUE CHILD_NUMBER
------------- ---------- ------------
select sql_text,sql_id,hash_value,child_number from v$sql where sql_text like '%select empno,ename%'
b43838yvpqmdh 3076214192            0


select empno,ename,dname from scott.emp t11,scott.dept t12 where t11.deptno=t12.deptno
fvp57hhd1vfmp  438155893            0




SQL> select * from table(dbms_xplan.display_cursor('fvp57hhd1vfmp',0,'advanced'));


PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------


SQL_ID  fvp57hhd1vfmp, child number 0
-------------------------------------
select empno,ename,dname from scott.emp t11,scott.dept t12 where
t11.deptno=t12.deptno


Plan hash value: 844388907


----------------------------------------------------------------------------------------
| Id  | Operation                    | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |         |       |       |     6 (100)|          |


PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------


|   1 |  MERGE JOIN                  |         |    14 |   364 |     6  (17)| 00:00:01 |
|   2 |   TABLE ACCESS BY INDEX ROWID| DEPT    |     4 |    52 |     2   (0)| 00:00:01 |
|   3 |    INDEX FULL SCAN           | PK_DEPT |     4 |       |     1   (0)| 00:00:01 |
|*  4 |   SORT JOIN                  |         |    14 |   182 |     4  (25)| 00:00:01 |
|   5 |    TABLE ACCESS FULL         | EMP     |    14 |   182 |     3   (0)| 00:00:01 |
----------------------------------------------------------------------------------------


Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------


   1 - SEL$1


PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------


   2 - SEL$1 / T12@SEL$1
   3 - SEL$1 / T12@SEL$1
   5 - SEL$1 / T11@SEL$1


Outline Data
-------------


  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('11.2.0.1')


PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------


      DB_VERSION('11.2.0.1')
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$1")
      INDEX(@"SEL$1" "T12"@"SEL$1" ("DEPT"."DEPTNO"))
      FULL(@"SEL$1" "T11"@"SEL$1")
      LEADING(@"SEL$1" "T12"@"SEL$1" "T11"@"SEL$1")
      USE_MERGE(@"SEL$1" "T11"@"SEL$1")
      END_OUTLINE_DATA
  */


Predicate Information (identified by operation id):


PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------


---------------------------------------------------


   4 - access("T11"."DEPTNO"="T12"."DEPTNO")
       filter("T11"."DEPTNO"="T12"."DEPTNO")


Column Projection Information (identified by operation id):
-----------------------------------------------------------


   1 - "DNAME"[VARCHAR2,14], "EMPNO"[NUMBER,22], "ENAME"[VARCHAR2,10]
   2 - "T12"."DEPTNO"[NUMBER,22], "DNAME"[VARCHAR2,14]
   3 - "T12".ROWID[ROWID,10], "T12"."DEPTNO"[NUMBER,22]


PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------


   4 - (#keys=1) "T11"."DEPTNO"[NUMBER,22], "EMPNO"[NUMBER,22],
       "ENAME"[VARCHAR2,10]
   5 - "EMPNO"[NUMBER,22], "ENAME"[VARCHAR2,10], "T11"."DEPTNO"[NUMBER,22]




已选择59行。


4）select * from table(dbms_xplan.display_awr('sql_id'))";--它用于查询指定SQL的所有历史执行计划。
--使用方法2和3能够得到sql执行计划的前提条件是该执行计划还在共享池中，而如果该SQL的执行计划已经被刷出共享池，那么只要该SQL的执行计划被ORACLE采集到AWR Repository中，
就可以用该方法来查看。


SQL> select sql_text,sql_id,version_count,executions from v$sqlarea where sql_text like 'select count(*) from t1%'


SQL_TEXT
--------------------------------------------------------------------------------
SQL_ID        VERSION_COUNT EXECUTIONS
------------- ------------- ----------
select count(*) from t1
5bc0v4my7dvr5             1          3




SQL> exec dbms_workload_repository.create_snapshot;


PL/SQL 过程已成功完成。


SQL> alter system flush shared_pool;


系统已更改。


SQL> select *　from table(dbms_xplan.display_cursor('5bc0v4my7dvr5',0,'advanced'));


PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID: 5bc0v4my7dvr5, child number: 0 cannot be found




SQL> select *　from table(dbms_xplan.display_cursor('5bc0v4my7dvr5',1,'advanced'));


PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID: 5bc0v4my7dvr5, child number: 1 cannot be found




SQL> select *　from table(dbms_xplan.display_awr('5bc0v4my7dvr5'));


PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID 5bc0v4my7dvr5
--------------------
select count(*) from t1


Plan hash value: 3724264953


-------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Cost (%CPU)| Time     |
-------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |       |     2 (100)|          |
|   1 |  SORT AGGREGATE    |      |     1 |            |          |


PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
|   2 |   TABLE ACCESS FULL| T1   |     2 |     2   (0)| 00:00:01 |
-------------------------------------------------------------------


Note
-----
   - dynamic sampling used for this statement (level=2)




已选择18行。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/26084062/viewspace-1083834/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/26084062/viewspace-1083834/