查看sql的真实执行计划
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中，那么怎么弄
