버퍼와 Shared Pool 경합

#핵심 요약

메모리와 I/O의 처리 단위는 블록임
서로 다른 행이라도 같은 블록에 있으면 동시 변경 때 버퍼 락을 기다림(buffer busy waits)
TX는 같은 행, buffer busy는 같은 블록 경합임

RAC에서는 gc buffer busy acquire(다른 노드에서 블록을 받는 동안), gc buffer busy release(다른 노드로 보내기 전) 대기가 따로 있음

래치는 SGA 메모리 구조를 아주 짧게 보호하는 장치이며, 기다릴 때 재시도(spin)하므로 경합이 늘면 CPU가 오름
Enqueue는 논리적 자원을 보호하고 큐에 줄을 섬

CBC 래치 경합의 원인은 실행당 버퍼를 많이 읽는 비효율 SQL이나 소수 블록에 몰리는 Hot block임
원인 SQL의 buffer gets를 줄이는 것이 중심임

리터럴 SQL은 값마다 다른 SQL로 하드 파싱되어 Shared Pool 구조를 계속 바꾸고 latch: shared pool과 CPU를 늘림
바인드 변수가 근본 대책이고, 소스를 고칠 수 없으면 cursor_sharing = force를 검토함
Shared Pool 증설로는 해결되지 않음

#개념과 동작 원리

실습은 강사가 제공한 OWI 부하 시나리오(@exec로 대기 이벤트 이름, 세션 수, 실행 시간을 입력)를 썼는데, 이 스크립트는 기재 없음

버퍼 캐시 블록은 블록 주소의 해시 값에 따라 해시 체인에 연결되고, 체인을 탐색하는 동안 구조가 바뀌지 않게 CBC 래치를 잡음(논리적 I/O와 물리적 I/O)
하드 파싱과 소프트 파싱의 차이는 SQL 공유와 재사용에 있음

#필수 명령어 또는 SQL

#buffer busy waits

SELECT s.inst_id, s.event, o.object_name, COUNT(*)
  FROM gv$session s
  LEFT JOIN dba_objects o ON o.object_id = s.row_wait_obj#
 WHERE s.event LIKE '%buffer busy%'
 GROUP BY s.inst_id, s.event, o.object_name;

SELECT COUNT(DISTINCT DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)) AS blocks, COUNT(*) AS cnt
  FROM t_buffer_busy_waits;

ALTER TABLE t_buffer_busy_waits PCTFREE 99;
CREATE TABLE t_buffer_busy_waits_backup AS SELECT * FROM t_buffer_busy_waits;
TRUNCATE TABLE t_buffer_busy_waits;
INSERT INTO t_buffer_busy_waits SELECT * FROM t_buffer_busy_waits_backup;
COMMIT;

결과 해석: 재적재 뒤 블록 수가 크게 늘어 블록당 행 수가 줄었고, 같은 부하를 다시 주자 대기 없이 빨리 끝났음
PCTFREE 99는 효과 확인용 극단값이며 운영에서는 행 크기, 동시성, 공간을 고려함
실습에서는 이어서 UPDATE 조건 컬럼 id에 인덱스를 만들어 consistent gets가 크게 줄어드는 것을 AUTOTRACE로 비교했음

#CBC 래치

SELECT inst_id, event, COUNT(*)
  FROM gv$session_wait
 WHERE event LIKE 'latch%'
 GROUP BY inst_id, event;

SELECT *
  FROM (SELECT inst_id, sql_id, executions,
               ROUND(buffer_gets / NULLIF(executions, 0)) AS gets_per_exec,
               SUBSTR(sql_text, 1, 60) AS sql_text
          FROM gv$sql
         WHERE parsing_schema_name = '<SCHEMA>'
         ORDER BY buffer_gets DESC)
 WHERE ROWNUM <= 5;

결과 해석:

실습 분석 대상 SQL은 선택도가 낮은 단일 컬럼 인덱스를 힌트로 강제했음
실습 기록상 type = '4', name = 'KIM', id = '4' 조건은 각각 약 15만, 16만, 15만 건이었음

CREATE INDEX t_cache_indx1 ON t_cache_buffers_chains_latch (type, name, id);

SELECT /*+ index(t t_cache_indx1) */ COUNT(*)
  FROM t_cache_buffers_chains_latch t
 WHERE t.type = '4' AND t.name = 'KIM' AND t.id = '4';

결과 해석: 결합 인덱스로 바꾸자 consistent gets가 크게 줄었음(주석)
읽는 버퍼가 줄면 래치 획득 빈도와 CPU도 줌
실습에서는 OS의 top으로 Oracle 프로세스의 CPU 사용률이 높은 것도 함께 확인했음

#Shared Pool과 하드 파싱

SELECT name, bytes FROM v$sgainfo;

SELECT inst_id, name, value
  FROM gv$sysstat
 WHERE name IN ('parse count (hard)', 'parse count (total)')
 ORDER BY inst_id, name;

결과 해석:

실습 기록: Shared Pool Size 738197504, Granule Size 16777216(44 그래뉼, VM마다 다름)
SGA 메모리 할당 단위는 그래뉼임
실습에서는 SQL 공유와 재사용의 리터럴 PL/SQL 블록을 1만 회로 늘려 실행했고 parse count (hard)가 약 1만 늘었음
바인드 변수 버전은 하드 파싱이 거의 늘지 않았음

EXEC DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT;

결과 해석:

부하 전후 스냅샷을 찍고 @?/rdbms/admin/awrrpt.sql, @?/rdbms/admin/addmrpt.sql로 보고서를 만들면 ADDM이 "Hard Parse Due to Literal Usage"를 찾아 바인드 변수 사용과 cursor_sharing = force를 권고했음(출력 텍스트, ADDM과 진단 도구)

#소스를 고칠 수 없을 때

SELECT inst_id, force_matching_signature, COUNT(*) AS cnt, MIN(sql_text)
  FROM gv$sql
 GROUP BY inst_id, force_matching_signature
HAVING COUNT(*) > 100
 ORDER BY cnt DESC;

ALTER SYSTEM SET cursor_sharing = force SCOPE = BOTH SID = '*';
-- 비교 후 원복
ALTER SYSTEM SET cursor_sharing = exact SCOPE = BOTH SID = '*';

결과 해석: 같은 FORCE_MATCHING_SIGNATURE에 수천 개 SQL이 있으면 값만 다른 리터럴 SQL임
주의: cursor_sharing은 재시작 없이 바뀌지만 실행계획이 바뀔 수 있어 전체 적용 전에 해당 솔루션 세션 단위 적용을 검토하고, 근본 개선은 업체에 바인드 변수 적용을 요청하는 것임