测试1。。。查看已经运行完成的sql的执行计划
--创建测试表t2
SQL> create table t2 as select * from all_tables where 1=2;

Table created
做任意DML操作
SQL> insert into t2 select * from all_tables;

4688 rows inserted
SQL> commit;

Commit complete
SQL> update t2 set t2.owner = 'YZM' where rownum <=10; 10 rows updated SQL> commit;

Commit complete
select * from t2 where t2.owner = 'YZM';
--查找运行的sql的sql_id
SQL> select * from (select sql_text, sql_id,t.FIRST_LOAD_TIME
2 from v$sqlarea t order by t.FIRST_LOAD_TIME desc)
3 where rownum<=10 4 ; SQL_TEXT SQL_ID FIRST_LOAD_TIME -------------------------------------------------------------------------------- ------------- ---------------------------------------------------------------------------- SELECT /* OPT_DYN_SAMP */ /*+ ALL_ROWS IGNORE_WHERE_CLAUSE NO_PARALLEL(SAMPLESUB 5g5hh5331p9yr 2014-04-30/19:42:39 select * from t2 where t2.owner = 'YZM' 5cqmg9syj22s0 2014-04-30/19:42:39 update t2 set t2.owner = 'YZM' where rownum <=10 f33z1tvz2ykkb 2014-04-30/19:42:24 insert into WRI$_DBU_CPU_USAGE(dbid, version, timestamp, b84gb6u21n480 2014-04-30/19:42:14 update WRI$_DBU_CPU_USAGE_SAMPLE set last_sample_date = :bind1, last_sample_ 8mkxm7ur07za0 2014-04-30/19:42:14 select count(FA#) from SYS_FBA_TRACKEDTABLES where OBJ# = 79480 and DROPSCN = 0 43qxhrh2xpnzk 2014-04-30/19:40:48 select * from table(dbms_xplan.display_cursor('ffch5mjzrhzky')) 2ab3dnbc5dn55 2014-04-30/19:38:02 SELECT /*+ opt_param('parallel_execution_enabled', 'false') */ 80hn4z6ntjz13 2014-04-30/19:38:02 select /* EXEC_FROM_DBMS_XPLAN */ case when upper(sql_text) like '%DBMS_XPLAN%' c8gdf450u1t8b 2014-04-30/19:38:02 select * from (select sql_text, sql_id,t.FIRST_LOAD_TIME from v$sqlarea t ord 70fh742mwbadc 2014-04-30/19:36:47 10 rows selected --找出select * from t2 where t2.owner = 'YZM' 这条记录的SQL_ID '5cqmg9syj22s0',然后根据sql_id查找执行计划 select * from table(dbms_xplan.display_cursor('5cqmg9syj22s0')); SQL> select * from table(dbms_xplan.display_cursor('5cqmg9syj22s0'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID 5cqmg9syj22s0, child number 0
-------------------------------------
select * from t2 where t2.owner = 'YZM'
Plan hash value: 1513984157
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 68 (100)| |
|* 1 | TABLE ACCESS FULL| T2 | 3 | 1623 | 68 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("T2"."OWNER"='YZM')
Note
-----

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

22 rows selected

--测试2 查看不在缓冲池的sql的执行计划
create table t1(a number);
在session1执行
begin
for i in 1..1000000 loop
insert into t1 values(i);
end loop;
end;
在session2查询
select * from (select sql_text, sql_id,t.FIRST_LOAD_TIME
from v$sqlarea t order by t.FIRST_LOAD_TIME desc)
where rownum<=10； 找到一条sql的sql_id select * from table(dbms_xplan.display_cursor('1143q48fq12gb')); 结果会提示： SQL_ID 1143q48fq12gb, child number 0 begin for i in 1..1000000 loop insert into t1 values(i); end loop; end; NOTE: cannot fetch plan for SQL_ID: 1143q48fq12gb, CHILD_NUMBER: 0 Please verify value of SQL_ID and CHILD_NUMBER; It could also be that the plan is no longer in cursor cache (check v$sql_plan) 测试3 --查看正在运行的sql的执行计划 create table t3 as select * from all_objects ； session1 --执行 为了保证执行时间够长，造笛卡尔积并排序 select * from t2,t3 order by t2.status; 在session2找出sql_id 并查看 select * from table(dbms_xplan.display_cursor('dn2a4r8gxpvt4')); SQL_ID dn2a4r8gxpvt4, child number 0 ------------------------------------- select * from t2,t3 order by t2.status Plan hash value: 3984566296 -------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | -------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | | 63M(100)| | | 1 | SORT ORDER BY | | 310M| 202G| 215G| 63M (1)|211:07:20 | | 2 | MERGE JOIN CARTESIAN| | 310M| 202G| | 1471K (1)| 04:54:14 | | 3 | TABLE ACCESS FULL | T2 | 4965 | 2623K| | 69 (2)| 00:00:01 | | 4 | BUFFER SORT | | 62603 | 9659K| | 63M (1)|211:07:19 | | 5 | TABLE ACCESS FULL | T3 | 62603 | 9659K| | 296 (1)| 00:00:04 | -------------------------------------------------------------------------------------- Note ----- - dynamic sampling used for this statement (level=2)