how to use dbms_application_info

1. 使用dbms_application_info设置longops的相关信息, 这样我们就可以使用v$session_longops视图来监控我们的程序执行的进度了^_^.

在第一个session中执行一个执行时间比较长的匿名块, 我们再在另一个session中监控, 这个session的执行进度^_^.
SQL> set echo on
SQL> @bbb.sql
SQL> declare
  2    rindex binary_integer;
  3    slno      binary_integer;
  4    total  number := 900;
  5    sofar  number := 0;
  6    obj       binary_integer;
  7    cont      binary_integer;
  8  begin
  9    for i in 1..900 loop
 10      sofar := sofar + 1;
 11      dbms_application_info.set_session_longops(rindex,slno,
 12        'test application',obj,cont,sofar,total,'test','seconds');
 13      dbms_lock.sleep(1);
 14    end loop;
 15  end;
 16  /


SQL> set echo on
SQL> @ccc.sql
SQL> col sid format 9999
SQL> col opname format a18
SQL> col target_desc format a10 heading 'target |description '
SQL> col sofar format 9999
SQL> col totalwork format 9999 heading 'total | work '
SQL> col units format a10
SQL> col time_remaining format 9999 heading 'time|remaining'
SQL> col elapsed_seconds format 9999 heading 'elapsed|seconds'
SQL> select sid,opname,target_desc,sofar,totalwork,units,time_remaining,elapse
seconds
  2  from v$session_longops
  3  /

                         target           total                  time elapsed
  SID OPNAME             descriptio SOFAR  work  UNITS      remaining seconds
----- ------------------ ---------- ----- ------ ---------- --------- -------
   10 test application   test          14    900 seconds          759      12

SQL> set echo off
SQL> /

                         target           total                  time elapsed
  SID OPNAME             descriptio SOFAR  work  UNITS      remaining seconds
----- ------------------ ---------- ----- ------ ---------- --------- -------
   10 test application   test          22    900 seconds          838      21

SQL> /

                         target           total                  time elapsed
  SID OPNAME             descriptio SOFAR  work  UNITS      remaining seconds
----- ------------------ ---------- ----- ------ ---------- --------- -------
   10 test application   test          25    900 seconds          840      24

SQL> /

                         target           total                  time elapsed
  SID OPNAME             descriptio SOFAR  work  UNITS      remaining seconds
----- ------------------ ---------- ----- ------ ---------- --------- -------
   10 test application   test          28    900 seconds          841      27

SQL> /

                         target           total                  time elapsed
  SID OPNAME             descriptio SOFAR  work  UNITS      remaining seconds
----- ------------------ ---------- ----- ------ ---------- --------- -------
   10 test application   test          74    900 seconds          815      73

SQL> /

                         target           total                  time elapsed
  SID OPNAME             descriptio SOFAR  work  UNITS      remaining seconds
----- ------------------ ---------- ----- ------ ---------- --------- -------
   10 test application   test         135    900 seconds          754     133

SQL> /

                         target           total                  time elapsed
  SID OPNAME             descriptio SOFAR  work  UNITS      remaining seconds
----- ------------------ ---------- ----- ------ ---------- --------- -------
   10 test application   test         137    900 seconds          757     136

SQL> /

                         target           total                  time elapsed
  SID OPNAME             descriptio SOFAR  work  UNITS      remaining seconds
----- ------------------ ---------- ----- ------ ---------- --------- -------
   10 test application   test         139    900 seconds          761     139

SQL> /

                         target           total                  time elapsed
  SID OPNAME             descriptio SOFAR  work  UNITS      remaining seconds
----- ------------------ ---------- ----- ------ ---------- --------- -------
   10 test application   test         142    900 seconds          758     142

SQL> /

                         target           total                  time elapsed
  SID OPNAME             descriptio SOFAR  work  UNITS      remaining seconds
----- ------------------ ---------- ----- ------ ---------- --------- -------
   10 test application   test         360    900 seconds          542     361

SQL> /

                         target           total                  time elapsed
  SID OPNAME             descriptio SOFAR  work  UNITS      remaining seconds
----- ------------------ ---------- ----- ------ ---------- --------- -------
   10 test application   test         472    900 seconds          428     472

SQL>

2. 可以使用dbms_application_info的set module/action模块将我们的对应的trace信息进行过滤, 达到对某一块具体的sql执行语句的分析的功能^_^.
APPNAME mod='test' mh=2662839991 act='first step' ah=3692506248
=====================
PARSING IN CURSOR #1 len=68 dep=0 uid=57 oct=42 lid=57 tim=195361146957 hv=1346161232 ad='7b37bf68'
alter session set events '10046 trace name context forever,level 12'
END OF STMT
EXEC #1:c=0,e=169,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=4,tim=195361129557
WAIT #1: nam='SQL*Net message to client' ela= 9 p1=1111838976 p2=1 p3=0
WAIT #1: nam='SQL*Net message from client' ela= 7736508 p1=1111838976 p2=1 p3=0
=====================
PARSING IN CURSOR #1 len=18 dep=0 uid=57 oct=3 lid=57 tim=195368885407 hv=1333943659 ad='7b3732ac'
select * from dual
END OF STMT
PARSE #1:c=0,e=239,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=4,tim=195368885391
BINDS #1:
EXEC #1:c=0,e=247,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=4,tim=195368886634
WAIT #1: nam='SQL*Net message to client' ela= 9 p1=1111838976 p2=1 p3=0
FETCH #1:c=0,e=86,p=0,cr=1,cu=0,mis=0,r=1,dep=0,og=4,tim=195368887026
WAIT #1: nam='SQL*Net message from client' ela= 639 p1=1111838976 p2=1 p3=0
FETCH #1:c=0,e=6,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=0,tim=195368888354
WAIT #1: nam='SQL*Net message to client' ela= 5 p1=1111838976 p2=1 p3=0
*** 2005-04-19 23:19:21.116
WAIT #1: nam='SQL*Net message from client' ela= 18042391 p1=1111838976 p2=1 p3=0
STAT #1 id=1 cnt=1 pid=0 pos=1 obj=31802 op='INDEX FULL SCAN SYS_IOT_TOP_31801 '
=====================
PARSING IN CURSOR #1 len=68 dep=0 uid=57 oct=47 lid=57 tim=195386948246 hv=480137194 ad='7b1ffae0'
BEGIN dbms_application_info.set_module('test','second step'); END;
END OF STMT
PARSE #1:c=10014,e=9811,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=4,tim=195386948228
BINDS #1:
APPNAME mod='test' mh=2662839991 act='second step' ah=1111681585
EXEC #1:c=0,e=704,p=0,cr=0,cu=0,mis=0,r=1,dep=0,og=4,tim=195386950096
WAIT #1: nam='SQL*Net message to client' ela= 9 p1=1111838976 p2=1 p3=0
WAIT #1: nam='SQL*Net message from client' ela= 499 p1=1111838976 p2=1 p3=0
.................
.................
这样我们就可以使用sed/awk编辑对应的trace脚本分析对应模块内的sql语句的具体的trace信息, 将对我们没有用户的trace信息过滤出去^_^.

3. 使用dbms_application_info的set_client_info/module/action作为存储系统全局变量的方式..

SQL> create or replace function get_deptno return number is
  2    v_deptno number;
  3    s_deptno varchar2(20);
  4  begin
  5    dbms_application_info.read_client_info(s_deptno);
  6    v_deptno := to_number(s_deptno);
  7    return v_deptno;
  8  end;
  9  /

Function created.

SQL> create or replace view v_emp as
  2  select * from emp where deptno = get_deptno;

View created.

SQL> select * from v_emp;

no rows selected

SQL> exec dbms_application_info.set_client_info('20');

PL/SQL procedure successfully completed.

SQL> select * from v_emp;

EMPNO ENAME      JOB         MGR HIREDATE         SAL       COMM     DEPTNO
----- ---------- --------- ----- --------- ---------- ---------- ----------
 7369 SMITH      CLERK      7902 17-DEC-80        800                    20
 7566 JONES      MANAGER    7839 02-APR-81       2975                    20
 7788 SCOTT      ANALYST    7566 19-APR-87       3000                    20
 7876 ADAMS      CLERK      7788 23-MAY-87       1100                    20
 7902 FORD       ANALYST    7566 03-DEC-81       3000                    20

SQL>

4. dbms_application_info的最基本的功能, 可以告诉我们系统正在做什么, 我们可以通过v$session直接定位相关的module/action的对应的session.
SQL> @sessinfo.sql
SQL> col sid format 9999
SQL> col username format a10
SQL> col client_info format a15
SQL> col module format a15
SQL> col action format a15
SQL> select a.sid,a.username,a.client_info,a.module,a.action
  2  from v$session a,v$mystat b
  3  where a.sid = b.sid
  4  and rownum <= 1
  5  /

  SID USERNAME   CLIENT_INFO     MODULE          ACTION
----- ---------- --------------- --------------- ---------------
   10 SCOTT                      SQL*Plus

SQL> set echo off
SQL> exec dbms_application_info.set_client_info('jametong');

PL/SQL procedure successfully completed.

SQL> exec dbms_application_info.set_module('test','step one');

PL/SQL procedure successfully completed.

SQL> @sessinfo.sql

  SID USERNAME   CLIENT_INFO     MODULE          ACTION
----- ---------- --------------- --------------- ---------------
   10 SCOTT      jametong        test            step one

SQL> exec dbms_application_info.set_module('test','step two');

PL/SQL procedure successfully completed.

SQL> @sessinfo.sql

  SID USERNAME   CLIENT_INFO     MODULE          ACTION
----- ---------- --------------- --------------- ---------------
   10 SCOTT      jametong        test            step two

SQL>
[@more@]

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/94317/viewspace-796436/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/94317/viewspace-796436/