Systems Engineering and RDBMS

DBMS_APPLICATION_INFO

Posted by decipherinfosys on August 23, 2007

In one of our previous blog post, we discussed about getting the ‘Module’ and ‘Action’ parameters using SYS_CONTEXT function. In this blog post, we will discuss about setting up the value for these parameters using dbms_application_info package. Once we setup the values, we can use it later on to retrieve information from V$session and V$sqlarea using values set earlier.

dbms_application_info package contains several subprogram which are used to setup/retrieve these parameter values. You can obtain list of subprograms of the package using following SQL.

SQL> desc dbms_Application_info

This will list all the subprograms of the package along with their arguments. Following is the list of all procedures in a package.

• READ_CLIENT_INFO
• SET_CLIENT_INFO
• READ_MODULE
• SET_ACTION
• SET_MODULE
• SET_SESSION_LONGOPS

Let us test this using an example. We will start with creating table and test procedure and we will set the module and action in the procedure.

CREATE TABLE TEST
(
TEST_ID NUMBER(9),
TEST_DESC VARCHAR(30),
TEST_DATE DATE,
UserID VARCHAR(15)
);

CREATE OR REPLACE PROCEDURE TEST_PROC
AS
BEGIN
/* Registering Module and Action both */
DBMS_APPLICATION_INFO.SET_MODULE
(
module_name => ‘TEST_PROC’,
action_name => ‘Adding records’
);

INSERT INTO TEST(TEST_ID,TEST_DESC,TEST_DATE)
SELECT ROWNUM, TABLE_NAME, SYSDATE
FROM USER_TABLES
WHERE ROWNUM < 10;

DBMS_APPLICATION_INFO.SET_MODULE(null,null);

/* Registering only Action */
DBMS_APPLICATION_INFO.SET_ACTION(action_name => ‘Update Records’);

UPDATE TEST
SET UserID = USER
WHERE TEST_ID < 10;

DBMS_APPLICATION_INFO.SET_ACTION(null);

END;
/

Once procedure is created successfully execute it from SQL*Plus prompt.

SQL> exec test_proc

In above procedure we are setting value for Module and Action parameters using SET_MODULE and SET_ACTION procedures of the package. We can retrieve these values by either using READ_MODULE procedure or by querying v$sqlarea view. Following is an example to retrieve data back. Query is followed by result.

SQL> SELECT Action, Fetches, Executions, Rows_PRocessed
2 FROM v$Sqlarea
3 WHERE Module = ‘TEST_PROC’
4 OR Action = ‘Update Records’
5 /

ACTION FETCHES EXECUTIONS ROWS_PROCESSED
————— ———- ———- ————–
Update Records 0 1 4
Adding records 0 1 4

We can retrieve any value we are interested in from v$sqlarea for the module and action parameters. Similarly we can set and retrieve the client_info value using SET_CLIENT_INFO and READ_CLIENT_INFO procedures. It will retrieve value from client_info column of v$session table.

This becomes very helpful during performance tuning. By going against v$sqlarea using module and action, we exactly know what procedure or PL/SQL block needs any attention. It also helps us to track resources like cpu time, elapsed time etc, for a specific module.

Sorry, the comment form is closed at this time.

 
%d bloggers like this: