[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語句,定位問題.