1.前台 pl/sql F5 , explain plan for 方式得到的计划,如果有绑定变量时候不准, 而且是预估值不是实际情况的
2.对于执行过的SQL:
如果我们想获取该语句的实际执行计划,通过下列步骤:
a、查询v$sql视图,找到该语句的sql_id(注意哟,必须要确保你要查询的sql语句还在shared pool):
SQL> select sql_id from v$sql where sql_text= 'select count(0) from cat_product cp,cat_drug cd where cp.medical_id=cd.id';
SQL_ID
-------------
c9cxqvr3q4tjd
b.、调用dbms_xplan包,查看该语句执行时的实现执行计划:
SQL> select * from table(dbms_xplan.display_cursor('c9cxqvr3q4tjd'));
如果想要更详细的则 开启statistics_level=all时或使用gather_plan_statistics hint 可以获得执行计划中实时的统计信息
//显示最后一条执行的执行计划
select * from table (dbms_xplan.display_cursor(null, null,'allstats +alias +outline'));
//对于已经执行的,需要找到 SQL_ID , 并且还在 V$SQL 中
select sql_id,child_number, plan_hash_value, last_load_time,last_active_time from v$sql where sql_id='xxxx';
select * from table (dbms_xplan.display_cursor('sql_id', 'child_number','allstats+alias+outline'));
注意: displan_cursor V$SQL里有的才行
案例分析
create table t1 as select * from all_objects where 1=2; --建表
create table t2 as select * from all_objects where 1=2; --建表
exec dbms_stats.gather_table_stats(null,'t1'); --先收集统计信息
exec dbms_stats.gather_table_stats(null,'t2'); -- 先收集统计信息
insert into t1 select * from all_objects; --插入数据
insert into t2 select * from all_objects; --插入数据
commit;
show parameter level -- 记不得可以通过这个查一下
alter session set statistics_level=all -- 收集一些附加的信息
//显示执行计划,对于执行过的SQL,而且还在v$sql里 可以通过 display_cursor 来显示
//select * from table (dbms_xplan.display_cursor(null , null ,'allstats +alias +outline'));
//由于执行时间太长 这里用: explain plan for select object_name from t1 where object_id not in (select max(object_id) from t2 group by owner)
//这个执行计划是针对 插入数据之前的统计信息 作出的 错误的统计信息 作出不好的访问路径
-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 30 | 5 (20)| 00:00:01 |
|* 1 | FILTER | | | | | |
| 2 | TABLE ACCESS FULL | T1 | 1 | 30 | 2 (0)| 00:00:01 |
|* 3 | FILTER | | | | | |
| 4 | HASH GROUP BY | | 1 | 30 | 3 (34)| 00:00:01 |
| 5 | TABLE ACCESS FULL| T2 | 1 | 30 | 2 (0)| 00:00:01 |
-----------------------------------------------------------------------------
explain plan for with a as (select /*+MATERIALIZE*/max(object_id) max_obj from t1 group by owner) select object_name from t2 where object_id not in (select a.max_obj from a)
select * from table (dbms_xplan.display)
-------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 30 | 7 (15)| 00:00:01 |
| 1 | TEMP TABLE TRANSFORMATION | | | | | |
| 2 | LOAD AS SELECT | T2 | | | | |
| 3 | HASH GROUP BY | | 1 | 30 | 3 (34)| 00:00:01 |
| 4 | TABLE ACCESS FULL | T1 | 1 | 30 | 2 (0)| 00:00:01 |
|* 5 | FILTER | | | | | |
| 6 | TABLE ACCESS FULL | T2 | 1 | 30 | 2 (0)| 00:00:01 |
|* 7 | VIEW | | 1 | 13 | 2 (0)| 00:00:01 |
| 8 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6601_946FB | 1 | 13 | 2 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------------
explain plan for 和 dbms_xplan.display 是处理 plan_table 表中的数据