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 select
SQL_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 number
default null) return number
3
is
4
begin
5
dbms_application_info.set_client_info(userenv('client_info')+1);
6
return instr(s1,s2,n1,n2);
7
end;
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
5
from
6
(select x,
7
myinstr(x,'.',1,1) d1,
8
myinstr(x,'.',1,2) d2,
9
myinstr(x,'.',1,3) d3
10
from t);
X1
X2
X3
X4
--------------- --------------- --------------- ---------------
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
6
from t
7
/
X
X1
X2
X3
X4
--------------- --------------- --------------- --------------- ---------------
1.1.1.1
1
1
ops$tkyte@ORA920 select userenv('client_info') from dual;
USERENV('CLIENT_INFO')
----------------------------------------------------------------
6
set_client_info对SESSION来说 是很好的全局变量。
