1.设置autotrace
序号 | 命令 | 解释 |
1 | SET AUTOTRACE OFF | 此为默认值,即关闭Autotrace |
2 | SET AUTOTRACE ON EXPLAIN | 只显示执行计划 |
3 | SET AUTOTRACE ON STATISTICS | 只显示执行的统计信息 |
4 | SET AUTOTRACE ON | 包含2,3两项内容 |
5 | SET AUTOTRACE TRACEONLY | 与ON相似,但不显示语句的执行结果 |
如果有些sql不允许重复操作,不能重新执行,所以要第二种
2.dbms_xplan.display_cursor来获取执行过的sql的执行计划
DBMS_XPLAN.DISPLAY_CURSOR(
sql_id IN VARCHAR2 DEFAULT NULL,
child_number IN NUMBER DEFAULT NULL,
format IN VARCHAR2 DEFAULT 'TYPICAL');
select sql_id from v$sql where sql_text='select substrb(dump(val,16,0,32),1,120) ep, cnt from (select /*+ no_expand_table(t) index_rs(t) no_parallel(t) no_parallel_index(t) dbms_stats cursor_sharing_exact use_weak_name_resl dynamic_sampling(0) no_monitoring no_substrb_pad */"SNAP_ID" val,count(*) cnt from "SYS"."WRH$_INSTANCE_RECOVERY" t where "SNAP_ID" is not null group by "SNAP_ID") order by val';
cztaxpckf0186
select * from table(dbms_xplan.display_cursor('cztaxpckf0186'));
1 SQL_ID cztaxpckf0186, child number 0
2 -------------------------------------
3 select substrb(dump(val,16,0,32),1,120) ep, cnt from (select /*+
4 no_expand_table(t) index_rs(t) no_parallel(t) no_parallel_index(t)
5 dbms_stats cursor_sharing_exact use_weak_name_resl dynamic_sampling(0)
6 no_monitoring no_substrb_pad */"SNAP_ID" val,count(*) cnt from
7 "SYS"."WRH$_INSTANCE_RECOVERY" t where "SNAP_ID" is not null group by
8 "SNAP_ID") order by val
9
10 Plan hash value: 4054321153
11
12 ---------------------------------------------------------------------------------------------------
13 | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
14 ---------------------------------------------------------------------------------------------------
15 | 0 | SELECT STATEMENT | | | | 3 (100)| |
16 | 1 | SORT GROUP BY | | 197 | 985 | 3 (34)| 00:00:01 |
17 | 2 | INDEX FAST FULL SCAN| WRH$_INSTANCE_RECOVERY_PK | 197 | 985 | 2 (0)| 00:00:01 |
18 ---------------------------------------------------------------------------------------------------
如果要查询的sql语句不在shared pool中,那么怎么弄