Tips: PL/SQL中监控执行进度两种方法

eagle's home 2009-10-21 09:05:06 浏览 3,702 次

标签监控

这是我常用的两种PL/SQL监控运行状况的方法：

1. 使用dbms_application_info.SET_CLIENT_INFO

举例如下:

以下是代码片段：
    declare
     cursor cr is select rowid from test;
     delete_count number;
     total_count number;
    begin
     delete_count :=0;
     total_count :=0;
     for i in cr loop
     delete from test where rowid=i.rowid;
     delete_count :=delete_count+1;
     total_count :=total_count+1;
     if (delete_count>100) then
     dbms_application_info.SET_CLIENT_INFO(’ So far ‘||total_count||’ rows has been deleted’);
     delete_count :=0;
     commit;
     end if;
     end loop;
    end;
    /

另开一session, select client_info from v$session where client_info like ‘So far%’;

注意info的长度有限制，超过64字符会被截断

2. 使用dbms_system.ksdwrt，这个可以写到300个字符

KSDWRT Procedure

This procedure prints the message to the target file (alert log and/or trace file).

Syntax

DBMS_SYSTEM.KSDWRT (

dest IN BINARY_INTEGER,

tst IN VARCHAR2);

Parameters:

dest Destination is indicated by the following values:

1 - Write to trace file.

2 - Write to alertlog.

3 - Write to both.

tst Message (not tested for max length, but output with 300 chars was successful)

举例如下:

以下是代码片段：
    declare
     cursor cr is select rowid from test;
     delete_count number;
     total_count number;
    begin
     delete_count :=0;
     total_count :=0;
     for i in cr loop
     delete from test where rowid=i.rowid;
     delete_count :=delete_count+1;
     total_count :=total_count+1;
     if (delete_count>100) then
     dbms_system.ksdwrt (1,’ So far ‘||total_count||’ rows has been deleted’);
     delete_count :=0;
     commit;
     end if;
     end loop;
    end;
    /

然后开一session, tail -30f xxx.trc

Tips: PL/SQL中监控执行进度两种方法

建议继续学习