[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 --//昨天看链接https://www.anbob.com/archives/5034.html，里面提到从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