使用Oracle自带的系统包和过程监控其它会话SQL语句的执行计划等信息

 查看本地会话的SQL或PL/SQL的执行计划可以在本地执行 alter session set sql_trace=true; 和打开捕获 10046事件 alter session set events '10046 trace name context forever,level 12';
10046事件的 level 值说明如下：
level 1：和 sql trace 作用相同（跟踪sql语句，包括解析、执行、提取、提交和回滚等）
level 4： level 1+ SQL的梆定变量信息
level 8： level 1+ SQL的等待事件信息
level 12： level 4 + level 8

查看本地会话执行的SQL执行计划也可以用 set autotrace on 或 explain plan for...，不过这两种方式捕获的执行计划可能不准确的，10046事件和 dbms_xplan.display_cursor 返回的执行计划信息才是准确的。

如果要查看远程其它会话的SQL或PL/SQL语句的执行情况呢？那么可以使用Oracle提供的系统包和存储过程：
dbms_system.set_sql_trace_in_session(sid,serial#,sql_trace);  --开启SQL Trace，这个相当于在本地会话执行 alter session set sql_trace=true;
sid 参数等于 v$session.sid；
serial# 参数等于 v$session.serial#；
sql_trace 是逻辑布尔值，true表示开启sql_trace，fasle表示关闭。 

dbms_monitor.session_trace_enable(session_id,serial_num,waits,binds);   --打开10046事件捕获SQL或PL/SQL语句的执行计划和等待事件等信息。
 session_id  参数等于 v$session.sid；
 serial_num 参数等于 v$session.serial#；
waits 参数表示记录等待事件的信息；
binds 参数表示记录梆定变量的信息。

以下通过事例来说明，分别开两个session：
session 1登录的用户是sys（用于执行系统包和过程打开和关闭远程会话(session 2) 的sql trace 和 10086；
session 2登录的用户是tuser，用于执行SQL语句，以便session 1捕获其的SQL执行计划等信息。

session 1：
查询 session 2 的 sid和serial#：
      04:22:59 SYS@orcl*SQL> select sid,serial# from v$session

04:23:05   2  where username='TUSER' and status in ('ACTIVE','INACTIVE');

               SID            SERIAL#

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

                25               1002
或者在 session  2中执行 select sid,serial# from v$session where sid=userenv('sid'); 或者
select sid,serial# from v$session where sid=(select distinct sid from v$mystat);
查看相关的 sid和serial# 。

开启 session 的SQL Trace：
      exec dbms_system.set_sql_trace_in_session(sid=>25,serial#=>1002,sql_trace=>true);


session 2： 
 在 session 2 中执行SQL：
04:20:32 TUSER@orcl*SQL> select * from emp where empno>7800;
 
查询session 2的trace跟踪文件位置：

04:25:54 TUSER@orcl*SQL> select s.sid,s.process,p.addr,p.spid,p.tracefile   

04:25:57   2  from v$process p,v$session s

04:25:57   3  where p.addr=s.paddr

04:25:57   4  and s.sid=userenv('sid');

               SID PROCESS    ADDR     SPID       TRACEFILE

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

                25 13840      38BCE46C 13842      /oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_13842.trc

 

查看 session 2 的 trace 文件记录的SQL的执行情况信息：
oracle@SLES11:/oracle/diag/rdbms/orcl/orcl/trace> less orcl_ora_13842.trc

PARSING IN CURSOR #3 len=34 dep=0 uid=91 oct=3 lid=91 tim=1452975876173470 hv=1639899136 ad='338cf680' sqlid='b2mdjv5hvxs00'

select * from emp where empno>7800

END OF STMT

PARSE #3:c=8001,e=6957,p=2,cr=42,cu=0,mis=1,r=0,dep=0,og=1,plh=3956160932,tim=1452975876173470

EXEC #3:c=0,e=9,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=3956160932,tim=1452975876173520

FETCH #3:c=0,e=31357,p=2,cr=3,cu=0,mis=0,r=1,dep=0,og=1,plh=3956160932,tim=1452975876204942

FETCH #3:c=0,e=33,p=0,cr=1,cu=0,mis=0,r=5,dep=0,og=1,plh=3956160932,tim=1452975876205507

STAT #3 id=1 cnt=6 pid=0 pos=1 obj=74974 op='TABLE ACCESS FULL EMP (cr=4 pr=2 pw=0 time=0 us cost=3 size=114 card=3)'
 
上面显示的是SQL的解析、执行、提取数据、执行计划等信息。其中什么cpu时间，执行用时，一致读，当前读等各项参数的含义网上说得很详细，这里不再详述。 

session 1：
在session 1中关闭远程会话的 sql trace 功能：
04:26:38 SYS@orcl*SQL> exec dbms_system.set_sql_trace_in_session(25,1002,false);

再在 session 1 中开启 seesion 2 的10046事件：
04:26:42 SYS@orcl*SQL> exec dbms_monitor.session_trace_enable(25,1002,waits=>true,binds=>true);
并将在 sys 用户创建的 f_test 测试样例函数授权给tuser用户： 
grant execute on sys.f_test to tuser;

session 2：
在 session 2 中执行SQL和使用梆定变量：

04:26:01 TUSER@orcl*SQL> var a number

04:30:03 TUSER@orcl*SQL> var b number

04:30:07 TUSER@orcl*SQL> var rs varchar2(200) 

04:30:19 TUSER@orcl*SQL> exec :a:=1 

04:31:09 TUSER@orcl*SQL> exec :b:=2

04:31:32 TUSER@orcl*SQL> exec :rs:=sys.f_test(:a,:b);

04:32:24 TUSER@orcl*SQL> print rs

RS

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

The result:1 + 2 = 3

04:32:26 TUSER@orcl*SQL> select sys.f_test(:a,:b) from dual;

SYS.F_TEST(:A,:B)

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

The result:1 + 2 = 3

 查看 session 2 的 trace 文件检查执行SQL和函数使用的梆定变量等信息：

=====================

PARSING IN CURSOR #4 len=34 dep=0 uid=91 oct=3 lid=91 tim=1452976366537538 hv=67575608 ad='384dafbc' sqlid='6ngw7f420f7ts'

select sys.f_test(:a,:b) from dual

END OF STMT

PARSE #4:c=4001,e=581,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=1,plh=0,tim=1452976366537537

BINDS #4:

 Bind#0

  oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00

  oacflg=03 fl2=1000000 frm=00 csi=00 siz=48 off=0

  kxsbbbfp=b6999a4c  bln=22  avl=02  flg=05

  value=1   --这个就是梆定变量 :a 的值

 Bind#1

  oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00

  oacflg=03 fl2=1000000 frm=00 csi=00 siz=0 off=24

  kxsbbbfp=b6999a64  bln=22  avl=02  flg=01

  value=2   --这个就是梆定变量 :b 的值

EXEC #4:c=0,e=3643,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=1,plh=1388734953,tim=1452976366541327

WAIT #4: nam='SQL*Net message to client' ela= 4 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=1452976366541455

FETCH #4:c=0,e=173,p=0,cr=0,cu=0,mis=0,r=1,dep=0,og=1,plh=1388734953,tim=1452976366541684

STAT #4 id=1 cnt=1 pid=0 pos=1 obj=0 op='FAST DUAL  (cr=0 pr=0 pw=0 time=0 us cost=2 size=0 card=1)'

WAIT #4: nam='SQL*Net message from client' ela= 2425 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=1452976366544303

FETCH #4:c=0,e=3,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=0,plh=1388734953,tim=1452976366544438

WAIT #4: nam='SQL*Net message to client' ela= 3 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=1452976366544494

WAIT #4: nam='SQL*Net message from client' ela= 1073 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=1452976366545603

===================== 

还可以使用 tkprof 工具将 10046事件的trace文件格式化，以更直观的方式更行显示，但是用tkprof 格式化的trace 文件是看不到梆定变量信息的。（完）