在PL/SQL中使DBMS_APPLICATION_INFO

王朝mssql·作者佚名  2008-05-31
窄屏简体版  字體: |||超大  

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来说 是很好的全局变量。

 
 
 
免责声明:本文为网络用户发布,其观点仅代表作者个人观点,与本站无关,本站仅提供信息存储服务。文中陈述内容未经本站证实,其真实性、完整性、及时性本站不作任何保证或承诺,请读者仅作参考,并请自行核实相关内容。
 
 
© 2005- 王朝網路 版權所有 導航