看SQL执行计划的信息

方法1：运行以下脚本，生成plan_table表

SQL> @/u01/app/oracle/product/10.2/db_1/rdbms/admin/utlxplan.sql

Table created.

SQL> explain plan for

2 select deptno from scott.dept group by deptno;

Explained.

SQL> select id,operation,options,object_name,position from plan_table;

ID OPERATION OPTIONS OBJECT_NAME POSITION

---- -------------------- --------------- ------------------------- ----------

0 SELECT STATEMENT 1

1 SORT GROUP BY NOSORT 1

2 INDEX FULL SCAN PK_DEPT 1

方法2：oracle提供v$sql_plan来

SQL> select id,options,operation,object_name,cost

2 from v$sql_plan

3 where object_owner='SCOTT';

no rows selected－－没有数据的原因是：刚刚的explain plan for命令只产生执行计划，而不是真正执行语句

SQL> select deptno from scott.dept group by deptno;

DEPTNO

----------

10

20

30

40

SQL> select id,operation,options,object_name,position from plan_table;

ID OPERATION OPTIONS OBJECT_NAME POSITION

---- -------------------- -------------------- -------------------- ----------

0 SELECT STATEMENT 1

1 SORT GROUP BY NOSORT 1

2 INDEX FULL SCAN PK_DEPT 1

ITPub博客

explain plan for命令——SQL语句优化工具