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.
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
CREATE OR REPLACE PROCEDURE TEST_PROC
/* Registering Module and Action both */
module_name => ‘TEST_PROC’,
action_name => ‘Adding records’
INSERT INTO TEST(TEST_ID,TEST_DESC,TEST_DATE)
SELECT ROWNUM, TABLE_NAME, SYSDATE
WHERE ROWNUM < 10;
/* Registering only Action */
DBMS_APPLICATION_INFO.SET_ACTION(action_name => ‘Update Records’);
SET UserID = USER
WHERE TEST_ID < 10;
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’
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.