♣
题目部分
在Oracle中,跟踪会话执行语句的方法有哪几种?
♣
答案部分
因为TRACE的目标范围不同,所以导致必须使用不同的方法。若作用于数据库全局的,则改初始化参数。若只作用于当前会话的,则就用ALTER SESSION命令。若作用于其它会话的,则就用DBMS_SYSTEM包。
① SQL_TRACE参数设置:非常传统的方法
SQL_TRACE可以作为初始化参数在全局启用,也可以通过命令行方式在具体会话启用。在参数文件(PFILE/SPFILE)中指定:SQL_TRACE=TRUE或ALTER SYSTEM SET SQL_TRACE=TRUE;。
通过在全局启用SQL_TRACE可以跟踪到所有后台进程及所有用户进程的活动,通过跟踪文件的实时变化,可以清晰地看到各个进程之间的紧密协调。需要注意的是,在全局启用SQL_TRACE会产生大量trace文件,很容易耗尽磁盘空间,这通常会导致比较严重的性能问题,所以在生产环境中要谨慎使用,并且及时关闭。
在大多数时候使用SQL_TRACE跟踪的都是当前会话的进程。通过跟踪当前进程可以发现当前操作的后台数据库递归活动,这在研究数据库新特性时尤其有效,在研究SQL执行,发现后台错误等方面也非常有用。
在会话级启用和停止SQL_TRACE的方式如下所示:
启用当前SESSION的跟踪:
1SQL> ALTER SESSION SET SQL_TRACE=TRUE; 2Session altered.
此时的SQL操作将被跟踪:
1SQL> SELECT COUNT(*) FROM DBA_USERS; 2 COUNT(*) 3---------- 4 44
结束跟踪:
1SQL> ALTER SESSION SET SQL_TRACE=FALSE; 2Session altered.
在很多时候需要跟踪其他用户的进程,而不是当前用户,这可以通过Oracle提供的系统包DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION来完成。
通过V$SESSION可以获得SID、SERIAL#等信息,获得进程信息,选择需要跟踪的进程:
1SQL> col spid for a8 2SQL> col spid for a10 3SQL> col username for a10 4SQL> col tracefile for a80 5SQL> SELECT S.INST_ID, S.SID, S.SERIAL#, P.SPID, S.USERNAME, P.TRACEFILE 6 2 FROM GV$SESSION S, GV$PROCESS P 7 3 WHERE P.ADDR = S.PADDR 8 4 AND S.INST_ID = P.INST_ID 9 5 AND S.USERNAME IS NOT NULL; 10 INST_ID SID SERIAL# SPID USERNAME TRACEFILE 11---------- ---------- ---------- ---------- ---------- --------------------------------------------------------------------------- 12 1 144 293 37302 SYS /u02/app/oracle/diag/rdbms/oratest/oratest/trace/oratest_ora_37302_lhr.trc 13 1 146 1557 37340 SYS /u02/app/oracle/diag/rdbms/oratest/oratest/trace/oratest_ora_37340.trc 14 1 26 321 37380 LHR /u02/app/oracle/diag/rdbms/oratest/oratest/trace/oratest_ora_37380.trc 15 1 152 3243 37400 SYS /u02/app/oracle/diag/rdbms/oratest/oratest/trace/oratest_ora_37400.trc
设置跟踪SID为26的会话:
1SQL> EXEC DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION(26,321,TRUE); 2PL/SQL procedure successfully completed.
可以等候片刻,等待SID为26的会话执行SQL,完成后,停止跟踪:
1SQL> EXEC DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION(26,321,FALSE); 2PL/SQL procedure successfully completed.
② 使用10046事件
全局设定:参数文件中指定:EVENT="10046 TRACE NAME CONTEXT FOREVER,LEVEL 12"
或者:
1SQL> ALTER SYSTEM SET EVENTS '10046 TRACE NAME CONTEXT FOREVER, LEVEL 12';--启用 2SQL> ALTER SYSTEM SET EVENTS '10046 TRACE NAME CONTEXT OFF';--关闭
当前会话设定:
1SQL> ALTER SESSION SET EVENTS '10046 TRACE NAME CONTEXT FOREVER, LEVEL 12'; 2SQL> --执行SQL语句 3SQL> ALTER SESSION SET EVENTS '10046 TRACE NAME CONTEXT OFF';
在Oracle 11g中还可以对单个SQL打开10046事件跟踪,命令如下所示:
1ALTER SYSTEM SET EVENTS 'SQL_TRACE [SQL:&&SQL_ID] WAIT=TRUE,BIND=TRUE,PLAN_STAT=ALL_EXECUTIONS,LEVEL=12';
关闭单个SQL的跟踪命令如下所示:
1ALTER SYSTEM SET EVENTS 'SQL_TRACE [SQL:&&SQL_ID] OFF';
若要针对每个新连接的会话生成10046跟踪文件,则可以使用登录触发器。如下的代码是跟踪LHR用户的信息,在用户LHR登录数据库系统时会启动10046跟踪。
1CREATE OR REPLACE TRIGGER TRI_SET_TRACE_LHR 2AFTER LOGON ON DATABASE 3WHEN (USER IN ('LHR')) 4DECLARE 5BEGIN 6 EXECUTE IMMEDIATE 'ALTER SESSION SET STATISTICS_LEVEL=ALL'; 7 EXECUTE IMMEDIATE 'ALTER SESSION SET MAX_DUMP_FILE_SIZE=UNLIMITED'; 8 EXECUTE IMMEDIATE 'ALTER SESSION SET EVENTS ''10046 TRACE NAME CONTEXT FOREVER, LEVEL 12'''; 9END TRI_SET_TRACE_LHR;
③ DBMS_SESSION包:只能跟踪当前会话,不能指定会话
跟踪当前会话:
1SQL> EXEC DBMS_SESSION.SET_SQL_TRACE(TRUE); 2SQL> 执行SQL 3SQL> EXEC DBMS_SESSION.SET_SQL_TRACE(FALSE);
DBMS_SESSION.SET_SQL_TRACE相当于ALTER SESSION SET SQL_TRACE,从生成的TRACE文件可以明确地看ALTER SESSION SET SQL_TRACE语句。使用DBMS_SESSION.SESSION_TRACE_ENABLE过程,不仅可以看到等待事件信息还可以看到绑定变量信息,相当于“ALTER SESSION SET EVENTS '10046 TRACE NAME CONTEXT FOREVER, LEVEL 12';”语句:
1SQL> EXEC DBMS_SESSION.SESSION_TRACE_ENABLE(WAITS=>TRUE,BINDS=>TRUE); 2SQL> 执行SQL 3SQL> EXEC DBMS_SESSION.SESSION_TRACE_DISABLE();
④ DBMS_SYSTEM包
使用DBMS_SYSTEM.SET_EV设置10046事件:
1SQL> EXEC DBMS_SYSTEM.SET_EV(SID,SERIAL#,10046,12,'');--启用跟踪 2SQL> EXEC DBMS_SYSTEM.SET_EV(SID,SERIAL#,10046,0,'');--结束跟踪
⑤ DBMS_MONITOR包
该包是从Oracle 10g开始提供的,功能非常强大。可在模块级别、动作级别、客户端级别、数据库级别、会话级别进行跟踪,Oracle官方支持。
跟踪当前会话:
1SQL> EXEC DBMS_MONITOR.SESSION_TRACE_ENABLE; 2SQL> --执行SQL 3SQL> EXEC DBMS_MONITOR.SESSION_TRACE_DISABLE;
跟踪其他会话:
1SQL>EXEC DBMS_MONITOR.SESSION_TRACE_ENABLE(SESSION_ID=>SID,SERIAL_NUM=>SERIAL#,WAITS=>TRUE,BINDS=>TRUE); 2SQL> --执行SQL 3SQL>EXEC DBMS_MONITOR.SESSION_TRACE_DISABLE(SESSION_ID=>SID,SERIAL_NUM=>SERIAL#);
⑥ oradebug
这是SQL*Plus的工具,需要提供OSPID或者Oracle PID。
跟踪当前会话:
1SQL> oradebug setmypid; 2Statement processed. 3SQL> oradebug unlimit; 4Statement processed. 5SQL> oradebug event 10046 trace name context forever,level 12; 6Statement processed. 7SQL> 执行SQL 8SQL> oradebug tracefile_name 9SQL> oradebug event 10046 trace name context off; 10Statement processed.
跟踪其他会话:
1SQL> select spid,pid2 from v$process 2 2 where addr in (select paddr from v$session where sid=(select distinct sid from v$mystat)); 3SPID PID 4------------ ---------- 51457 313 6SQL> oradebug setospid 1457; 7Statement processed.
或者:
1SQL> oradebug setorapid 313; 2Statement processed. 3SQL> oradebug unlimit; 4Statement processed. 5SQL> oradebug event 10046 trace name context forever,level 12; 6Statement processed. 7SQL> oradebug tracefile_name 8SQL> oradebug event 10046 trace name context off; 9Statement processed.
使用oradebug help可以查看oradebug的命令帮助。