基本有以下几种方式：
1、通过sql_trace初始化参数
2、通过Autotrace
3、通过explain plan
4、通过dbms_xplan.display_cursor
5、通过dbms_xplan.display_awr
6、通过10046事件
1、通过explain plan 工具
12:24:00 SCOTT@ prod>explain plan for
12:24:06 2 select empno,ename,sal,deptno from emp where empno=7788;
Explained.
Elapsed: 00:00:00.22
12:24:16 SCOTT@ prod>select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------
Plan hash value: 2949544139
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 46 | 2 (0)| 00:00:01 |
| 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 |
--------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("EMPNO"=7788)
14 rows selected.
Elapsed: 00:00:01.14
2、通过DBMS_XPLAN.display_cursor查看
12:52:37 SCOTT@ prod>desc dbms_xplan
FUNCTION DISPLAY_CURSOR RETURNS DBMS_XPLAN_TYPE_TABLE
Argument Name Type In/Out Default
------------------------------ ----------------------- ------ --------
SQL_ID VARCHAR2 IN DEFAULT
CURSOR_CHILD_NO NUMBER(38) IN DEFAULT
FORMAT VARCHAR2 IN DEFAULT
如果以scott用户访问需要进行授权：
12:31:44 SYS@ prod>select * from dict where upper(table_name)='V$SESSION';
TABLE_NAME COMMENTS
------------------------------ ----------------------------------------
V$SESSION Synonym for V_$SESSION
Elapsed: 00:00:00.09
12:31:09 SYS@ prod>grant select on V_$SESSION to scott;
Grant succeeded.
Elapsed: 00:00:00.10
12:43:15 SCOTT@ prod>select * from table(dbms_xplan.display_cursor(null,null,'advanced'));
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------
User has no SELECT privilege on V$SQL_PLAN
解决权限不足：
12:42:33 SYS@ prod>grant select any table to scott;
Grant succeeded.
12:43:46 SYS@ prod>show parameter o7
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
O7_DICTIONARY_ACCESSIBILITY boolean TRUE
12:44:54 SYS@ prod>
案例：dbms_xplan.display_cursor
12:42:45 SCOTT@ prod>select empno,ename,sal,deptno from emp where empno=7788;
EMPNO ENAME SAL DEPTNO
---------- ---------- ---------- ----------
7788 SCOTT 3000 20
Elapsed: 00:00:00.08
12:43:15 SCOTT@ prod>select * from table(dbms_xplan.display_cursor(null,null,'all'));
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)