-- dbms_xplan.display_cursor传入的前两个参数值均为 null,null第三个参数是 "advanced"第三个参 数也可以是 "all"得到的显示结果，少了 "Outline data"部分的内容 。
sql>select sql_text,sql_id,hash_value,child_number from v$sql
2* where sql_text like 'select empno,ename,sal%'
SQL_TEXT SQL_ID HASH_VALUE CHILD_NUMBER
-------------------------------------------------- ------------- ---------- ------------
select empno,ename,sal,deptno from emp where empno bqz9ujgnn4jzu 3913435130 0
=7788
Elapsed: 00:00:00.04
13:00:25 SCOTT@ prod>select * from table(dbms_xplan.display_cursor('bqz9ujgnn4jzu',0,'advanced'));
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------
SQL_ID bqz9ujgnn4jzu, child number 0
-------------------------------------
select empno,ename,sal,deptno from emp where empno=7788
Plan hash value: 2949544139
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 2 (100)| |
| 1 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 46 | 2 (0)| 00:00:01 |
|* 2 | INDEX UNIQUE SCAN | PK_EMP | 1 | | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------
1 - SEL$1 / EMP@SEL$1
2 - SEL$1 / EMP@SEL$1
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('11.2.0.1')
DB_VERSION('11.2.0.1')
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")
INDEX_RS_ASC(@"SEL$1" "EMP"@"SEL$1" ("EMP"."EMPNO"))
END_OUTLINE_DATA
*/
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("EMPNO"=7788)
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - "EMPNO"[NUMBER,22], "ENAME"[VARCHAR2,10], "SAL"[NUMBER,22],
"DEPTNO"[NUMBER,22]
2 - "EMP".ROWID[ROWID,10], "EMPNO"[NUMBER,22]
46 rows selected.
Elapsed: 00:00:00.14