使用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.
使用包安装过程如下:
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/