使用DBMS_APPLICATION_INFO包

郑全

DBMS_APPLICATION_INFO是一个非常有用的程序包，他提供了通过V$SESSION
跟踪脚本运行情况的能力，该包允许你在v$session中的如下三列中填值：
CLIENT_INFO,MODULE,ACTION，该包不仅提供了设置这些列值的过程，还提供了
返回这些列值的过程，在CLIENT_INFO列中适合存放允许你的程序的客户端信息，
MODULE列适合存放你的主程序名，如包的名称，ACTION列适合存放你的程序包中
的过程名，现在我们先简单了解一下DBMS_APPLICATION_INFO的和V$session相关
的函数：

  dbms_application_info.set_client_info：允许你向v$session中写入你的客户端的信息
  dbms_application_info.set_module：允许你向v$session中写入你的主程序（如包）
  和你的过程的名称
  dbms_application_info.read_client_info：允许你从v$session中读取客户端的信息
  dbms_application_info.read_module：允许你从v$session中读取主程序（如包）
  和你的过程的名称
 
看一个简单的例子：


SQL> set serveroutput on
SQL> 
SQL> DECLARE
  2 l_clinent VARCHAR2(100);
  3 l_mod_name VARCHAR2(100);
  4 l_act_name VARCHAR2(100);
  5 BEGIN
  6 dbms_application_info.set_client_info('my client');
  7 dbms_application_info.read_client_info(l_clinent);
  8 dbms_output.put_line('client='||l_clinent);
  9 dbms_application_info.set_module('my mod','inserting');
 10 FOR i IN 1..100
 11 LOOP
 12 execute immediate 'INSERT INTO pp_test(c1) VALUES(:X)' USING i;
 13 END LOOP;
 14 dbms_application_info.read_module(l_mod_name,l_act_name);
 15 dbms_output.put_line('mod_name='||l_mod_name);
 16 dbms_output.put_line('act_name='||l_act_name);
 17 END;
 18  
 19 /
 
client=my client
mod_name=my mod
act_name=inserting
 
PL/SQL procedure successfully completed
 
SQL> commit;
 
Commit complete
 
SQL> select sid from v$mystat where rownum=1;
 
  SID
----------
  1065
 
SQL> SELECT sid,serial#,client_info,MODULE,action FROM v$session WHERE sid=1065;
 
  SID SERIAL# CLIENT_INFO MODULE ACTION
---------- ---------- ---------------------------------------------------------------- ------------------------------------------------ --------------------------------
  1065 18968 my client my mod inserting
 
再看一个更有用的例子：

 1.首先我们构建一个有大量数据的表：

SQL> DELETE FROM pp_test;
 
1320 rows deleted
 
SQL> insert into pp_test(c1) select object_name from all_objects;
 
116441 rows inserted
 
SQL> insert into pp_test(c1) select c1 from pp_test;
 
116441 rows inserted
 
SQL> insert into pp_test(c1) select c1 from pp_test;
 
232882 rows inserted
 
SQL> insert into pp_test(c1) select c1 from pp_test;
 
465764 rows inserted
 
SQL> commit;
 
Commit complete

 2.我们现在需要更新PP_TEST表的C1列，在脚本执行过程中我们需要知道
  已经处理的行数和已经花费的时间，执行结束后，我们需要知道处理的
  总的行数和执行的总时间，使用如下代码：
   
  --在session1中执行：
DECLARE
CURSOR cur_test IS
SELECT c1,ROWID
FROM pp_test;
l_new_c1 VARCHAR2(2000);
l_count_num PLS_INTEGER := 0;
l_start_time_num PLS_INTEGER;
BEGIN
l_start_time_num := DBMS_UTILITY.GET_TIME;
FOR cur_test_rec IN cur_test LOOP
l_count_num := l_count_num + 1;
l_new_c1 := cur_test_rec.c1||'_NEW';
UPDATE pp_test
SET c1 = l_new_c1
WHERE rowid = cur_test_rec.ROWID;
IF MOD(l_count_num, 1000) = 0 THEN
DBMS_APPLICATION_INFO.SET_MODULE('Records Processed: ' ||
l_count_num, 'Elapsed: ' || (DBMS_UTILITY.GET_TIME -
l_start_time_num)/100 || ' sec'); --每更新1000行，记录一次执行时间
END IF;
END LOOP;
COMMIT;
DBMS_APPLICATION_INFO.SET_MODULE('Records Processed: ' ||
l_count_num, 'Elapsed: ' || (DBMS_UTILITY.GET_TIME -
l_start_time_num)/100 || ' sec'); --更新结束，记录总的执行时间
END;

执行过程中我们可以查询v$session,如下所示：

SQL> SELECT sid,serial#,client_info,MODULE,action FROM v$session WHERE sid=307;
 
  SID SERIAL# CLIENT_INFO MODULE ACTION
---------- ---------- ---------------------------------------------------------------- ------------------------------------------------ --------------------------------
  307 36536 Records Processed: 360000 Elapsed: 18.69 sec
 
SQL> SELECT sid,serial#,client_info,MODULE,action FROM v$session WHERE sid=307;
 
  SID SERIAL# CLIENT_INFO MODULE ACTION
---------- ---------- ---------------------------------------------------------------- ------------------------------------------------ --------------------------------
  307 36536 Records Processed: 626000 Elapsed: 32.99 sec
 
SQL> SELECT sid,serial#,client_info,MODULE,action FROM v$session WHERE sid=307;
 
  SID SERIAL# CLIENT_INFO MODULE ACTION
---------- ---------- ---------------------------------------------------------------- ------------------------------------------------ --------------------------------
  307 36536 Records Processed: 837000 Elapsed: 44.56 sec
   
执行结束，我们再次查询v$session:

SQL> SELECT sid,serial#,client_info,MODULE,action FROM v$session WHERE sid=307;
 
  SID SERIAL# CLIENT_INFO MODULE ACTION
---------- ---------- ---------------------------------------------------------------- ------------------------------------------------ --------------------------------
  307 36536 Records Processed: 931528 Elapsed: 49.86 sec
 
SQL>