怎样打开oracle的执行计划

查看Oracle执行计划的几种方法

一、通过PL/SQL Dev工具

1、直接File->New->Explain Plan Window，在窗口中执行sql可以查看计划结果。其中，Cost表示cpu的消耗，单位为n%，Cardinality表示执行的行数，等价Rows。

2、先执行 EXPLAIN PLAN FOR select from tableA where paraA=1，再 select from table(DBMS_XPLANDISPLAY)便可以看到oracle的执行计划了，看到的结果和1中的一样，所以使用工具的时候推荐使用1方法。

注意：PL/SQL Dev工具的Command window中不支持set autotrance on的命令。还有使用工具方法查看计划看到的信息不全，有些时候我们需要sqlplus的支持。

二、通过sqlplus

1．最简单的办法

Sql> set autotrace on

Sql> select from dual;

执行完语句后，会显示explain plan 与 统计信息。

这个语句的优点就是它的缺点，这样在用该方法查看执行时间较长的sql语句时，需要等待该语句执行成功后，才返回执行计划，使优化的周期大大增长。如果不想执行语句而只是想得到执行计划可以采用：

Sql> set autotrace traceonly

这样，就只会列出执行计划，而不会真正的执行语句，大大减少了优化时间。虽然也列出了统计信息，但是因为没有执行语句，所以该统计信息没有用处，如果执行该语句时遇到错误，解决方法为：

(1）在要分析的用户下：

Sqlplus > @

dbmsadminutlxplansql

(2) 用sys用户登陆

Sqlplus > @ sqlplusadminplustrcesql

Sqlplus > grant plustrace to user_name;

- - user_name是上面所说的分析用户

2．用explain plan命令

(1) sqlplus > explain plan for select from testdbmyuser

(2) sqlplus > select from table(dbms_xplandisplay);

上面这2种方法只能为在本会话中正在运行的语句产生执行计划，即我们需要已经知道了哪条语句运行的效率很差，我们是有目的只对这条SQL语句去优化。其实，在很多情况下，我们只会听一个客户抱怨说现在系统运行很慢，而我们不知道是哪个SQL引起的。此时有许多现成的语句可以找出耗费资源比较多的语句，如：

SELECT ADDRESS, substr(SQL_TEXT,1,20) Text, buffer_gets, executions,

buffer_gets/executions AVG FROM v$sqlarea

WHERE executions>0 AND buffer_gets > 100000 ORDER BY 5;

ADDRESS TEXT BUFFER_GETS EXECUTIONS AVG

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

66D83D64 select tname, (sel 421531 60104 701336017

66D9E8AC select tschema, tn 1141739 2732 417913250

66B82BCC select ssynonym_nam 441261 6 735435

从而对找出的语句进行进一步优化。当然我们还可以为一个正在运行的会话中运行的所有SQL语句生成执行计划，这需要对该会话进行跟踪，产生trace文件，然后对该文件用tkprof程序格式化一下，这种得到执行计划的方式很有用，因为它包含其它额外信息，如SQL语句执行的每个阶段(如Parse、Execute、Fetch)分别耗费的各个资源情况(如CPU、DISK、elapsed等)。

3、启用SQL_TRACE跟踪所有后台进程活动：

全局参数设置： OracleHome/admin/SID/pfile中指定: SQL_TRACE = true (10g)

当前session中设置：

SQL> alter session set SQL_TRACE=true;

SQL> select from dual;

SQL> alter session set SQL_TRACE=false;

对其他用户进行跟踪设置：

SQL> select sid,serial#,username from v$session where username='XXX';

SID SERIAL# USERNAME

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

127 31923 A

128 54521 B

开启跟踪:SQL> exec dbms_systemset_SQL_TRACE_in_session(127,31923,true);

关闭跟踪:SQL> exec dbms_systemset_SQL_TRACE_in_session(127,31923,false);

然后使用oracle自带的tkprof命令行工具格式化跟踪文件。

4、使用10046事件进行查询：

10046事件级别：

Lv1 - 启用标准的SQL_TRACE功能,等价于SQL_TRACE

Lv4 - Level 1 + 绑定值(bind values)

Lv8 - Level 1 + 等待事件跟踪

Lv12 - Level 1 + Level 4 + Level 8

全局设定：

OracleHome/admin/SID/pfile中指定: EVENT="10046 trace name context forever,level 12"

当前session设定：

开启:SQL> alter session set events '10046 trace name context forever, level 8';

关闭:SQL> alter session set events '10046 trace name context off';

对其他用户进行设置：

SQL> select sid,serial#,username from v$session where username='XXX';

SID SERIAL# USERNAME

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

127 31923 A

SQL> exec dbms_systemset_ev(127,31923,10046,8,'A');

5、使用tkprof格式化跟踪文件： (根据下面SQL语句得到的文件都不存在该目录下,郁闷啊,懵懂啊)

一般，一次跟踪可以分为以下几步：

1、界定需要跟踪的目标范围，并使用适当的命令启用所需跟踪。

2、经过一段时间后，停止跟踪。此时应该产生了一个跟踪结果文件。

3、找到跟踪文件，并对其进行格式化，然后阅读或分析。

--使用一下SQL找到当前session的跟踪文件：

SELECT dvalue|| '/' ||lower(rtrim(iinstance, chr( 0 )))|| '_ora_' ||pspid|| 'trc' trace_file_name

from

( select pspid from v$mystat m,v$session s, v$process p

where mstatistic# = 1 and ssid = msid and paddr = spaddr) p,

( select tinstance from v$thread t,v$parameter v

where vname = 'thread' and (vvalue = 0 or tthread# = to_number(vvalue))) i,

( select value from v$parameter where name = 'user_dump_dest' ) d;

-- 其它用户的 session

SELECT dvalue|| '/' ||lower(rtrim(iinstance, chr( 0 )))|| '_ora_' ||pspid|| 'trc' trace_file_name

from

( select pspid from v$session s, v$process p

where ssid= '27' and s SERIAL#= '30' and paddr = spaddr) p,

( select tinstance from v$thread t,v$parameter v

where vname = 'thread' and (vvalue = 0 or tthread# = to_number(vvalue))) i,

( select value from v$parameter where name = 'user_dump_dest' ) d;

--查找后使用tkprof命令,将TRACE文件格式为到D盘的explain_formattxt文件中

SQL> $tkprof d:/oracle/admin/FZLGFM/udump/fzlgfm_ora_3468trc d:/explain_formattxt

文件内容大致如下(看不太懂懵懂啊天啊神啊过几时就懂了/////////////)

TKPROF: Release 92010 - Production on 星期二 4月 20 13:59:20 2010

Copyright (c) 1982, 2002, Oracle Corporation All rights reserved

Trace file: d:/oracle/admin/FZLGFM/udump/fzlgfm_ora_3468trc

Sort options: default

count = number of times OCI procedure was executed

cpu = cpu time in seconds executing

elapsed = elapsed time in seconds executing

disk = number of physical reads of buffers from disk

query = number of buffers gotten for consistent read

current = number of buffers gotten in current mode (usually for update)

rows = number of rows processed by the fetch or execute call

alter session set events '10046 trace name context forever, level 8'

call count cpu elapsed disk query current rows

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

Parse 0 000 000 0 0 0 0

Execute 1 000 000 0 0 0 0

Fetch 0 000 000 0 0 0 0

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

total 1 000 000 0 0 0 0

Misses in library cache during parse: 0

Misses in library cache during execute: 1

Optimizer goal: CHOOSE

Parsing user id: SYS

一在线查看执行计划表

如果PLAN_TABLE表不存在，执行$ORACLE_HOME/rdbms/admin/utlxplansql创建plan_table表。

1explain plan

for

select from

2select from table(DBMS_XPLANDisplay);

二使用oracle第三方工具：

plsql developer(F5)

Toad (Ctrl+E)

三使用SQLPLUS：

如果PLAN_TABLE表不存在，执行$ORACLE_HOME/rdbms/admin/utlxplansql创建plan_table表。

如果PLUSTRACE角色不存在，执行

$ORACLE_HOME/sqlplus/admin/plustrcesql

1sqlplus / as sysdba

set autotrace on;

关于Autotrace几个常用选项的说明:

SET AUTOTRACE OFF ---------------- 不生成AUTOTRACE 报告，这是缺省模式

SET AUTOTRACE ON EXPLAIN ------ AUTOTRACE只显示优化器执行路径报告

SET AUTOTRACE ON STATISTICS -- 只显示执行统计信息

SET AUTOTRACE ON ----------------- 包含执行计划和统计信息

SET AUTOTRACE TRACEONLY ------ 同set autotrace on，但是不显示查询

2执行sql

四sql trace

1alter session set sql_trace=true;

2执行sql

3alter session set sql_trace=false;

4查看相应的sql trace文件。

五诊断事件(10046)

1alter session set events '10046 trace name context forever,level 12';

2执行sql

3alter session set events '10046 trace name context off';

3查看相应的sql trace文件。

可利用TKPROF工具查看跟踪文件

TKPROF是一个用于分析oracle跟踪文件并且产生一个更加清晰合理的输出结果的可执行工具。如果一个系统的执行效率比较低，一个比较好的方法是跟踪用户的会话并且使用TKPROF工具的排序功能格式化输出，从而找出有问题的SQL语句。

TKPROF命令后面的选项及输出文件各个列的含义在这里不做详细的介绍。google一下就会有很多资料。

下面简单描述一下TKPROF工具的使用步骤：

1、在session级别设置sql_trace=true

sys@ORCL>alter session set sql_trace=true;

Session altered

如果要在pl/sql中对session级别设置true，可以使用dbms_system这个包：

sys@ORCL> exec dbms_systemset_sql_trace_in_session(sid,serial#,true);

2、指定一下生成的trace文件的名字，便于查找：

sys@ORCL>alter session set trace file_identifier='yourname';

3、执行SQL语句。

4、利用TKPROF工具格式化输出的trace 文件：

[oracle@q1test01~] $tkprof/oracle/admin/orcl/udump/orcl_ora_10266_yournametrc/oracle/yournametxtexplain=user/pwdaggregate=yessys=nowaits=yessort=fchela

5、查看生成的文件再设置sql_trace=false：

sys@ORCL>alter session set sql_trace=false;

以上就是关于怎样打开oracle的执行计划全部的内容，包括:怎样打开oracle的执行计划、怎么使用plsql查看执行计划、等相关内容解答，如果想了解更多相关内容，可以关注我们，你们的支持是我们更新的动力！