[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.