[20210114]理解DBMS_SESSION.set_identifier.txt

[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语句,定位问题.