dump之10046

10046用于跟踪sql语句的执行，该sql执行的三个主要阶段的耗时情况（parse，exec，fetch）

SQL> show parameter back;  --backgroud_dump_dest 为log日志以及dump文件目录路径
 
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
background_dump_dest                 string      /u01/oracle/diag/rdbms/d601/d6/trace

--查询当前会话sid

SQL> select distinct a.sid from v$mystat a;  

 
       SID
----------
      1322

--获取当前sid的trace文件路径   用户需要对v$session 和v$process视图有查询权限，或者已dba角色 执行

grant select on v_$process to 用户名； 

SQL> select b.tracefile,spid

  2    from v$session a, v$process b
  3   where a.paddr = b.addr
  4     and a.sid = 1322;
 
TRACEFILE                                                                        SPID
-------------------------------------------------------------------------------- ------------------------
/u01/oracle/diag/rdbms/d601/d6/trace/d6_ora_25530.trc                            25530
 
SQL> 

接下来开启 SESSION级跟踪需要 ALTER SESSION 权限（系统级跟踪对系统资源消耗较大，一般会话级即可）

grant alter session to username;

SQL> alter session set events '10046 trace name context forever , level 12'; --12代表对应跟踪等级。

Session altered.

在执行跟踪命令前是看不到对应的 d6_ora_25530.trc  文件的，除非开了系统级跟踪

这时执行需要跟踪的sql。以下一个业务优化前与优化后的fetch的时间对比

FETCH #2:c=44349257,e=44354479,p=0,cr=7990,cu=0,mis=0,r=1,dep=0,og=1,plh=3460609101,tim=1436841994379542

FETCH #1:c=41993,e=41680,p=0,cr=338,cu=96,mis=0,r=1,dep=0,og=1,plh=2928247108,tim=1436862350762129

最后关闭跟踪

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

错误更正

用户的跟踪文件路径参数应该是

USER_DUMP_DEST 

以下摘自oracle reference。对于background_dump_dest应该是一些主要的后台进程的跟踪文件，比如sid_ckpt_spid.trc等文件的路径

BACKGROUND_DUMP_DESTspecifies the pathname (directory or disc) where debuggingtrace files for the background processes (LGWR, DBWn, and so on) are written duringOracle operations.