=0,e=208,p=0,cr=7,cu=0,mis=0,r=1,dep=0,og=1,tim=1253679195783930
FETCH #4:c=0,e=73,p=0,cr=1,cu=0,mis=0,r=0,dep=0,og=1,tim=1253679195826698
STAT #4 id=1 cnt=1 pid=0 pos=1 obj=51148 op='TABLE ACCESS FULL EMP (cr=8 pr=0 pw=0 time=194 us)'
--在scott登陆的会话中执行下面的语句，则跟踪文件orcl_ora_7826.trc同步显示所执行的数据操纵语句
SQL> select * from scott.emp where ename ='SCOTT';
EMPNO ENAME JOB MGR HIREDATE
---------- -------------------- ------------------ ---------- -------------
SAL COMM DEPTNO
---------- ---------- ----------
7788 SCOTT ANALYST 7566 19-4月-87
3100 20
--由上可知，整个语句的执行详细处理步骤，最后提示使用了全表扫描
关于实例级别的跟踪,专用服务器模式每个服务器进程都会生成自己的跟踪信息流
共享模式则每个共享服务器都存在一个跟踪文件,且跟踪文件包含的跟踪信息与该共享服务器为其服务的所有会话
而执行的所有SQL语句相关
--关闭实例级别的跟踪
SQL> alter system set sql_trace = false;
System altered.
2.演示基于会话级别的跟踪
一个会话自身能够交互地启用针对该会话的跟踪,其它会话也可以编程启用针对该会话进行跟踪
--清理上次跟踪的会话文件，且同样可以使用上面的方式来进行跟踪，只不过修改跟踪的参数不同而已
SQL> ho rm -f /u01/app/oracle/admin/orcl/udump/* --*/
SQL> conn system/redhat
Connected.
SQL> alter session set sql_trace=true;
Session altered.
--执行SQL 语句则开始跟踪，跟踪完毕后执行下面的语句关闭跟踪
--此处跟踪的方法同实例级别跟踪
SQL> alter session set sql_trace = false;
Session altered.
--开启另一会话使用dbms包进行跟踪
SQL> conn system/redhat
Connected.
SQL> select sid,serial# from v$session where username = 'SYSTEM';
SID SERIAL#
---------- ----------
133 1141
SQL> exec dbms_monitor.session_trace_enable(session_id=>133,serial_num=>1141);
PL/SQL procedure successfully completed.
SQL> update scott.emp set sal = sal + 500 where ename = 'SCOTT';
1 row updated.
SQL> exec dbms_monitor.session_trace_disable(session_id=>133,serial_num=>1141);
PL/SQL procedure successfully completed.
SQL> select s.username,s.sid,p.spid from v$session s,v$process p
2 where s.paddr = p.addr and s.sid = 133;
USERNAME SID SPID
------------------------------ ---------- ------------
SYSTEM 133 10170
SQL> ho cat $ORACLE_BASE/admin/orcl/udump/orcl_ora_10170.trc
/u01/app/oracle/admin/orcl/udump/orcl_ora_10170.trc
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
ORACLE_HOME = /u01/app/oracle/10g
System name: Linux
Node name: robinson
Release: 2.6.18-164.el5
Version: #1 SMP Tue Aug 18 15:51:54 EDT 2009
Machine: i686
Instance name: orcl
Redo thread mounted by this instance: 1
Oracle process number: 18
Unix process pid: 10170, image: oracle@robinson (TNS V1-V3)
*** 2010-09-06 19:49:33.906
*** ACTION NAME:() 2010-09-06 19:49:33.903
|