oracle 查看执行计划方法（SQL developer，sqlplus）

来源：互联网发布：淘宝入驻费用多少钱编辑：程序博客网时间：2024/04/28 19:58

一、什么是执行计划（explain plan）
执行计划：一条查询语句在ORACLE中的执行过程或访问路径的描述。

二.执行计划查看的几种方法

1.如果用的是oracle SQL developer的话，你可以按F10

2.use DBMS_XPLAN （在oracle SQL developer和 sqlplus上都可以）
The DBMS_XPLAN package provides an easy way to display the output of the EXPLAIN PLAN command in several, predefined formats
example：Displaying a Plan Table Using DBMS_XPLAN.DISPLAY
Execute an explain plan command on a SELECT statement:
EXPLAIN PLAN FOR
SELECT * FROM emp e, dept d
WHERE e.deptno = d.deptno
AND e.ename='benoit';

Display the plan using the DBMS_XPLAN.DISPLAY table function
SELECT * FROM table(DBMS_XPLAN.DISPLAY);

This query produces the following output:
Plan hash value: 3693697075
-
---------------------------------------------------------------------------
|
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-
---------------------------------------------------------------------------
|
| 0 | SELECT STATEMENT | | 1 | 57 | 6 (34)| 00:00:01 |
|
|* 1 | HASH JOIN | | 1 | 57 | 6 (34)| 00:00:01 |
|
|* 2 | TABLE ACCESS FULL| EMP | 1 | 37 | 3 (34)| 00:00:01 |
|
| 3 | TABLE ACCESS FULL| DEPT | 4 | 80 | 3 (34)| 00:00:01 |
-
---------------------------------------------------------------------------

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

1 - access("E"."DEPTNO"="D"."DEPTNO")

2 - filter("E"."ENAME"='benoit')

15 rows selected.
当然，你也可以用 Displaying a Cursor Execution Plan Using DBMS_XPLAN.DISPLAY_CURSOR

Displaying a cursor Execution Plan using DBMS_XPLAN.DISPLAY_CURSOR

3.在sqlplus上set autotrace on to show query statistics as well as an exection plan. You can also view only the execution plan
set autotrace on

for example：
SQL> set autotrace on explain
SQL> select * from cdr_unbilled;

CDR_DATA_PARTIT MSG_ID MSG_ID2 MSG_ID_SERV SPLIT_ROW_NUM ACCOUNT_NO SUBSCR_NO SUBSCR_NO_RESETS TRANS_DT
--------------- ---------- ---------- ----------- ------------- ---------- ---------- ---------------- ---------------
RATE_DT
---------------
12-JAN-16 1 1 3 0 3 2 0 01-JAN-01
01-FEB-01

12-JAN-16 2 6 3 0 3 2 0 01-JAN-01
01-FEB-01

12-JAN-16 3 6 3 0 3 2 0 01-JAN-01
01-FEB-01

12-JAN-16 4 6 3 0 3 2 0 02-JAN-01
01-FEB-01

Execution Plan
----------------------------------------------------------
Plan hash value: 532673897

----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 4 | 172 | 3 (0)| 00:00:01 |
| 1 | TABLE ACCESS FULL| CDR_UNBILLED | 4 | 172 | 3 (0)| 00:00:01 |
----------------------------------------------------------------------------------

oracle execute plan document reference：
officical explain-plan document

http://oracle.readthedocs.org/en/latest/sql/plans/explain-plan.html

再补充两种：
参考：http://bfc99.blog.51cto.com/265386/1343594
方法3：查看v$sql_plan表
通过SQL语句的SQL_ID和子游标号，可以在V$SQL_PLAN表中查看到该SQL语句的执行计划。
例如：执行了语句“select * from t1;',先通过v$sqlarea和v$sql找到该语句的SQL_ID和子游标号。
SQL> select sql_id,sql_text from v$sqlarea where sql_text like '%from t1%';
SQL_ID SQL_TEXT
------------- ----------------------------------------------------------------------------------------------------
27uhu2q2xuu7r select * from t1
bf45pybkumcx5 select sql_id,sql_text from v$sqlarea where sql_text like '%from t1%'
SQL> select sql_id,child_number,sql_text from v$sql where sql_id='27uhu2q2xuu7r';
SQL_ID CHILD_NUMBER SQL_TEXT
------------- ------------ ----------------------------------------------------------------------------------------------------
27uhu2q2xuu7r 0 select * from t1
--通过以上两条查询语句，查得目标SQL语句的SQL_ID为 “27uhu2q2xuu7r”，子游标号为“0”.
SQL> select timestamp,operation,options,object_name,cost,id,parent_id from v$sql_plan where sql_id='27uhu2q2xuu7r' and child_number=0;
TIMESTAMP OPERATION OPTIONS OBJECT_NAME COST IDPARENT_ID
----------------- -------------------- ---------- ------------------------------ ---------- ---------- ----------
20131221 20:49:14 SELECT STATEMENT 3 0
20131221 20:49:14 TABLE ACCESS FULL T1 3 1 0

方法4：查到指定语句的SQL_ID和子游标号后（查找方法请见方法3），通过DBMS_XPLAN包进行查看。
例如：假设目标语句的SQL_ID和子游标号同方法3.
SQL> select * from table(dbms_xplan.display_cursor('27uhu2q2xuu7r',0,'TYPICAL'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID27uhu2q2xuu7r, child number 0
-------------------------------------
select * from t1
Plan hash value: 3617692013
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 3 (100)| |
| 1 | TABLE ACCESS FULL| T1 | 6 | 96 | 3 (0)| 00:00:01 |
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------
13 rows selected.

0 0