DBMS_APPLICATION_INFO包的使用

lhrbest發表於2017-03-19
原文網址 : http://blog.itpub.net/26736162/viewspace-2135626/
APP


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 用法例子

from：http://www.psoug.org/reference/dbms_applic_info.html


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); 
BEGIN
dbms_application_info.read_client_info(x);
dbms_output.put_line(x);
END;
/

exec dbms_application_info.set_client_info('747');

DECLARE 
x VARCHAR2(100); 
BEGIN
dbms_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); 
BEGIN
dbms_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';












About Me

...............................................................................................................................

● 本文整理自網路：http://blog.csdn.net/zhpsam109/article/details/3716373、http://blog.itpub.net/271283/viewspace-997571/

● 本文在itpub（http://blog.itpub.net/26736162）、部落格園（http://www.cnblogs.com/lhrbest）和個人微信公眾號（xiaomaimiaolhr）上有同步更新

● 本文pdf版及小麥苗雲盤地址：http://blog.itpub.net/26736162/viewspace-1624453/

● QQ群：230161599     微信群：私聊

● 聯絡我請加QQ好友(642808185)，註明新增緣由

● 文章內容來源於小麥苗的學習筆記，部分整理自網路，若有侵權或不當之處還請諒解

● 版權所有，歡迎分享本文，轉載請保留出處

...............................................................................................................................

拿起手機使用微信客戶端掃描下邊的左邊圖片來關注小麥苗的微信公眾號：xiaomaimiaolhr，掃描右邊的二維碼加入小麥苗的QQ群，學習最實用的資料庫技術。

 DBMS_APPLICATION_INFO包的使用  DBA筆試面試講解

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/26736162/viewspace-2135626/，如需轉載，請註明出處，否則將追究法律責任。

相關文章