How To Tag a Particular Query

Sometimes it happens that you want to track and trace your query or block of code in the Shared SQL area (v$sql) or v$session. For this particular purpose, Oracle is armed with the DBMS_APPLICATION_INFO package.

With that, you can tag your codes (even each step individually) so you can better track and monitor your queries. Let’s have a closer look at the procedures you can use.

• SET_MODULE – you can set a module name for a currently running code
• READ_MODULE – you can get a module name for a currently running code
• SET_ACTION – you can set an action name for a currently running code in a module
• SET_CLIENT_INFO – you can set additional information about the process/client
• READ_CLIENT_INFO – you can get additional information about the process/client
• SET_SESSION_LONGOPS – you can log your process execution to track the on-going progress of your application (i.e. database backup progress tracking) in v$session_longops

I will only touch two of them: SET_MODULE and SET_ACTION. These I use the most and the rest is pretty much self-descriptive anyway 🙂

Examples

You can use it either for a single query:

-- set the action and module
BEGIN
  dmbs_application_info.set_action('Action name'); -- to describe the action
  dbms_application_info.set_module('Module name','Action name'); -- params: (Module, Action)
END;

-- run a query (withing the same window/session)
SELECT *
FROM oracle_world.employees;

-- check the query in the v$sql (module, action)
SELECT module
, action
, s.*
FROM v$sql s
WHERE module = 'Module name'; -- or you can use your client_info data or action_name of course as well

-- or check it in the v$session (module, action, client_info)
SELECT module
, action
, client_info
, ssn.*
FROM v$session ssn
WHERE action = 'Action name'; -- or you can use your client_info data or module_name of course as well

This is a good approach, for example, as a performance tuning tracking (so you can easily identify your query in between the bunch of others.

On the other side, you can use this for tracking/logging complex procedures, packages, … this is a sample withdrawal procedure ..

CREATE PROCEDURE p_sample_withdraw_money(
  p_id_account NUMBER IN,
  p_amount NUMBER IN,
  p_status VARCHAR2 OUT  
  ) 
AS 

  l_funds account.funds%TYPE;
  l_err_msg VARCHAR2(255);
  NOT_ENOUGH_FUNDS EXCEPTION;
  
BEGIN 
  DBMS_APPLICATION_INFO.SET_MODULE(module_name => 'check available funds', 
                                   action_name => 'ATM Sample withdrawal'); 
  SELECT funds INTO l_funds
  FROM account
  WHERE 1=1
  AND id_account = p_id_account;

  IF l_funds < p_amount THEN
    RAISE NOT_ENOUGH_FUNDS;
  END IF;


  DBMS_APPLICATION_INFO.SET_MODULE(module_name => 'process withdrawal', 
                                   action_name => 'ATM Sample withdrawal'); 
   
  UPDATE account
  SET funds = funds - p_amount
  WHERE 1=1
  AND id_account = p_id_account;


  DBMS_APPLICATION_INFO.SET_MODULE(module_name => 'finishing withdrawal', 
                                   action_name => 'ATM Sample withdrawal'); 
  p_status := 'Successfully withdrawn';
  commit;

  DBMS_APPLICATION_INFO.SET_MODULE(null,null); 

  EXCEPTION
    WHEN NOT_ENOUGH_FUNDS THEN
          p_status := 'Insufficient funds';
          l_err_msg := 'You do not have enough funds. Please try a lower amount';
          dbms_output.put_line(l_err_msg);
          RAISE_APPLICATION_ERROR(-20001,l_err_msg);
    WHEN OTHERS THEN
          p_status := 'Other error';
          dbms_output.put_line('Ooops, something went wrong');
END;