dbms_application_info 包,用于交换环境中执行处理的时间点信息。
让一个长时间运行的PL/SQL程序提供处理信息。以下代码段每隔1000 行记录就更新应用程序的信息,内容主要是处理的记录数和花费的时间。
通过查询 v$session,可以监控处理过程。
select username,sid,serial#,module,action from v$session where username='PLSQL';
最后一次查询的时间是在 PL/SQL 程序单元已经完成时。
以上演示了一种特定环境的有价值的技巧,并[b][color=red]提供了实时监控的机制[/color][/b],这样能更容易地[b]准确测量出程序已经运行的时间[/b][color=red][/color],[b]并可估算出程序还要多长时间才能完成[/b][color=red][/color]。
让一个长时间运行的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 程序单元已经完成时。
以上演示了一种特定环境的有价值的技巧,并[b][color=red]提供了实时监控的机制[/color][/b],这样能更容易地[b]准确测量出程序已经运行的时间[/b][color=red][/color],[b]并可估算出程序还要多长时间才能完成[/b][color=red][/color]。