追踪SQL语句，查看执行计划系列

技术2024-07-13 67

如何查看执行计划执行计划是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

SELECT d.value|| '/' ||lower(rtrim(i.instance, chr( 0 )))|| '_ora_' ||p.spid|| '.trc' trace_file_name

from

( 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

SELECT d.value|| '/' ||lower(rtrim(i.instance, chr( 0 )))|| '_ora_' ||p.spid|| '.trc' trace_file_name

from

( 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.

最新回复(0)