Re: Applications tags and identifiers on V$SESSION

This is the subject of chapter 5, “Making Your Application Easy to Trace” (pages 82–92), in *The Method R Guide to Mastering Oracle Trace Data, 3rd Edition*, published just a week or two ago.

https://method-r.com/2019/04/12/motd3/ if you're interested.

Cary Millsap
Method R Corporation
Author of *Optimizing Oracle Performance <http://amzn.to/OM0q75>* and *The Method R Guide to Mastering Oracle Trace Data, 3rd edition <https://amzn.to/2IhhCG6+-+Millsap+2019.+Mastering+Oracle+Trace+Data+3ed>*

On Wed, Apr 10, 2019 at 8:46 AM Sayan Malakshinov <xt.and.r_at_gmail.com> wrote:

> Hi Luis,
>
> Yes, that is a well-known and recommended thing:
> https://docs.oracle.com/cd/B19306_01/network.102/b14266/apdvprxy.htm#i1010372
> Moreover it also allows to gather and aggregate statistics by
> service/module/action,client_id using dbms_monitor and special views
> like v$serv_mod_act_stats and v$client_stats:
> https://docs.oracle.com/database/121/ARPLS/d_monitor.htm#ARPLS67154
>
> You may noticed that even standard trace files do not store client_info:
> *** 2019-03-21T01:49:01.531276+03:00
> *** SESSION ID:(137.60357) 2019-03-21T01:49:01.531276+03:00
> *** CLIENT ID:() 2019-03-21T01:49:01.531276+03:00
> *** SERVICE NAME:() 2019-03-21T01:49:01.531276+03:00
> *** MODULE NAME:(oradim.exe) 2019-03-21T01:49:01.531276+03:00
> *** ACTION NAME:() 2019-03-21T01:49:01.531276+03:00
> *** CLIENT DRIVER:() 2019-03-21T01:49:01.531276+03:00
>
> You can find column client_info just in a few views: v/gv$session,
> v/gv$sql_monitor, dba/all/user_sql_translations and sys_fba_context_aud.
>
> On Wed, Apr 10, 2019 at 3:48 PM Luis Santos <lsantos_at_pobox.com> wrote:
>
>> I always recommend to our developers to make extensive use of
>> DBMS_APPLICATION_INFO. As far I know there are three main columns on
>> V$SESSION that are updated when using DBMS_APPLICATION_INFO:
>>
>> SQL> exec dbms_application_info.set_client_info (CLIENT_INFO => 'MY
>>> PROCESS');
>>> SQL> exec dbms_application_info.set_module (MODULE_NAME => 'MY
>>> MODULE', ACTION_NAME => 'ACTION_01');
>>> SQL>
>>> SQL> SELECT CLIENT_INFO, MODULE, ACTION FROM v$session WHERE audsid =
>>> USERENV('SESSIONID');
>>> CLIENT_INFO MODULE ACTION
>>> -------------------- -------------------- --------------------
>>> MY PROCESS MY MODULE ACTION_01
>>> SQL>
>>> SQL> exec dbms_application_info.set_action (ACTION_NAME => 'ACTION
>>> 02');
>>> SQL>
>>> SQL> SELECT CLIENT_INFO, MODULE, ACTION FROM v$session WHERE audsid =
>>> USERENV('SESSIONID');
>>> CLIENT_INFO MODULE ACTION
>>> -------------------- -------------------- --------------------
>>> MY PROCESS MY MODULE ACTION 02
>>
>>
>> But today a developer told me that he is using
>> *dbms_session.set_identifier.*
>>
>> I have to confess I never heard about this option before.
>>
>> This set another column on V$SESSION, called CLIENT_IDENTIFIER.
>>
>> SQL> exec dbms_session.set_identifier (CLIENT_ID => 'MY PROCESS AGAIN');
>>>
>>> SQL> SELECT CLIENT_INFO, CLIENT_IDENTIFIER, MODULE, ACTION FROM
>>> v$session WHERE audsid = USERENV('SESSIONID');
>>> CLIENT_INFO CLIENT_IDENTIFIER MODULE ACTION
>>> -------------------- -------------------- --------------------
>>> --------------------
>>> MY PROCESS MY PROCESS AGAIN MY MODULE ACTION 02
>>
>>
>> And I discover, to my surprise, that CLIENT_IDENTIFIER /
>> DBMS_SESSION.SET_IDENTIFIER seems a too much better option. Because
>> CLIENT_INFO is not stored on v$active_session_history, but
>> CLIENT_IDENITIFER is (although shortened to "CLIENT_ID").
>>
>> SQL> SELECT CLIENT_INFO, CLIENT_IDENTIFIER, MODULE, ACTION FROM
>>> v$active_session_history where rownum <=1;
>>> SELECT CLIENT_INFO, CLIENT_IDENTIFIER, MODULE, ACTION FROM
>>> v$active_session_history where rownum <=1
>>> *
>>> ERRO na linha 1:
>>> ORA-00904: "CLIENT_IDENTIFIER": identificador inválido
>>>
>>> SQL> SELECT CLIENT_INFO, CLIENT_ID , MODULE, ACTION FROM
>>> v$active_session_history where rownum <=1;
>>> SELECT CLIENT_INFO, CLIENT_ID , MODULE, ACTION FROM
>>> v$active_session_history where rownum <=1
>>> *
>>> ERRO na linha 1:
>>> ORA-00904: "CLIENT_INFO": identificador inválido
>>>
>>> SQL> SELECT CLIENT_ID , MODULE, ACTION FROM v$active_session_history
>>> where rownum <=1;
>>> CLIENT_ID MODULE
>>> ACTION
>>> ----------------------------------------------------------------
>>> -------------------- --------------------
>>> 93316716
>>> REGISTROSOLIC_TAP SOLICITACAO_TAP
>>
>>
>>
>> *--*
>> *Att*
>>
>>
>> *Luis Santos*
>>
>>
>
>

--
http://www.freelists.org/webpage/oracle-l