在PL/SQL中使DBMS_APPLICATION_INFO
DBMS_APPLICATION_INFO 可以设置SESSION的MODULE，ACTION，CLIENT INFO等状态信息，对应着v$session表中的3个字段，方便了SQL TRACE和
跟踪定位出问题的程序。
可以在PL/SQL块，事务，批处理程序中调用该包，建议在存储过程中调用。假如直接将DBMS_APPLICATION_INFO编码到代码中，会引起不必要的
network trip.
DBMS_APPLICATION_INFO 可以设置SESSION的MODULE，ACTION，CLIENT INFO等状态信息，对应着v$session表中的3个字段，方便了SQL TRACE和
跟踪定位出问题的程序。
可以在PL/SQL块，事务，批处理程序中调用该包，建议在存储过程中调用。假如直接将DBMS_APPLICATION_INFO编码到代码中，会引起不必要的
network trip.
多中方式获取SESSION信息
SQL select username,program,module,action,client_info from v$session where sid=
(select sid from v$mystat where rownum=1);
SQL select userenv('client_info') from dual;
也会在跟踪文件中显示SESSION的MODULE和ACTION信息
*** 2004-11-19 15:30:34.958
*** ACTION NAME:(get count from t) 2004-11-19 15:30:34.928
*** MODULE NAME:(Test) 2004-11-19 15:30:34.928
*** SERVICE NAME:(SYS$USERS) 2004-11-19 15:30:34.928
*** SESSION ID:(155.23) 2004-11-19 15:30:34.908
同时在v$sqlarea 也记录了执行SQL时候的MODULE，ACTION信息
SQL selectSQL_TEXT from v$sqlarea where MODULE='Test';
SQL_TEXT
--------------------------------------------------------------------------------
select count(*) from t
SELECT p1.value''p2.value'_ora_'p.spid filename FROM v$proc
ess p, v$session s, v$parameter p1, v$parameter p2 W
HERE p1.name = 'user_dump_dest' AND p2.name = 'db_name' AND p.addr = s.p
addr AND s.audsid = USERENV ('SESSIONID')
alter session set sql_trace=true
alter session set sql_trace=false
BEGIN dbms_application_info.set_client_info('Testget count from t'); END;
下面是TOM使用set_client_info优化SQL函数调用的程序
ops$tkyte@ORA920 create or replace function myinstr( s1 in varchar2, s2 in
varchar,
2 n1 in number default null, n2 in numberdefault null) return number
3is
4begin
5dbms_application_info.set_client_info(userenv('client_info')+1);
6return instr(s1,s2,n1,n2);
7end;
8/
Function created.
ops$tkyte@ORA920 drop table t;
Table dropped.
ops$tkyte@ORA920 create table t ( x varchar2(15) );
Table created.
ops$tkyte@ORA920 insert into t values ( '1.1.1.1' );
1 row created.
ops$tkyte@ORA920 exec dbms_application_info.set_client_info(0);
PL/SQL procedure sUCcessfully completed.
ops$tkyte@ORA920 select substr(x, 1, d1-1) x1,
2 substr(x, d1+1, d2-d1-1) x2,
3 substr(x, d2+1, d3-d2-1) x3,
4 substr(x, d3+1) x4
5from
6 (select x,
7myinstr(x,'.',1,1) d1,
8myinstr(x,'.',1,2) d2,
9myinstr(x,'.',1,3) d3
10from t);
X1X2X3X4
--------------- --------------- --------------- ---------------
1 1 1 1
ops$tkyte@ORA920 select userenv('client_info') from dual;
USERENV('CLIENT_INFO')
----------------------------------------------------------------
8
ops$tkyte@ORA920
ops$tkyte@ORA920 exec dbms_application_info.set_client_info(0);
PL/SQL procedure successfully completed.
ops$tkyte@ORA920 select x,
2 substr( x, 1, myinstr(x,'.')-1 ) x1,
3 substr( x, myinstr(x,'.')+1, myinstr(x,'.',1,2)-myinstr(x,'.')-1 ) x2,
4 substr( x, myinstr(x,'.',1,2)+1, myinstr(x,'.',1,3)-myinstr(x,'.',1,2)-1
) x3,
5 substr( x, myinstr(x,'.',1,3)+1 ) x4
6from t
7/
X X1X2X3X4
--------------- --------------- --------------- --------------- ---------------
1.1.1.1 1 1
ops$tkyte@ORA920 select userenv('client_info') from dual;
USERENV('CLIENT_INFO')
----------------------------------------------------------------
6
set_client_info对SESSION来说 是很好的全局变量。
