oracle执行计划

来源：互联网发布：京东抢卷软件编辑：程序博客网时间：2024/06/05 10:57

查看oracle执行计划的三种方式：

1、利用PL/SQL Developer工具获取

在SQL windows写好sql，然后通过F5快捷键获取执行计划，如果是已经执行过的sql，可以通过V$SQL_PLAN视图查看已经被执行过的sql的执行计划

2、使用sql语句获取执行计划

SQL> explain plan for select * from dual;已解释。SQL> SELECT plan_table_output FROM TABLE(DBMS_XPLAN.DISPLAY('PLAN_TABLE'));PLAN_TABLE_OUTPUT--------------------------------------------------------------------------------Plan hash value: 4017058736--------------------------------------------------------------------------| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |--------------------------------------------------------------------------|   0 | SELECT STATEMENT  |      |     1 |     2 |     2   (0)| 00:00:01 ||   1 |  TABLE ACCESS FULL| DUAL |     1 |     2 |     2   (0)| 00:00:01 |--------------------------------------------------------------------------已选择8行。SQL>

或者

SQL> explain plan for select * from dual;已解释。SQL> select * from table(dbms_xplan.display);PLAN_TABLE_OUTPUT--------------------------------------------------------------------------------Plan hash value: 4017058736--------------------------------------------------------------------------| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |--------------------------------------------------------------------------|   0 | SELECT STATEMENT  |      |     1 |     2 |     2   (0)| 00:00:01 ||   1 |  TABLE ACCESS FULL| DUAL |     1 |     2 |     2   (0)| 00:00:01 |--------------------------------------------------------------------------已选择8行。SQL>

3、设置autotrace

SQL> set autotrace on;SQL> select * from dual;DU--X执行计划----------------------------------------------------------Plan hash value: 4017058736--------------------------------------------------------------------------| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |--------------------------------------------------------------------------|   0 | SELECT STATEMENT  |      |     1 |     2 |     2   (0)| 00:00:01 ||   1 |  TABLE ACCESS FULL| DUAL |     1 |     2 |     2   (0)| 00:00:01 |--------------------------------------------------------------------------统计信息----------------------------------------------------------          1  recursive calls          0  db block gets          3  consistent gets          0  physical reads          0  redo size        339  bytes sent via SQL*Net to client        358  bytes received via SQL*Net from client          2  SQL*Net roundtrips to/from client          0  sorts (memory)          0  sorts (disk)          1  rows processedSQL>

autotrace相关设置说明

序号命令含义 1 SET AUTOTRACE OFF 默认值，即关闭autotrace，显示sql的执行结果 2 SET AUTOTRACE ON EXPLAIN 只显示执行计划 3 SET AUTOTRACE ON STATISTICS 只显示统计信息 4 SET AUTOTRACE ON 显示sql执行结果，执行计划，统计信息 5 SET AUTOTRACE TRACEONLY 显示sql执行计划，统计信息，但不显示执行结果

0 0