使用 DBMS_APPLICATION_INFO 进行实时监控

   dbms_application_info 包，用于交换环境中执行处理的时间点信息。
让一个长时间运行的PL/SQL程序提供处理信息。以下代码段每隔1000 行记录就更新应用程序的信息，内容主要是处理的记录数和花费的时间。

conn plsql/plsql
SQL> create table s_employee_test as select * from hr.employees;
 
Table created
SQL> insert into s_employee_test select * from s_employee_test
  2  ;
 
29 rows inserted
...
...
SQL> insert into s_employee_test select * from s_employee_test;
 
237568 rows inserted
 
SQL> commit;
 
Commit complete
SQL> select count(*) from s_employee_test;
 
  COUNT(*)
----------
    475136

SQL> declare
  2    cursor cur_emp is
  3      select employee_id,salary,rowid from s_employee_test;
  4      lv_new_salary_num number;
  5      lv_count_num pls_integer := 0;
  6      lv_start_time_num pls_integer;
  7  begin
  8    lv_start_time_num := dbms_utility.get_time;
  9    for cur_emp_rec in cur_emp loop
 10      lv_count_num := lv_count_num + 1;
 11      lv_new_salary_num := cur_emp_rec.salary;
 12      update s_employee_test set salary = lv_new_salary_num
 13        where rowid=cur_emp_rec.rowid;
 14      if mod(lv_count_num,1000) = 0 then
 15        dbms_application_info.set_module('records Processed:'||lv_count_num,'Elapsed :'||(dbms_utility.get_time-lv_start_time_num)/100||' sec');
 16      end if;
 17    end loop;
 18    commit;
 19    dbms_application_info.set_module('records Processed:'||lv_count_num,'Elapsed :'||(dbms_utility.get_time-lv_start_time_num)/100||' sec');
 20  end;
 21  /
 
PL/SQL procedure successfully completed

通过查询 v$session，可以监控处理过程。

select username,sid,serial#,module,action from v$session where username='PLSQL';

SQL> select count(*) from s_employee_test;
 
  COUNT(*)
----------
    475136

SQL> select username,sid,serial#,module,action from v$session where username='PLSQL';
 
USERNAME                              SID    SERIAL# MODULE                                           ACTION
------------------------------ ---------- ---------- ------------------------------------------------ --------------------------------
PLSQL                                   9        309 PL/SQL Developer                                 SQL Window - select username,sid
PLSQL                                 132         64 PL/SQL Developer                                 Command Window - New
PLSQL                                 192        187 records Processed:74000                          Elapsed :20.37 sec
PLSQL                                 197        239 PL/SQL Developer                                 Main session

SQL> select username,sid,serial#,module,action from v$session where username='PLSQL';
 
USERNAME                              SID    SERIAL# MODULE                                           ACTION
------------------------------ ---------- ---------- ------------------------------------------------ --------------------------------
PLSQL                                   9        309 PL/SQL Developer                                 SQL Window - select username,sid
PLSQL                                 132         64 PL/SQL Developer                                 Command Window - New
PLSQL                                 192        187 records Processed:94000                          Elapsed :25.82 sec
PLSQL                                 197        239 PL/SQL Developer                                 Main session

...
...
SQL> select username,sid,serial#,module,action from v$session where username='PLSQL';
 
USERNAME                              SID    SERIAL# MODULE                                           ACTION
------------------------------ ---------- ---------- ------------------------------------------------ --------------------------------
PLSQL                                   9        309 PL/SQL Developer                                 SQL Window - select username,sid
PLSQL                                 132         64 PL/SQL Developer                                 Command Window - New
PLSQL                                 192        187 records Processed:475136                         Elapsed :130.18 sec
PLSQL                                 197        239 PL/SQL Developer                                 Main session

最后一次查询的时间是在 PL/SQL 程序单元已经完成时。

以上演示了一种特定环境的有价值的技巧，并提供了实时监控的机制，这样能更容易地准确测量出程序已经运行的时间[/color]，并可估算出程序还要多长时间才能完成[color=red]。