在PL/SQL中使DBMS_APPLICATION_INFO

2/9/2008来源:Oracle教程人气:8439


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