explain plan

SQL> explain plan set statement_id='mytest' into plan_table for
  2  select employee_id from employees where employee_id between 100 and 108;

已解释。

SQL> desc dbms_xplan
FUNCTION DISPLAY RETURNS DBMS_XPLAN_TYPE_TABLE
参数名称                       类型                    输入/输出默认值？
------------------------------ ----------------------- ------ --------
 TABLE_NAME                     VARCHAR2                IN     DEFAULT
 STATEMENT_ID                   VARCHAR2                IN     DEFAULT
 FORMAT                         VARCHAR2                IN     DEFAULT

SQL> select plan_table_output from table(dbms_xplan.display('plan_table','mytest','serial'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

-----------------------------------------------------------------------
| Id  | Operation            |  Name          | Rows  | Bytes | Cost  |
-----------------------------------------------------------------------
|   0 | SELECT STATEMENT     |                |     9 |    27 |     1 |
|*  1 |  INDEX RANGE SCAN    | EMP_EMP_ID_PK  |     9 |    27 |     1 |
-----------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
   1 - access("EMPLOYEES"."EMPLOYEE_ID">=100 AND "EMPLOYEES"."EMPLOY
              EE_ID"<=108)

Note: cpu costing is off

已选择15行。

SQL> ed
已写入文件 afiedt.buf

  1* select plan_table_output from table(dbms_xplan.display('plan_table','mytest1','serial'))
SQL> /

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Error: cannot fetch plan for statement_id 'mytest1'

SQL>