High "row cache mutex" Waiting Events When Execute Query with DBLINK Concurrently (Doc ID 2655417.1)

Last updated on APRIL 30, 2020

Applies to:

Symptoms

• High row cache mutex events occur when concurrently execute query with DBLINK by more than 10 job clients
• From row cache id:15, row cache mutex contention occurs on dc_props
• Call stack when waiting for "row cache mutex" is all the same:  
  <- kqrpre2 <- kqrpre1 <- kkdlpExecSqlCbk
  <- kkdlpExecSql <- kkdlpGet <- kziagvs <- kzdlkcsaltd <- kzdlkdbde
  <- npigdn0 <- npicon0 <- kpndbcon   
   

• The waiting on "row cache mutex" did not be improved even after applied patch 30329209
• This problem can be reproduced as following:
  
  CREATE USER USERA IDENTIFIED BY <password>;
  GRANT CREATE SESSION, CREATE ANY TABLE, UNLIMITED TABLESPACE TO USERA;
  conn USERA/<password>
  create table dba_tmp99 as select sysdate as x3 from dual;
  conn / as sysdba
  CREATE USER USERC IDENTIFIED BY <password>;
  GRANT CREATE SESSION TO USERC;
  GRANT SELECT ON USERA.dba_tmp99 TO USERC;

  CREATE DATABASE LINK DL_TMP99 CONNECT TO USERC IDENTIFIED BY <password> USING '<Connect_String>';

  --create procedure to execute 'select from dblink' in loop for 5 minutes:

  CREATE OR REPLACE PROCEDURE P_TEST99 IS
  v_date date;
  v_end_time date := sysdate+5/1440;
  sid number;
  ser number;
  begin
  select a.sid, a.serial#
  into sid, ser
  from v$session a
  where a.sid = (select sid from v$mystat where rownum = 1);
  dbms_output.put_line('session=(' || sid || ',' || ser || ')');
  dbms_output.put_line(to_char(sysdate,'yyyy-mm-dd hh24:mi:ss'));
  while sysdate <= v_end_time loop
  select x3 into v_date from USERA.dba_tmp99@DL_TMP99;
  end loop;
  dbms_output.put_line(to_char(sysdate,'yyyy-mm-dd hh24:mi:ss'));
  END P_TEST99;
  /

  -- Make the shell scripts for startup sqlplus & execute the procedure 10 times.(to simulate concurrency)

  vi ss.sh

  --------------------ss.sh start--------------------
  #!/bin/sh
  sqlplus / as sysdba >> sql_output.log <
  exec P_TEST99;
  exit;
  EOF
  --------------------ss.sh end--------------------

  vi dd.sh

  --------------------dd.sh start--------------------
  #!/bin/sh

  echo "connect.... test..."
  for i in {1..10};do
  tnow='date "+%Y-%m-%d %H:%M:%S"'
  echo "connect.... $i, time: $tnow"
  echo "connect.... $i, time: $tnow" >> output.log
  /bin/sh ss.sh > /dev/null 2>&1 &
  done
  --------------------dd.sh end--------------------
  Execute the shell script to invoke procedure concurrently;
  $ sh dd.sh