[20200120]oracle wait event "enq: SQ – contention" and DBA_DB_LINK_SOURCES.txt

[20200120]oracle wait event "enq: SQ – contention" and DBA_DB_LINK_SOURCES.txt

--//昨天看連結，裡面提到從12c 版本開始新引入DBA_DB_LINK_SOURCES(link_sources$)記
--//錄了遠端dblink 曾登入本地資料的會話資訊(hostname、IP, dbname、使用者名稱、logon_time、logon_count)。

1.環境：
SCOTT@test01p> @ ver1
PORT_STRING                    VERSION        BANNER                                                                               CON_ID
------------------------------ -------------- -------------------------------------------------------------------------------- ----------
IBMPC/WIN_NT64-9.1.0           12.2.0.1.0     Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production              0

SYS@test> @desc DBA_DB_LINK_SOURCES
Name              Null?    Type
----------------- -------- ---------------------------
SOURCE_ID         NOT NULL NUMBER
DB_NAME           NOT NULL VARCHAR2(256)
DBID              NOT NULL NUMBER
DB_UNIQUE_NAME             VARCHAR2(256)
HOST_NAME                  VARCHAR2(256)
IP_ADDRESS                 VARCHAR2(128)
PROTOCOL                   VARCHAR2(64)
USERNAME          NOT NULL VARCHAR2(128)
USER#             NOT NULL NUMBER
FIRST_LOGON_TIME  NOT NULL TIMESTAMP(6)
LAST_LOGON_TIME            TIMESTAMP(6)
LOGON_COUNT                NUMBER


SYS@test> select text_vc c80 from dba_views where VIEW_NAME='DBA_DB_LINK_SOURCES';
C80
------------------------------------------------------------------------------------
select source_id, db_name, dbid, db_unique_name, host_name, ip_address,
       protocol, username, user#, first_logon_time,
       (select max(llt) from
          ((select last_logon_time llt from LINK_SOURCES$
                   where source_id = X.source_id)
            union
           (select max(logon_time) llt from LINK_LOGONS$
                   where source_id = X.source_id))) last_logon_time,
       (select X.logon_count + count(*) from LINK_LOGONS$
             where source_id = X.source_id) logon_count
from LINK_SOURCES$ X

--//該檢視基於底層基表link_sources$以及LINK_LOGONS$。

2.測試：
SCOTT@test01p> CREATE PUBLIC DATABASE LINK LOOPBACK USING 'localhost:1521/test01p';
Database link created.

SCOTT@test01p> select * from dept@loopback ;
    DEPTNO DNAME                LOC
---------- -------------------- -------------
        10 ACCOUNTING           NEW YORK
        20 RESEARCH             DALLAS
        30 SALES                CHICAGO
        40 OPERATIONS           BOSTON

SCOTT@test01p> select * from DBA_DB_LINK_SOURCES
  2  @ prxx
==============================
SOURCE_ID                     : 1
DB_NAME                       : TEST01P
DBID                          : 3179431019
DB_UNIQUE_NAME                : test
HOST_NAME                     : XXXXXX
IP_ADDRESS                    : 127.0.0.1
PROTOCOL                      : tcp
USERNAME                      : SCOTT
USER#                         : 108
FIRST_LOGON_TIME              : 2020-01-20 01:22:44.786000
LAST_LOGON_TIME               : 2020-01-20 01:22:44.786000
LOGON_COUNT                   : 1
PL/SQL procedure successfully completed.

--//檢索共享池可以發現如下語句：
--//sql_id=d2217udafsm66
INSERT INTO link_sources$
            (
               source_id
              ,username
              ,user#
              ,first_logon_time
              ,last_logon_time
              ,logon_count
              ,db_name
              ,dbid
              ,host_name
              ,ip_address
              ,protocol
              ,db_unique_name
            )
     VALUES
            (
               link_source_id_seq.NEXTVAL
              , :usrnm
              , :usri
              ,SYSTIMESTAMP AT TIME ZONE 'UTC'
              ,SYSTIMESTAMP AT TIME ZONE 'UTC'
              ,1
              , :dbldbn
              , :dbldbi
              ,SYS_CONTEXT ('USERENV', 'HOST')
              ,SYS_CONTEXT ('USERENV', 'IP_ADDRESS')
              ,SYS_CONTEXT ('USERENV', 'NETWORK_PROTOCOL')
              ,SUBSTR
               (
                  SYS_CONTEXT ('USERENV', 'DBLINK_INFO')
                 ,20
                 ,  INSTR
                    (
                       SYS_CONTEXT ('USERENV', 'DBLINK_INFO')
                      ,','
                      ,1
                      ,1
                    )
                  - 20
               )
            )


--//sql_id=fw07zwwyz6gva
UPDATE link_sources$
   SET (LOGON_COUNT, LAST_LOGON_TIME) =
          (SELECT (link_sources$.logon_count + COUNT (*)), MAX (LOGON_TIME)
             FROM link_logons$
            WHERE     (link_sources$.source_id = link_logons$.source_id)
                  AND (link_logons$.logon_time <= :ref_tstmp))
 WHERE link_sources$.source_id IN (SELECT UNIQUE source_id
                                     FROM link_logons$
                                    WHERE link_logons$.logon_time <=
                                             :ref_tstmp)

--//裡面有使用序列號link_source_id_seq.
SCOTT@test01p> select * from DBA_SEQUENCES where sequence_owner='SYS' and sequence_name='LINK_SOURCE_ID_SEQ'
  2  @ prxx
==============================
SEQUENCE_OWNER                : SYS
SEQUENCE_NAME                 : LINK_SOURCE_ID_SEQ
MIN_VALUE                     : 1
MAX_VALUE                     : 9999999999999999999999999999
INCREMENT_BY                  : 1
CYCLE_FLAG                    : N
ORDER_FLAG                    : N
CACHE_SIZE                    : 10
LAST_NUMBER                   : 11
SCALE_FLAG                    : N
EXTEND_FLAG                   : N
SESSION_FLAG                  : N
KEEP_VALUE                    : N
PL/SQL procedure successfully completed.

--//裡面CACHE_SIZE設定太小，如果應用頻繁呼叫db_links.出現"enq: SQ - contention "很正常，適當增加cache_size大小。
--//另外也可以透過隱含引數關閉 database link source tracking。

SYS@test> @ hide _db_link_sources_tracking
NAME                      DESCRIPTION                                  DEFAULT_VALUE SESSION_VALUE SYSTEM_VALUE ISSES ISSYS_MOD
------------------------- -------------------------------------------- ------------- ------------- ------------ ----- ---------
_db_link_sources_tracking enable/disable database link source tracking TRUE          TRUE          TRUE         FALSE FALSE