[20120112]xplan包的安装.txt

使用xplan包可以更好的查看执行计划,它提供两种使用方式,1.单独调用执行.2.通过包调用.

使用包安装过程如下:
1.下载安装文件并解压:
http://www.oracle-developer.net/content/utilities/xplan.zip?p=1.2

2.我选择sys用户来安装:
其它用户需要这些权限:
-- Required:     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
--                  Or:
--                     SELECT_CATALOG_ROLE
--
--               3) CREATE TYPE, CREATE PROCEDURE

$ sqlplus sys as sysdba

SQL> @ xplan.package.sql
SQL> CREATE PUBLIC SYNONYM XPLAN FOR SYS.XPLAN;
SQL> grant execute on sys.xplan to public;

3.例子:

SQL> var a number;
SQL> exec :a :=10;
SQL> select /*+ gather_plan_statistics */ * from dept where deptno=:a;
SQL> select * from table(xplan.display_cursor(NULL,NULL,'ALLSTATS LAST'));
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID  atqyqkjm2a7ts, child number 0
-------------------------------------
select /*+ gather_plan_statistics */ * from dept where deptno=:a
Plan hash value: 2852011669
---------------------------------------------------------------------------------------------------------------
| Id  | PID | Order | Operation                   | Name    | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
---------------------------------------------------------------------------------------------------------------
|   0 |     |     3 | SELECT STATEMENT            |         |      1 |        |      1 |00:00:00.01 |       2 |
|   1 |   0 |     2 |  TABLE ACCESS BY INDEX ROWID| DEPT    |      1 |      1 |      1 |00:00:00.01 |       2 |
|*  2 |   1 |     1 |   INDEX UNIQUE SCAN         | PK_DEPT |      1 |      1 |      1 |00:00:00.01 |       1 |
---------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
-----------------------------------------------------------------
   2 - access("DEPTNO"=:A)
About
-----
   - XPlan v1.2 by Adrian Billington (http://www.oracle-developer.net)
20 rows selected.

4.对比dbms_xplan.display_cursor的显示:
SQL> select /*+ gather_plan_statistics */ * from dept where deptno=:a;
SQL> select * from table(dbms_xplan.display_cursor(NULL,NULL,'ALLSTATS LAST'));
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID  atqyqkjm2a7ts, child number 0
-------------------------------------
select /*+ gather_plan_statistics */ * from dept where deptno=:a
Plan hash value: 2852011669
-------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name    | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |         |      1 |        |      1 |00:00:00.01 |       2 |
|   1 |  TABLE ACCESS BY INDEX ROWID| DEPT    |      1 |      1 |      1 |00:00:00.01 |       2 |
|*  2 |   INDEX UNIQUE SCAN         | PK_DEPT |      1 |      1 |      1 |00:00:00.01 |       1 |
-------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("DEPTNO"=:A)
19 rows selected.

5.如果你不习惯安装包的方式,也可以采用直接调用的方式.
对应文件就是一个readme,自己看看就知道如何执行了.

SQL> @ /home/oracle/sqllaji/xplan.display_cursor.sql  atqyqkjm2a7ts "" "allstats last peeked_binds"
PLAN_TABLE_OUTPUT
--------------------------------------
SQL_ID  atqyqkjm2a7ts, child number 0
-------------------------------------
select /*+ gather_plan_statistics */ * from dept where deptno=:a
Plan hash value: 2852011669
-------------------------------------------------------------------------------------------------------------
| Id  | Pid | Ord | Operation                   | Name    | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------------------------------
|   0 |     |   3 | SELECT STATEMENT            |         |      1 |        |      1 |00:00:00.01 |       2 |
|   1 |   0 |   2 |  TABLE ACCESS BY INDEX ROWID| DEPT    |      1 |      1 |      1 |00:00:00.01 |       2 |
|*  2 |   1 |   1 |   INDEX UNIQUE SCAN         | PK_DEPT |      1 |      1 |      1 |00:00:00.01 |       1 |
-------------------------------------------------------------------------------------------------------------
Peeked Binds (identified by position):
--------------------------------------
   1 - (NUMBER): 10
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("DEPTNO"=:A)
About
------
  - XPlan v1.2 by Adrian Billington (http://www.oracle-developer.net)
24 rows selected.

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/267265/viewspace-714778/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/267265/viewspace-714778/