Oracle查看SQL执行计划的方式__select_PLAN_TABLE_OUTPUT_00_empno_NUMBER_

当前位置: 数据库>oracle

Oracle查看SQL执行计划的方式

来源: 互联网发布时间：2017-06-05

本文导语: 获取Oracle sql执行计划并查看执行计划，是掌握和判断数据库性能的基本技巧。下面案例介绍了多种查看sql执行计划的方式：基本有以下几种方式： 1、通过sql_trace初始化参数 2、通过Autotrace 3、通过explain plan 4、通过dbms_xplan.disp...

获取Oracle sql执行计划并查看执行计划，是掌握和判断数据库性能的基本技巧。下面案例介绍了多种查看sql执行计划的方式：

基本有以下几种方式：

1、通过sql_trace初始化参数

2、通过Autotrace

3、通过explain plan

4、通过dbms_xplan.display_cursor

5、通过dbms_xplan.display_awr

6、通过10046事件

1、通过explain plan 工具

12:24:00 SCOTT@ prod>explain plan for

12:24:06 2 select empno,ename,sal,deptno from emp where empno=7788;

Explained.

Elapsed: 00:00:00.22

12:24:16 SCOTT@ prod>select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT

------------------------------------------------------------------------------------------------------

Plan hash value: 2949544139

--------------------------------------------------------------------------------------

--------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 1 | 46 | 2 (0)| 00:00:01 |

| 1 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 46 | 2 (0)| 00:00:01 |

|* 2 | INDEX UNIQUE SCAN | PK_EMP | 1 | | 1 (0)| 00:00:01 |

--------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

2 - access("EMPNO"=7788)

14 rows selected.

Elapsed: 00:00:01.14

2、通过DBMS_XPLAN.display_cursor查看

12:52:37 SCOTT@ prod>desc dbms_xplan

FUNCTION DISPLAY_CURSOR RETURNS DBMS_XPLAN_TYPE_TABLE

Argument Name Type In/Out Default?

------------------------------ ----------------------- ------ --------

SQL_ID VARCHAR2 IN DEFAULT

CURSOR_CHILD_NO NUMBER(38) IN DEFAULT

FORMAT VARCHAR2 IN DEFAULT

12:31:44 SYS@ prod>select * from dict where upper(table_name)='V$SESSION';

TABLE_NAME COMMENTS

------------------------------ ----------------------------------------

Elapsed: 00:00:00.09

12:31:09 SYS@ prod>grant select on V_$SESSION to scott;

Grant succeeded.

Elapsed: 00:00:00.10

12:43:15 SCOTT@ prod>select * from table(dbms_xplan.display_cursor(null,null,'advanced'));

PLAN_TABLE_OUTPUT

------------------------------------------------------------------------------------------------------

12:42:33 SYS@ prod>grant select any table to scott;

Grant succeeded.

12:43:46 SYS@ prod>show parameter o7

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

O7_DICTIONARY_ACCESSIBILITY boolean TRUE

12:44:54 SYS@ prod>

12:42:45 SCOTT@ prod>select empno,ename,sal,deptno from emp where empno=7788;

EMPNO ENAME SAL DEPTNO

---------- ---------- ---------- ----------

7788 SCOTT 3000 20

Elapsed: 00:00:00.08

12:43:15 SCOTT@ prod>select * from table(dbms_xplan.display_cursor(null,null,'all'));

PLAN_TABLE_OUTPUT

------------------------------------------------------------------------------------------------------

SQL_ID bqz9ujgnn4jzu, child number 0

-------------------------------------

select empno,ename,sal,deptno from emp where empno=7788

Plan hash value: 2949544139

--------------------------------------------------------------------------------------

--------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | | | 2 (100)| |

| 1 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 46 | 2 (0)| 00:00:01 |

|* 2 | INDEX UNIQUE SCAN | PK_EMP | 1 | | 1 (0)| 00:00:01 |

--------------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):

-------------------------------------------------------------

PLAN_TABLE_OUTPUT

------------------------------------------------------------------------------------------------------

1 - SEL$1 / EMP@SEL$1

2 - SEL$1 / EMP@SEL$1

Predicate Information (identified by operation id):

---------------------------------------------------

2 - access("EMPNO"=7788)

Column Projection Information (identified by operation id):

-----------------------------------------------------------

1 - "EMPNO"[NUMBER,22], "ENAME"[VARCHAR2,10], "SAL"[NUMBER,22],

"DEPTNO"[NUMBER,22]

2 - "EMP".ROWID[ROWID,10], "EMPNO"[NUMBER,22]

32 rows selected.

Elapsed: 00:00:00.05

12:49:10 SCOTT@ prod>select empno,ename,sal,deptno from emp where empno=7788;

EMPNO ENAME SAL DEPTNO

---------- ---------- ---------- ----------

7788 SCOTT 3000 20

Elapsed: 00:00:00.00

12:50:06 SCOTT@ prod>select * from table(dbms_xplan.display_cursor(null,null,'advanced'));

PLAN_TABLE_OUTPUT

------------------------------------------------------------------------------------------------------

SQL_ID bqz9ujgnn4jzu, child number 0

-------------------------------------

select empno,ename,sal,deptno from emp where empno=7788

Plan hash value: 2949544139

--------------------------------------------------------------------------------------

--------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | | | 2 (100)| |

| 1 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 46 | 2 (0)| 00:00:01 |

|* 2 | INDEX UNIQUE SCAN | PK_EMP | 1 | | 1 (0)| 00:00:01 |

--------------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):

-------------------------------------------------------------

PLAN_TABLE_OUTPUT

------------------------------------------------------------------------------------------------------

1 - SEL$1 / EMP@SEL$1

2 - SEL$1 / EMP@SEL$1

Outline Data

-------------

/*+

BEGIN_OUTLINE_DATA

IGNORE_OPTIM_EMBEDDED_HINTS

OPTIMIZER_FEATURES_ENABLE('11.2.0.1')

DB_VERSION('11.2.0.1')

ALL_ROWS

OUTLINE_LEAF(@"SEL$1")

INDEX_RS_ASC(@"SEL$1" "EMP"@"SEL$1" ("EMP"."EMPNO"))

END_OUTLINE_DATA

PLAN_TABLE_OUTPUT

------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

2 - access("EMPNO"=7788)

Column Projection Information (identified by operation id):

-----------------------------------------------------------

1 - "EMPNO"[NUMBER,22], "ENAME"[VARCHAR2,10], "SAL"[NUMBER,22],

"DEPTNO"[NUMBER,22]

2 - "EMP".ROWID[ROWID,10], "EMPNO"[NUMBER,22]

46 rows selected.

Elapsed: 00:00:00.06

12:50:21 SCOTT@ prod>

这种方法在 SQLPLUS中查看刚执行过的 SQLSQLSQL的执行计划。

-- dbms_xplan.display_cursor传入的前两个参数值均为 null,null第三个参数是 "advanced"第三个参数也可以是 "all"得到的显示结果，少了 "Outline data"部分的内容。

sql>select sql_text,sql_id,hash_value,child_number from v$sql

2* where sql_text like 'select empno,ename,sal%'

SQL_TEXT SQL_ID HASH_VALUE CHILD_NUMBER

-------------------------------------------------- ------------- ---------- ------------

select empno,ename,sal,deptno from emp where empno

=7788

Elapsed: 00:00:00.04

13:00:25 SCOTT@ prod>select * from table(dbms_xplan.display_cursor('bqz9ujgnn4jzu',0,'advanced'));

PLAN_TABLE_OUTPUT

------------------------------------------------------------------------------------------------------

SQL_ID bqz9ujgnn4jzu, child number 0

-------------------------------------

select empno,ename,sal,deptno from emp where empno=7788

Plan hash value: 2949544139

--------------------------------------------------------------------------------------

--------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | | | 2 (100)| |

| 1 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 46 | 2 (0)| 00:00:01 |

|* 2 | INDEX UNIQUE SCAN | PK_EMP | 1 | | 1 (0)| 00:00:01 |

--------------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):

-------------------------------------------------------------

PLAN_TABLE_OUTPUT

------------------------------------------------------------------------------------------------------

1 - SEL$1 / EMP@SEL$1

2 - SEL$1 / EMP@SEL$1

Outline Data

-------------

/*+

BEGIN_OUTLINE_DATA

IGNORE_OPTIM_EMBEDDED_HINTS

OPTIMIZER_FEATURES_ENABLE('11.2.0.1')

DB_VERSION('11.2.0.1')

ALL_ROWS

OUTLINE_LEAF(@"SEL$1")

INDEX_RS_ASC(@"SEL$1" "EMP"@"SEL$1" ("EMP"."EMPNO"))

END_OUTLINE_DATA

PLAN_TABLE_OUTPUT

------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

2 - access("EMPNO"=7788)

Column Projection Information (identified by operation id):

-----------------------------------------------------------

1 - "EMPNO"[NUMBER,22], "ENAME"[VARCHAR2,10], "SAL"[NUMBER,22],

"DEPTNO"[NUMBER,22]

2 - "EMP".ROWID[ROWID,10], "EMPNO"[NUMBER,22]

46 rows selected.

Elapsed: 00:00:00.14

更多详情见请继续阅读下一页的精彩内容：

在CentOS 6.4下安装Oracle 11gR2(x64)

Oracle 11gR2 在VMWare虚拟机中安装步骤

Debian 下安装 Oracle 11g XE R2

Oracle 11gR2 在VMWare虚拟机中安装步骤

您可能感兴趣的文章:

怎么写一个Shell来执行这样的功能，访问Oracle数据库，然后执行一个SQL脚本，生成一个文件。急！

win2000+jbuilder6+oracle817编出的程序，在win2000下执行很好，在win98下却访问不了oracle数据库

oracle 可以在crontab 中定时执行吗？

oracle sql执行过程(流程图)

linux能够通过执行脚本添加oracle数据库的用户吗

ORACLE安装时/tmp/orainstRoot.sh 执行发生错误

求救：HPUNIX下的ORACLE7执行select * from tablename提示权限不足！！

为什么 export ORACLE_SID=test写在程序里面就不会执行？

请问在 Linux 下如何用代码实现连接oracle数据库并执行 SQL 语句？

shell调用oracle储存过程，怎么判断储存过程执行结果是否正确

shell 执行oracle sql脚本的问题

oracle单库彻底删除干净的执行步骤

查看Oracle的执行计划一句话命令

关于ORACLE中执行批处理的问题

oracle中得到一条SQL语句的执行时间的两种方式

oracle导出sql语句的结果集和保存执行的sql语句(深入分析)

Nagios check_oracle_health 关于执行SQL问题

执行Commit时Oracle做哪些工作

RedHat AS 4 安装oracle9i的时候，执行Disk1下的runInstaller后提示正在初始化虚拟机，请等待后就再无反应

JBUILDER如何执行ORACLE的储存过程

Oracle 数据库(oracle Database)Select 多表关联查询方式

linux下通过对文件读取方式查询oracle的版本信息

参加Oracle认证的两种考试方式

Oracle解锁的方式介绍

Linux操作系统下Oracle数据库多实例启动方式及修改内存

如何实现将客户机上的word文件,以B/S方式最终存入oracle数据库?

asp.net使用ODP即oracle连接方式的的防注入登录验证程序

本站(WWW.)旨在分享和传播互联网科技相关的资讯和技术，将尽最大努力为读者提供更好的信息聚合和浏览方式。
本站(WWW.)站内文章除注明原创外，均为转载、整理或搜集自网络。欢迎任何形式的转载，转载请注明出处。