xplan对dbms_xplan做了封装。
xplan显示的执行计划比dbms_xplan多了Order列。通过xplan可以清晰的看到执行计划中每一步的执行顺序。
安装需求:
|
1) PLAN_TABLE of at least 10.1 format 2) Either: SELECT ANY DICTIONARY Or: SELECT on V$DATABASE SELECT on V$SQL_PLAN SELECT on V$SESSION SELECT on V$MYSTAT SELECT on DBA_HIST_SQL_PLAN 3) CREATE TYPE, CREATE PROCEDURE |
安装步骤：执行xplan.sql
|
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> @d:/xplan.sql
类型已创建。 类型已创建。
原值 13: &&_awr_start 新值 13: /* 原值 19: &&_awr_end 新值 19: */
程序包已创建。
原值 270: &_awr_start 新值 270: /* 原值 342: &_awr_end 新值 342: */
程序包体已创建。
|
利用xplan查看执行计划：
|
SQL>SELECT * FROM TABLE(xplan.display_cursor('0psd0t6n2401z'));
SQL_ID 0psd0t6n2401z, child number 0 ------------------------------------- select * FROM company WHERE UpperComCode='123' ORDER BY ComCode
Plan hash value: 3855335939
------------------------------------------------------------------------------------------------------------- | Id | Order | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------------------------------------- | 0 | 4 | SELECT STATEMENT | | | | 6 (100)| | | 1 | 3 | SORT ORDER BY | | 8 | 2168 | 6 (17)| 00:00:01 | | 2 | 2 | TABLE ACCESS BY INDEX ROWID| company | 8 | 2168 | 5 (0)| 00:00:01 | |* 3 | 1 | INDEX RANGE SCAN | IDX_COM_UPPERCOMCODE | 8 | | 1 (0)| 00:00:01 | -------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id): -----------------------------------------------------------
3 - access("UPPERCOMCODE"='123')
|
卸载xplan
|
SQL> DROP PACKAGE xplan; 程序包已删除。
SQL> DROP TYPE XPLAN_OT; DROP TYPE XPLAN_OT 第 1 行出现错误: ORA-02303: cannot drop or replace a type with type or table dependents
SQL> DROP TYPE XPLAN_OT FORCE;
类型已删除。
SQL> DROP TYPE xplan_ntt;
类型已删除。
|