DBMS_APPLICATION_INFO包的使用

原创

小麦苗123 2021-04-18 17:11:51 ©著作权

文章标签 DBMS 文章分类 数据库

©著作权归作者所有：来自51CTO博客作者小麦苗123的原创作品，请联系作者获取转载授权，否则将追究法律责任

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中读取主程序（如包）
和你的过程的名称

SYS@orclasm > desc dbms_application_info
PROCEDURE READ_CLIENT_INFO
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
CLIENT_INFO VARCHAR2 OUT
PROCEDURE READ_MODULE
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
MODULE_NAME VARCHAR2 OUT
ACTION_NAME VARCHAR2 OUT
PROCEDURE SET_ACTION
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
ACTION_NAME VARCHAR2 IN
PROCEDURE SET_CLIENT_INFO
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
CLIENT_INFO VARCHAR2 IN
PROCEDURE SET_MODULE
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
MODULE_NAME VARCHAR2 IN
ACTION_NAME VARCHAR2 IN
PROCEDURE SET_SESSION_LONGOPS
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
RINDEX BINARY_INTEGER IN/OUT
SLNO BINARY_INTEGER IN/OUT
OP_NAME VARCHAR2 IN DEFAULT
TARGET BINARY_INTEGER IN DEFAULT
CONTEXT BINARY_INTEGER IN DEFAULT
SOFAR NUMBER IN DEFAULT
TOTALWORK NUMBER IN DEFAULT
TARGET_DESC VARCHAR2 IN DEFAULT
UNITS VARCHAR2 IN DEFAULT

SYS@orclasm >

看一个简单的例子：

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>

Application developers can use the DBMS_APPLICATION_INFO package with Oracle Trace and the SQL trace facility to record names of executing modules or transactions in the database for later use when tracking the performance of various modules and debugging.

When an application registers with the database, its name and actions are recorded in the V$SESSION and V$SQLAREA views.

[ @more @]

Oracle DBMS_APPLICATION_INFO 用法例子

General Information Source {ORACLE_HOME}/rdbms/admin/dbmsapin.sql First Available 7.3.4 Constants Name Data Type Value set_session_longops_nohint BINARY_INTEGER -1 Dependencies DBMS_BACKUP_RESTORE EM_PING KUPV$FT DBMS_STATS EM_SEVERITY_REPOS KUPW$WORKER EMD_CRONOS_ADMIN gv_$session WWV_FLOW EMD_LOADER gv_$session_longop WWV_FLOW_SC_TRANSACTIONS EMD_MAINTENANCE gv_$sqlarea WWV_FLOW_SW_SCRIPT EMD_NOTIFICATION KUPM$MCP READ_CLIENT_INFO Read the value of the client_info field of the current session dbms_application_info.set_client_info( client_info OUT VARCHAR2(64)); exec dbms_application_info.set_client_info('B%'); set serveroutput on DECLARE x VARCHAR2(100); BEGINdbms_application_info.read_client_info(x); dbms_output.put_line(x); END; / exec dbms_application_info.set_client_info('747'); DECLARE x VARCHAR2(100); BEGINdbms_application_info.read_client_info(x); dbms_output.put_line(x); END; /-- the following will not work but try it so that you understand why -- you can not use a stored procedure in a WHERE clauseCREATE OR REPLACE VIEW airplanes_view AS SELECT * FROM airplanes WHERE program_id = dbms_application_info.read_client_info(x);-- wrap the stored procedure so that it presents itself as a functionCREATE OR REPLACE FUNCTION app_info_wrapper RETURN VARCHAR2 IS x VARCHAR2(64); BEGINdbms_application_info.read_client_info(x); RETURN x; END app_info_wrapper; /-- now you can create the view CREATE OR REPLACE VIEW airplanes_view AS SELECT * FROM airplanes WHERE program_id = app_info_wrapper; SELECT * FROM airplanes_view; exec dbms_application_info.set_client_info('777'); SELECT * FROM airplanes_view; READ_MODULE Reads the values of the module and action fields of the current session dbms_application_info.read_module( module_name OUT VARCHAR2(48), action_name OUT VARCHAR2(32)); set serveroutput on DECLARE mod_in VARCHAR2(48); act_in VARCHAR2(32); mod_out VARCHAR2(48); act_out VARCHAR2(32); display_str VARCHAR2(200); BEGIN mod_in := 'Test Module'; act_in := 'Test Action';dbms_application_info.set_module(mod_in, act_in); dbms_lock.sleep(5);dbms_application_info.read_module(mod_out, act_out); display_str := 'Module Is '||mod_out||' and Action is '||act_out; dbms_output.put_line(display_str); END; / SET_ACTION Sets the name of the current action within the current module dbms_application_info.set_action(action_name IN VARCHAR2(32)); exec dbms_application_info.set_action('Load Departments'); SET_CLIENT_INFO Set Client Info Field For The Session dbms_application_info.set_client_info(client_info IN VARCHAR2(64)); CREATE OR REPLACE VIEW btest AS SELECT object_name FROM all_objs WHERE object_name LIKE userenv('client_info'); SELECT * FROM btest; exec dbms_application_info.set_client_info('B%'); SELECT * FROM btest;--==================================== CREATE OR REPLACE VIEW vair AS SELECT * FROM airplanes WHERE program_id = userenv('client_info'); SELECT * FROM vair; exec dbms_application_info.set_client_info('747'); SELECT * FROM vair; SET_MODULE Sets the name of the module that is currently running dbms_application_info.set_module( module_name IN VARCHAR2(48), action_name IN VARCHAR2(32)); exec dbms_application_info.set_module('LOAD_TAB', 'Load Emp'); SET_SESSION_LONGOPS Sets a row in the GV$SESSION_LONGOPS view dbms_application_info.set_session_longops( rindex IN OUT BINARY_INTEGER, slno IN OUT BINARY_INTEGER, op_name IN VARCHAR2(64) DEFAULT NULL, target IN BINARY_INTEGER DEFAULT 0, context IN BINARY_INTEGER DEFAULT 0, sofar IN NUMBER DEFAULT 0, totalwork IN NUMBER DEFAULT 0, target_desc IN VARCHAR2(32) DEFAULT 'unknown_target', units IN VARCHAR2(32) DEFAULT NULL); rindex constant to start a new row set_session_longops_nohint constant BINARY_INTEGER := -1; use returned value from previous call to reuse a row do not use slno ... for internal use by Oracle target is the object number being worked on sofar is any number indicating proress ... so far totalwork a best guess as to the 100% value ... on completion units used for sofar and totalwork CREATE TABLE test ( testcol NUMBER(10));-- Session 1 SELECT DISTINCT sid FROM gv$mystat;-- use this sid number in the session 2 query below DECLARE rindex BINARY_INTEGER; slno BINARY_INTEGER; sofar NUMBER(6,2); target BINARY_INTEGER; totwork NUMBER := 100; BEGIN rindex := dbms_application_info.set_session_longops_nohint; SELECT object_id INTO target FROM all_objs WHERE object_name = 'TEST'; FOR i IN 1 .. totwork LOOP sofar := i;dbms_application_info.set_session_longops(rindex, slno, 'PSOUG', target, 0, sofar, 100, 'Pct Complete'); INSERT INTO test VALUES (i); dbms_lock.sleep(0.25); END LOOP; COMMIT; END; /-- Session 2 substitute the sid returned above from session 1SELECT sid, serial#, schemaname FROM gv$session; SELECT start_time, sofar, totalwork, time_remaining, elapsed_seconds FROM gv$session_longops WHERE sid = 140 AND serial# = 266; DBMS_APPLICATION_INFO Demo Set Action Demo CREATE TABLE test ( testcol NUMBER(10));-- session 1 DECLARE mod_name VARCHAR2(48); act_name VARCHAR2(32); BEGIN mod_name := 'read mod'; act_name := 'inserting';dbms_application_info.set_module(mod_name, act_name); FOR x IN 1..5 LOOP FOR i IN 1 ..60 LOOP INSERT INTO test VALUES (i); COMMIT; dbms_lock.sleep(1); END LOOP; act_name := 'deleting';dbms_application_info.set_action(act_name); FOR i IN 1 ..60 LOOP DELETE FROM test WHERE testcol = i; COMMIT; dbms_lock.sleep(1); END LOOP; END LOOP; END; /-- session 2 col module format a20 col action format a20 SELECT module, action FROM gv$session; SELECT module, action FROM gv$sqlarea; SELECT sql_text, disk_reads, module, action FROM gv$sqlarea WHERE action = 'deleting';