[20210114]理解DBMS_SESSION.set_identifier.txt

--//上午检查发现一套系统定义一个触发器,内容如下:

CREATE OR REPLACE TRIGGER SYS.LOGIN_ON_RECORD_IP
   AFTER LOGON
   ON DATABASE
DECLARE
   v_client_info   v$session.client_info%TYPE;
BEGIN
   v_client_info := NVL (SYS_CONTEXT ('userenv', 'ip_address'), '127.0.0.1');
   DBMS_APPLICATION_INFO.set_client_info (v_client_info);
   DBMS_SESSION.set_identifier (v_client_info);

   EXECUTE IMMEDIATE 'alter session set cursor_sharing =force';
END;
/

--//我记忆里执行DBMS_SESSION.set_identifier (v_client_info),可以改变跟踪文件名字.我发现并没有变,不知道为什么.分析看看.

1.环境:
SCOTT@book> @ ver1
PORT_STRING                    VERSION        BANNER
------------------------------ -------------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx            11.2.0.4.0     Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

2.测试:
SCOTT@book> exec DBMS_SESSION.set_identifier ('abcd');
PL/SQL procedure successfully completed.

SCOTT@book> @ pp
TRACEFILE
--------------------------------------------------------------------------------
/u01/app/oracle/diag/rdbms/book/book/trace/book_ora_4468.trc

--//做一些10046跟踪,查看跟踪文件确实是/u01/app/oracle/diag/rdbms/book/book/trace/book_ora_4468.trc.

3.我理解错误吗?
--//看来我理解错误,检索:https://docs.oracle.com/cd/B19306_01/appdev.102/b14258/d_sessio.htm#i996935
SET_IDENTIFIER

This procedure sets the client ID in the session.

Syntax

DBMS_SESSION.SET_IDENTIFIER ( client_id VARCHAR2);

Parameters

Table 96-15 SET_IDENTIFIER Procedure Parameters
Parameter     Description
client_id   The application-specific identifier of the current database session.

Usage Notes

Note the following:

SET_IDENTIFIER initializes the current session with a client identifier to identify the associated global application context
client_id is case sensitive; it must match the client_id parameter in the set_context
This procedure is executable by public

SCOTT@book> @ spid
       SID    SERIAL# PROCESS                  SERVER    SPID       PID  P_SERIAL# C50
---------- ---------- ------------------------ --------- ------ ------- ---------- --------------------------------------------------
        30       4831 4467                     DEDICATED 4468        26        119 alter system kill session '30,4831' immediate;

SCOTT@book> select client_info , CLIENT_IDENTIFIER from v$session where sid=30;
CLIENT_INFO          CLIENT_IDENTIFIER
-------------------- ----------------------------------------------------------------
                     abcd
--//实际上修改视图V$SESSION的CLIENT_IDENTIFIER.感觉这个字段应该命名为CLIENT_ID.

SCOTT@book> exec DBMS_APPLICATION_INFO.set_client_info ('1234')
PL/SQL procedure successfully completed.

SCOTT@book> select client_info , CLIENT_IDENTIFIER from v$session where sid=30;
CLIENT_INFO          CLIENT_IDENTIFIER
-------------------- ----------------------------------------------------------------
1234                 abcd

--//这样就清晰了.这样设置会话的CLIENT_IDENTIFIER有什么用呢?实际上他会记录在V$ACTIVE_SESSION_HISTORY的client_id字段里面.
select * from DBA_TAB_COLUMNS where column_name='CLIENT_ID' and owner='SYS';
--//执行以上查询可以获得那些视图包含client_id字段.

4.继续测试:
SCOTT@book> select count(*) from emp,all_objects,emp;
  COUNT(*)
----------
  16622956

SCOTT@book> @ hash
HASH_VALUE SQL_ID        CHILD_NUMBER HASH_HEX
---------- ------------- ------------ ---------
2622534844 2dysfxff51d5w            0  9c50b4bc

SYS@book> select distinct client_id from v$active_session_history where sql_id='2dysfxff51d5w';
CLIENT_ID
---------
abcd

--//可以发现v$active_session_history记录了client_id,也就是你设置它出现一些性能问题,按照前面的设置,
--//就知道是那个IP地址执行的sql语句,定位问题.