查看SQL执行计划 explain
查看SQL执行计划 explain
explain使用方式
alter session set current_schema=test;
explain plan for +sql语句; --并不会实际执行,因此生成的执行计划也是预估的
select * from table(dbms_xplan.display);
explain使用场景
1.内存中没有谓词信息了,查看谓词信息。
2.添加hint或者改写SQL后,看预估执行计划。
3.收集完统计信息后,看新的rows评估情况。或者看不同绑定变量的评估情况。
explain查看执行计划示例
create table t1014 as select * from dba_objects;
SYS@db11g> explain plan for select object_name from t1014 where object_id<100;
Explained.
SYS@db11g> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------
Plan hash value: 2622338484
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 1106 | 337 (1)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T1014 | 14 | 1106 | 337 (1)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("OBJECT_ID"<100)
Note
-----
- dynamic sampling used for this statement (level=2)
17 rows selected.