如何查看执行计划
执行计划是SQL调优的最基本方法,查看执行计划的方法很多,不过我经常偷懒直接用PLSQL DEV来F5一下就完事了,这样下去很多能力都要退化了,下面看一下有哪些办法直接查看执行计划。数了一下,简单列出的就有5种之多。
1、直接使用sqlplus系统参数:
SQL> set autotrace on explain
SQL> select * from dual;
D
-
X
Execution Plan
----------------------------------------------------------
Plan hash value: 272002086
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 2 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS FULL| DUAL | 1 | 2 | 2 (0)| 00:00:01 |
--------------------------------------------------------------------------
SQL> set autotrace off
但是这样操作的结果是先执行SQL,再出执行计划,如果SQL耗时巨大,则不现实;
2、使用explain plan for语句:
SQL> explain plan for select * from dual;
Explained.
SQL> select * from table(DBMS_XPLAN.display);
Execution Plan
----------------------------------------------------------
Plan hash value: 2137789089
---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 8168 | 16336 | 21 (0)| 00:00:01 |
| 1 | COLLECTION ITERATOR PICKLER FETCH| DISPLAY | | |
| |
---------------------------------------------------------------------------------------------
这样就可以在执行SQL之前查看执行计划了
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
129 48940 B
SQL> exec dbms_system.set_SQL_TRACE_in_session(127,31923,true);
SQL> select * from dual;
SQL> exec dbms_system.set_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> select * from dual;
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
128 54521 B
129 48940 B
SQL> exec dbms_system.set_ev(127,31923,10046,8,'A');
SQL> select * from dual;
SQL> exec dbms_system.set_ev(127,31923,10046,0,'A');
5、使用tkprof格式化跟踪文件:
使用一下SQL找到当前session的跟踪文件:
当前 session
d.value|| '/' ||lower(rtrim(i.instance, chr( 0 )))|| '_ora_' ||p.spid|| '.trc' trace_file_name
select p.spid from v$mystat m,v$session s, v$process p
where m.statistic# = 1 and s.sid = m.sid and p.addr = s.paddr) p,
select t.instance from v$thread t,v$parameter v
where v.name = 'thread' and (v.value = 0 or t.thread# = to_number(v.value))) i,
select value from v$parameter where name = 'user_dump_dest' ) d;
其他用户 session
d.value|| '/' ||lower(rtrim(i.instance, chr( 0 )))|| '_ora_' ||p.spid|| '.trc' trace_file_name
select p.spid from v$session s, v$process p
where s.sid= '127 and s. SERIAL#= '31923 and p.addr = s.paddr) p,
select t.instance from v$thread t,v$parameter v
where v.name = 'thread' and (v.value = 0 or t.thread# = to_number(v.value))) i,
select value from v$parameter where name = 'user_dump_dest' ) d;
查找后使用tkprof命令:
SQL> $tkprof D:/....../SID_ora_5352.trc D:/....../SID_ora_5352.txt
注:9i中默认安装 无法使用 autotrace,需要手动安装:
SQL>start $ORACLE_HOME/rdbms/admin/utlxplan.sql;
SQL>create public synonym plan_table for plan_table;
SQL>grant ALL on plan_table to public;
附:ORACLE诊断事件
*********************************************************************************
Oracle为RDBMS提供了多种的诊断工具,诊断事件(Event)是其中一种常用、好用的方法,它使DBA可以方便的转储数据库各种结构及跟踪特定事件的发生.
一、Event的通常格式及分类
1、 通常格式如下:
EVENT="<事件名称><动作><跟踪项目><范围限定>"
2、 Event分类
诊断事件大体上可以分为四类:
a. 转储类事件:它们主要用于转储Oracle的一些结构,例如转储一下控制文件、数据文件头等内容。
b. 捕捉类事件:它们用于捕捉一些Error事件的发生,例如捕捉一下ORA-04031发生时一些Rdbms信息,以判断是Bug还是其它原因引起的这方面的问题。
c. 改变执行途径类事件:它们用于改主一些Oracle内部代码的执行途径,例如设置10269将会使Smon进程不去合并那些Free的空间。
d. 跟踪类事件:这们用于获取一些跟踪信息以用于Sql调优等方面,最典型的便是10046了,将会对Sql进行跟踪。
3、 说明:
a. 如果immediate放在第一个说明是无条件事件,即命令发出即转储到跟踪文件。
b. trace name位于第二、三项,除它们外的其它限定词是供Oracle内部开发组用的。
c. level通常位于1-10之间(10046有时用到12),10意味着转储事件所有的信息。例如当转储控制文件时,level1表示转储控制文件头,而level 10表明转储控制文件全部内容。
d. 转储所生成的trace文件在user_dump_dest初始化参数指定的位置。
二、说一说设置的问题了
可以在init.ora中设置所需的事件,这将对所有会话期打开的会话进行跟踪,也可以用alter session set event 等方法设置事件跟踪,这将打开正在进行会话的事件跟踪。
1、 在init.ora中设置跟踪事件的方法
a. 语法
EVENT=”event 语法|,level n|:event 语法|,level n|…”
b. 举例
event=”10231 trace name context forever,level 10’
c. 可以这样设置多个事件:
EVENT="/
10231 trace name context forever, level 10:/
10232 trace name context forever, level 10"
2、 通过Alter session/system set events这种方法
举个例子大家就明白了
Example:
Alter session set events ‘immediate trace name controlf level 10’;
Alter session set events ‘immediate trace name blockdump level 112511416’; (*)
在oracle8x及之上的版本也有这样的语句:
Alter system dump datafile 13 block 15;实现的功能与(*)是类似的。
3、 使用DBMS_SYSTEM.SET_EV的方法
a. 过和定义如下
DBMS_SYSTEM.SET_EV(
SI Binary_integer,
SE Binary_integer,
EV Binary_integer,
LE Binary_integer,
NM Binary_integer);
SI: 即v$session中的sid
SE:即v$session中的serial#
EV:要设置的事件
LE:要设置事件的级别
NM:名称
b. 举个例子,以10046为例
SQL> EXECUTE SYS.DBMS_SYSTEM.SET_EV(sid,serial#,10046,12,'');
4、 使用Oradebug来设置诊断事件的方法
同样举个例子大家就明白了:
a. 找到spid
SQL>select username, sid, serial#, paddr from v$session where username='qiuyb';
USERNAME SID SERIAL# PADDR
--------------------------------------------------------
HRB3 265 910 C000000084435AD8
SQL>SELECT ADDR,PID,SPID FROM V$PROCESS WHERE ADDR= C000000084435AD8';
ADDR PID SPID
------------------------------------------
C000000084435AD8 91 4835
b. 设置事件,以10046为例
sqlplus /nolog
SQL>connect / as sysdba;
SQL>oradebug setospid 4835
SQL>oradebug unlimit --不限制转储文件的大小
SQL> oradebug event 10046 trace name context forever,level 12 --设置事件进行sql跟踪
SQL> oradebug event 10046 trace name context off --关闭跟踪
注意不要用oradug去跟踪oracle的smon,pmon等几个进程,操作不当可能会杀掉这几个后台进和引起宕库。
三、你可能的问题
1、 我如何知道在系统中设置了哪些event?
回答:
a. 如果你的事件是在init.ora中设置的可以用
SQL>show parameter event;
来查看
b. Michael R.Ault给的SQL
serveroutput on size 1000000
declare
event_level number;
begin
for i in 10000..10999 loop
sys.dbms_system.read_ev(i,event_level);
if (event_level > 0) then
dbms_output.put_line('Event '||to_char(i)||' set at level '||
to_char(event_level));
end if;
end loop;
end;
/
2、在oracle9i中使用spfile的那种如何设置诊断事件呢?
回答:
简单,Alter system命令就可以完成
alter system set event='10046 trace name context forever, level 12' scope=spfile;
重启一下就生效了。
3、坏了,我的9i设置完诊断事件,起不来了,报ORA-02194错怎么办?
回答:
那你一定是在使用Alter system时把某一项写错了,比如把context写成了conetxt了,可以做如下的解决:
a.由spfile生成pfile
SQL>create pfile from spfile;
File created.
b.编辑pfile以修正错误
Change... *.event='10046 trace name conetxt forever, level 12'
-to- *.event='10046 trace name context forever, level 12'
c.用pfile启动
SQL>startup pfile=/.....
d.重新生成 SPFILE.
SQL>create spfile from pfile;
File created.