ITPub博客

首页 > Linux操作系统 > Linux操作系统 > PERF--DBMS_XPLAN 9i中新的執行計划查看工具

PERF--DBMS_XPLAN 9i中新的執行計划查看工具

原创 Linux操作系统 作者:vongates 时间:2019-07-17 13:09:02 0 删除 编辑

在9i中提供了dbms_xplan這個package,用于要看plan_table的內容,并對表中的內容格式化,我們可以通過下面的這些方法來取得plan_table格式化后的輸出:

@?/rdbms/admin/utlxplp;
select * from table(dbms_xplan.display);

下面有一個簡單的過程轉于 oracle-base 供參考,


The DBMS_XPLAN package is used to format the output of an explain plan. It is intended as a replacement for the utlxpls.sql script.

If it is not already present create the SCOTT schema:
conn sys/password as sysdba
@$ORACLE_HOME/rdbms/admin/utlsampl.sql
Create a PLAN_TABLE if it does not already exist:
conn sys/password as sysdba
@$ORACLE_HOME/rdbms/admin/utlxplan.sql
CREATE PUBLIC SYNONYM plan_table FOR sys.plan_table;
GRANT ALL ON sys.plan_table TO public;
Next we explain an SQL statement:
conn scott/tiger
EXPLAIN PLAN FOR
SELECT *
FROM   emp e, dept d
WHERE  e.deptno = d.deptno
AND    e.ename  = 'SMITH';
Finally we use the DBMS_XPLAN.DISPLAY function to display the execution plan:
SET LINESIZE 130
SET PAGESIZE 0
SELECT * 
FROM   TABLE(DBMS_XPLAN.DISPLAY);

----------------------------------------------------------------------------
| Id  | Operation                    |  Name       | Rows  | Bytes | Cost  |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |             |     1 |    57 |     3 |
|   1 |  NESTED LOOPS                |             |     1 |    57 |     3 |
|*  2 |   TABLE ACCESS FULL          | EMP         |     1 |    37 |     2 |
|   3 |   TABLE ACCESS BY INDEX ROWID| DEPT        |     1 |    20 |     1 |
|*  4 |    INDEX UNIQUE SCAN         | PK_DEPT     |     1 |       |       |
----------------------------------------------------------------------------

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

   2 - filter("E"."ENAME"='SMITH')
   4 - access("E"."DEPTNO"="D"."DEPTNO")

Note: cpu costing is off

18 rows selected.
The DBMS_XPLAN.DISPLAY function can accept 3 parameters:
  • table_name - Name of plan table, default value 'PLAN_TABLE'.
  • statement_id - Statement id of the plan to be displayed, default value NULL.
  • format - Controls the level of detail displayed, default value 'TYPICAL'. Other values include 'BASIC', 'ALL', 'SERIAL'.
EXPLAIN PLAN SET STATEMENT_ID='TSH' FOR
SELECT *
FROM   emp e, dept d
WHERE  e.deptno = d.deptno
AND    e.ename  = 'SMITH';

SET LINESIZE 130
SET PAGESIZE 0
SELECT * 
FROM   TABLE(DBMS_XPLAN.DISPLAY('PLAN_TABLE','TSH','BASIC'));

----------------------------------------------------
| Id  | Operation                    |  Name       |
----------------------------------------------------
|   0 | SELECT STATEMENT             |             |
|   1 |  NESTED LOOPS                |             |
|   2 |   TABLE ACCESS FULL          | EMP         |
|   3 |   TABLE ACCESS BY INDEX ROWID| DEPT        |
|   4 |    INDEX UNIQUE SCAN         | PK_DEPT     |
----------------------------------------------------

10 rows selected.

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

请登录后发表评论 登录
全部评论

注册时间:2018-09-11

  • 博文量
    449
  • 访问量
    292658