查看oracle执行计划(命令实现)

?

?

看下面小练习,告诉你如何用oracle命令查看执行计划.

?

1.想要查看执法计划的SQL语句:

explain plan for select t.*, t.rowid from A5 t where t.l = '101214' and t.k like '%8号' order by i ;

?

?

2.查看此SQL语句的执行计划:

select * from table(dbms_xplan.display);

执行结果如下:

PLAN_TABLE_OUTPUT--------------------------------------------Plan hash value: 1156097717---------------------------------------| Id? | Operation????????? | Name | Rows? | Bytes | Cost (%CPU)| Time???? |---------------------------------------|?? 0 | SELECT STATEMENT?? |?????   |??? 12 |? 1524 |??? 71?? (3)| 00:00:01 ||?? 1 |? SORT ORDER BY????  |?????   |??? 12 |? 1524 |??? 71?? (3)| 00:00:01 ||*? 2 |?? TABLE ACCESS FULL| A5?? |??? 12 |? 1524 |??? 70?? (2)| 00:00:01 |----------------------------------------Note-----   - 'PLAN_TABLE' is old version 12 rows selected

'PLAN_TABLE' is old version : 这句话告诉我们,PLAN_TABLE的版本太旧,需要重新生成.

?

3.重新生成PLAN_TABLE表:

-- 删除表drop table PLAN_TABLE;-- 新建表@D:\oracle\product\10.2.0\db_1\RDBMS\ADMIN\utlxplan.sql;

?

4.查看执行计划:

select * from table(dbms_xplan.display);

结果如下:

?

PLAN_TABLE_OUTPUT--------------------------------------------Plan hash value: 1156097717---------------------------------------| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |---------------------------------------|   0 | SELECT STATEMENT   |      |    12 |  1524 |    71   (3)| 00:00:01 ||   1 |  SORT ORDER BY     |      |    12 |  1524 |    71   (3)| 00:00:01 ||*  2 |   TABLE ACCESS FULL| A5   |    12 |  1524 |    70   (2)| 00:00:01 |---------------------------------------Predicate Information (identified by operation id):---------------------------------------------------   2 - filter("T"."L"='101214' AND "T"."K" LIKE '%8号') 14 rows selected

?

?

?

?

?

?

?

?