Interconnect와 RAC 대기

SELECT inst_id, instance_name, host_name, status FROM gv$instance ORDER BY inst_id;

SELECT *
  FROM (SELECT inst_id, event, wait_class,
               ROUND(time_waited_micro / 1000000) AS wait_sec,
               RANK() OVER (PARTITION BY inst_id ORDER BY time_waited_micro DESC) AS rk
          FROM gv$system_event
         WHERE wait_class <> 'Idle')
 WHERE rk <= 5
 ORDER BY inst_id, rk;

SELECT inst_id, wait_class, ROUND(SUM(time_waited_micro) / 1000000) AS wait_sec
  FROM gv$system_event
 WHERE wait_class IN ('Cluster', 'User I/O', 'Concurrency', 'Commit')
 GROUP BY inst_id, wait_class
 ORDER BY inst_id, wait_sec DESC;

SELECT inst_id, event, wait_class, COUNT(*)
  FROM gv$session
 WHERE username = '<SCHEMA>' AND state = 'WAITING'
 GROUP BY inst_id, event, wait_class
 ORDER BY inst_id;

SELECT s.inst_id, s.sql_id, q.sql_text, o.object_name
  FROM gv$session s
  JOIN gv$sql q
    ON q.inst_id = s.inst_id AND q.sql_id = s.sql_id AND q.child_number = s.sql_child_number
  LEFT JOIN dba_objects o ON o.object_id = s.row_wait_obj#
 WHERE s.username = '<SCHEMA>'
   AND s.wait_class = 'Cluster' AND s.state = 'WAITING';

oifcfg getif

SELECT inst_id, name, ip_address, is_public, source
  FROM gv$cluster_interconnects
 ORDER BY inst_id;

ip link show <PRIVATE_NIC> | grep mtu
ping -c 3 -M do -s 1472 <PRIVATE_HOST>
netstat -su | grep -i -E "receive errors|buffer errors"

SELECT inst_id,
       ROUND(SUM(DECODE(name, 'gc cr block receive time', value)) * 10 /
             NULLIF(SUM(DECODE(name, 'gc cr blocks received', value)), 0), 2) AS avg_cr_ms,
       ROUND(SUM(DECODE(name, 'gc current block receive time', value)) * 10 /
             NULLIF(SUM(DECODE(name, 'gc current blocks received', value)), 0), 2) AS avg_cur_ms
  FROM gv$sysstat
 WHERE name IN ('gc cr block receive time', 'gc cr blocks received',
                'gc current block receive time', 'gc current blocks received')
 GROUP BY inst_id
 ORDER BY inst_id;

SHOW PARAMETER cpu_count
SHOW PARAMETER gcs_server_processes
SELECT inst_id, pname FROM gv$process WHERE pname LIKE 'LMS%' ORDER BY 1, 2;

ALTER SYSTEM SET gcs_server_processes = 4 SCOPE = SPFILE SID = '*';

srvctl stop database -db <DB_NAME>
srvctl start database -db <DB_NAME>

SELECT inst_id, COUNT(*) AS lms_count
  FROM gv$process WHERE pname LIKE 'LMS%'
 GROUP BY inst_id ORDER BY inst_id;

ALTER SYSTEM RESET gcs_server_processes SCOPE = SPFILE SID = '*';

srvctl stop database -db <DB_NAME>
srvctl start database -db <DB_NAME>

SELECT inst_id, value FROM gv$parameter WHERE name = 'gcs_server_processes';
SELECT inst_id, COUNT(*) AS lms_count
  FROM gv$process WHERE pname LIKE 'LMS%'
 GROUP BY inst_id ORDER BY inst_id;

ps -ef | grep lms | grep -v grep
ps -o pid,cls,ni,pri,cmd -p <LMS_PID>

renice -20 -p <LMS_PID>

CREATE TABLE t_order_seq (branch_id NUMBER PRIMARY KEY, last_no NUMBER);
INSERT INTO t_order_seq SELECT level, 0 FROM dual CONNECT BY level <= 4;
COMMIT;

-- 1번 노드: 지점 1, 2 / 2번 노드: mod(i,2) + 3 으로 지점 3, 4
BEGIN
  FOR i IN 1..200000 LOOP
    UPDATE t_order_seq SET last_no = last_no + 1 WHERE branch_id = mod(i,2) + 1;
    COMMIT;
  END LOOP;
END;
/

SELECT inst_id, wait_class, event, COUNT(*)
  FROM gv$session
 WHERE username = '<SCHEMA>' AND state = 'WAITING'
 GROUP BY inst_id, wait_class, event
 ORDER BY inst_id;

SELECT s.inst_id, s.sql_id, q.sql_text
  FROM gv$session s
  JOIN gv$sql q
    ON q.inst_id = s.inst_id AND q.sql_id = s.sql_id AND q.child_number = s.sql_child_number
 WHERE s.username = '<SCHEMA>' AND s.wait_class = 'Cluster' AND s.state = 'WAITING';

SELECT DISTINCT s.inst_id, o.owner, o.object_name
  FROM gv$session s
  JOIN dba_objects o ON o.object_id = s.row_wait_obj#
 WHERE s.username = '<SCHEMA>' AND s.wait_class = 'Cluster' AND s.state = 'WAITING';

for p in $(pgrep -f "_lms"); do renice 19 -p $p; done

oifcfg getif
ip link show <PRIVATE_NIC> | grep mtu      # 1번 노드에서도 같은 명령으로 비교
ping -c 3 -M do -s 1472 <RAC1_PRIVATE_HOST>
ps -o pid,cls,ni,cmd -p $(pgrep -d, -f _lms)

SELECT inst_id, name, ip_address FROM gv$cluster_interconnects;

for p in $(pgrep -f "_lms"); do renice -20 -p $p; done
ps -o pid,cls,ni,cmd -p $(pgrep -d, -f _lms)